Create a new record if the desired ID isn't found

I'm using an unbound combo box as the entry point for calling up service
records by the service tag number. The Row Source for the combo box is a
query that gets all of the existing tag numbers. The user can then start
typing in the tag number, and if it already existing, the combo box will go
to the right value, and then when the user hits enter or tab, the After
Update event runs a FindFirst to find the record. Setting Me.Bookmark loads
up the existing tag information and the user can update the tag.

For the times when the user is entering a number for the first time, I want
to use the NoMatch results to create a new record, store the tag number in
the tag field, and then the user can start entering data. I tried using
DoCmd.GoToRecord , , acNewRec, but I get an error message: Run-time error
'2499' You can't use the GoToRecord action or method on an object in Design
view.

The form is Open, not in Design View.

The code I'm using for the event is:
Private Sub SRNumber_Select_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SRNumber] = '" & Me![SRNumber_Select] & "'"
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
End If
End Sub

Thanks, Rich

You use the combo's Not in List event for this. First, you have to set the
Limit To List property to Yes so the Not In List event will fire.

For starters, take the GotoRecord code out of the AfterUpdate event:
(note changes for simplicity:

Private Sub SRNumber_Select_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[SRNumber] = """ & Me![SRNumber_Select] & """"
If Not rs.NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

Then create a Not In List event for the combo:

Private Sub SRNumber_Select_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Whatever Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO NameOfTable (SRNumber) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery

With Me.RecordsetClone
.FindFirst "[SRNumber] = """ & Me![SRNumber_Select] & """"
If Not rs.NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Response = acDataErrAdded
Else
Me![SRNumber_Select].Undo
Response = acDataErrContinue
End If

End Sub

--
Dave Hargis, Microsoft Access MVP


"RichKorea" wrote:

I'm using an unbound combo box as the entry point for calling up service
records by the service tag number. The Row Source for the combo box is a
query that gets all of the existing tag numbers. The user can then start
typing in the tag number, and if it already existing, the combo box will go
to the right value, and then when the user hits enter or tab, the After
Update event runs a FindFirst to find the record. Setting Me.Bookmark loads
up the existing tag information and the user can update the tag.

For the times when the user is entering a number for the first time, I want
to use the NoMatch results to create a new record, store the tag number in
the tag field, and then the user can start entering data. I tried using
DoCmd.GoToRecord , , acNewRec, but I get an error message: Run-time error
'2499' You can't use the GoToRecord action or method on an object in Design
view.

The form is Open, not in Design View.

The code I'm using for the event is:
Private Sub SRNumber_Select_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SRNumber] = '" & Me![SRNumber_Select] & "'"
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
End If
End Sub

Thanks, Rich

Hi Klatuu, I added the code you recommended, and I was able to get it to
work, but not without some kluging. My question follows the description of
what happened and my work around.

The NotInList event for the unbound combo box would trigger the creation of
a new record, but when the Me.Requery executed, it would trigger another
NotInList event, which would attempt to create a duplicate record. I got
around this by turning off the LimitToList property before doing the requery,
and turning it back on again after the requery completed.

Once the second NotInList event was prevented, I found Me.Requery was also
triggering an AfterUpdate event for the same combo box I normally use to find
the requested record (find and set the bookmark), but for a new record, the
search is not coming up with the new record, so the form is left on the prior
record. The AfterUpdate event is then followed by a Form_Current event,
which runs through some code to lookup values for unbound text boxes based on
info in the selected record, and then stuffs the record key from a combo box
that is linked to the query (and the table) into the SRNumber_Select combo
box.

After all that, the Me.Requery finishes, and then the code you suggested
executes, but now I've got the "wrong" record ID in the combo box. The code
completes and the form is still on the prior record data, but when I try to
scroll to a different record, I get an error message (trying to create a
duplicate key).

After playing for a while, I got it to work by adding a global variable
NewRecordStatus that I set before the Me.Requery and reset afterwards. In
the AfterUpdate and Form_Current code, I bypass everything if it's a new
record, which does what I need.

Question – Is turning off the NotInList property and using a global variable
to ignore code when adding a new record the best way to accomplish my goal,
or is there a more elegant method?

Thanks - Rich

"Klatuu" wrote:

You use the combo's Not in List event for this. First, you have to set the
Limit To List property to Yes so the Not In List event will fire.

For starters, take the GotoRecord code out of the AfterUpdate event:
(note changes for simplicity:

Private Sub SRNumber_Select_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[SRNumber] = """ & Me![SRNumber_Select] & """"
If Not rs.NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

Then create a Not In List event for the combo:

Private Sub SRNumber_Select_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Whatever Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO NameOfTable (SRNumber) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery

With Me.RecordsetClone
.FindFirst "[SRNumber] = """ & Me![SRNumber_Select] & """"
If Not rs.NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Response = acDataErrAdded
Else
Me![SRNumber_Select].Undo
Response = acDataErrContinue
End If

End Sub

--
Dave Hargis, Microsoft Access MVP


"RichKorea" wrote:

I'm using an unbound combo box as the entry point for calling up service
records by the service tag number. The Row Source for the combo box is a
query that gets all of the existing tag numbers. The user can then start
typing in the tag number, and if it already existing, the combo box will go
to the right value, and then when the user hits enter or tab, the After
Update event runs a FindFirst to find the record. Setting Me.Bookmark loads
up the existing tag information and the user can update the tag.

For the times when the user is entering a number for the first time, I want
to use the NoMatch results to create a new record, store the tag number in
the tag field, and then the user can start entering data. I tried using
DoCmd.GoToRecord , , acNewRec, but I get an error message: Run-time error
'2499' You can't use the GoToRecord action or method on an object in Design
view.

The form is Open, not in Design View.

The code I'm using for the event is:
Private Sub SRNumber_Select_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SRNumber] = '" & Me![SRNumber_Select] & "'"
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
End If
End Sub

Thanks, Rich

Sorry, I left out an important line in the code.

This line:
Me.cboClientSearch.Undo
Should go right before this line:
CurrentDb.Execute ("INSERT INTO NameOfTable (SRNumber) " _
& "VALUES ('" & NewData & "');"), dbFailOnError

That will stop the repeated Not In List, but it cannot stop the Current
event from firing twice. It will fire once on the Requery and again when you
position to the new record. That is normal.

--
Dave Hargis, Microsoft Access MVP


"RichKorea" wrote:

Hi Klatuu, I added the code you recommended, and I was able to get it to
work, but not without some kluging. My question follows the description of
what happened and my work around.

The NotInList event for the unbound combo box would trigger the creation of
a new record, but when the Me.Requery executed, it would trigger another
NotInList event, which would attempt to create a duplicate record. I got
around this by turning off the LimitToList property before doing the requery,
and turning it back on again after the requery completed.

Once the second NotInList event was prevented, I found Me.Requery was also
triggering an AfterUpdate event for the same combo box I normally use to find
the requested record (find and set the bookmark), but for a new record, the
search is not coming up with the new record, so the form is left on the prior
record. The AfterUpdate event is then followed by a Form_Current event,
which runs through some code to lookup values for unbound text boxes based on
info in the selected record, and then stuffs the record key from a combo box
that is linked to the query (and the table) into the SRNumber_Select combo
box.

After all that, the Me.Requery finishes, and then the code you suggested
executes, but now I've got the "wrong" record ID in the combo box. The code
completes and the form is still on the prior record data, but when I try to
scroll to a different record, I get an error message (trying to create a
duplicate key).

After playing for a while, I got it to work by adding a global variable
NewRecordStatus that I set before the Me.Requery and reset afterwards. In
the AfterUpdate and Form_Current code, I bypass everything if it's a new
record, which does what I need.

Question – Is turning off the NotInList property and using a global variable
to ignore code when adding a new record the best way to accomplish my goal,
or is there a more elegant method?

Thanks - Rich

"Klatuu" wrote:

You use the combo's Not in List event for this. First, you have to set the
Limit To List property to Yes so the Not In List event will fire.

For starters, take the GotoRecord code out of the AfterUpdate event:
(note changes for simplicity:

Private Sub SRNumber_Select_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[SRNumber] = """ & Me![SRNumber_Select] & """"
If Not rs.NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

Then create a Not In List event for the combo:

Private Sub SRNumber_Select_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Whatever Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO NameOfTable (SRNumber) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery

With Me.RecordsetClone
.FindFirst "[SRNumber] = """ & Me![SRNumber_Select] & """"
If Not rs.NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Response = acDataErrAdded
Else
Me![SRNumber_Select].Undo
Response = acDataErrContinue
End If

End Sub

--
Dave Hargis, Microsoft Access MVP


"RichKorea" wrote:

I'm using an unbound combo box as the entry point for calling up service
records by the service tag number. The Row Source for the combo box is a
query that gets all of the existing tag numbers. The user can then start
typing in the tag number, and if it already existing, the combo box will go
to the right value, and then when the user hits enter or tab, the After
Update event runs a FindFirst to find the record. Setting Me.Bookmark loads
up the existing tag information and the user can update the tag.

For the times when the user is entering a number for the first time, I want
to use the NoMatch results to create a new record, store the tag number in
the tag field, and then the user can start entering data. I tried using
DoCmd.GoToRecord , , acNewRec, but I get an error message: Run-time error
'2499' You can't use the GoToRecord action or method on an object in Design
view.

The form is Open, not in Design View.

The code I'm using for the event is:
Private Sub SRNumber_Select_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SRNumber] = '" & Me![SRNumber_Select] & "'"
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
End If
End Sub

Thanks, Rich

I added Me.cbo.ClientSearch.undo, but Visual Basic's coming back with Method
or data member not found. I didn't see anything resembling a ClientSearch
property under the form (Me.) or the combo box. Is there another name for
the property?

"Klatuu" wrote:

Sorry, I left out an important line in the code.

This line:
Me.cboClientSearch.Undo
Should go right before this line:
CurrentDb.Execute ("INSERT INTO NameOfTable (SRNumber) " _
& "VALUES ('" & NewData & "');"), dbFailOnError

That will stop the repeated Not In List, but it cannot stop the Current
event from firing twice. It will fire once on the Requery and again when you
position to the new record. That is normal.

--
Dave Hargis, Microsoft Access MVP


"RichKorea" wrote:

Hi Klatuu, I added the code you recommended, and I was able to get it to
work, but not without some kluging. My question follows the description of
what happened and my work around.

The NotInList event for the unbound combo box would trigger the creation of
a new record, but when the Me.Requery executed, it would trigger another
NotInList event, which would attempt to create a duplicate record. I got
around this by turning off the LimitToList property before doing the requery,
and turning it back on again after the requery completed.

Once the second NotInList event was prevented, I found Me.Requery was also
triggering an AfterUpdate event for the same combo box I normally use to find
the requested record (find and set the bookmark), but for a new record, the
search is not coming up with the new record, so the form is left on the prior
record. The AfterUpdate event is then followed by a Form_Current event,
which runs through some code to lookup values for unbound text boxes based on
info in the selected record, and then stuffs the record key from a combo box
that is linked to the query (and the table) into the SRNumber_Select combo
box.

After all that, the Me.Requery finishes, and then the code you suggested
executes, but now I've got the "wrong" record ID in the combo box. The code
completes and the form is still on the prior record data, but when I try to
scroll to a different record, I get an error message (trying to create a
duplicate key).

After playing for a while, I got it to work by adding a global variable
NewRecordStatus that I set before the Me.Requery and reset afterwards. In
the AfterUpdate and Form_Current code, I bypass everything if it's a new
record, which does what I need.

Question – Is turning off the NotInList property and using a global variable
to ignore code when adding a new record the best way to accomplish my goal,
or is there a more elegant method?

Thanks - Rich

"Klatuu" wrote:

You use the combo's Not in List event for this. First, you have to set the
Limit To List property to Yes so the Not In List event will fire.

For starters, take the GotoRecord code out of the AfterUpdate event:
(note changes for simplicity:

Private Sub SRNumber_Select_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[SRNumber] = """ & Me![SRNumber_Select] & """"
If Not rs.NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

Then create a Not In List event for the combo:

Private Sub SRNumber_Select_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Whatever Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO NameOfTable (SRNumber) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery

With Me.RecordsetClone
.FindFirst "[SRNumber] = """ & Me![SRNumber_Select] & """"
If Not rs.NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Response = acDataErrAdded
Else
Me![SRNumber_Select].Undo
Response = acDataErrContinue
End If

End Sub

--
Dave Hargis, Microsoft Access MVP


"RichKorea" wrote:

I'm using an unbound combo box as the entry point for calling up service
records by the service tag number. The Row Source for the combo box is a
query that gets all of the existing tag numbers. The user can then start
typing in the tag number, and if it already existing, the combo box will go
to the right value, and then when the user hits enter or tab, the After
Update event runs a FindFirst to find the record. Setting Me.Bookmark loads
up the existing tag information and the user can update the tag.

For the times when the user is entering a number for the first time, I want
to use the NoMatch results to create a new record, store the tag number in
the tag field, and then the user can start entering data. I tried using
DoCmd.GoToRecord , , acNewRec, but I get an error message: Run-time error
'2499' You can't use the GoToRecord action or method on an object in Design
view.

The form is Open, not in Design View.

The code I'm using for the event is:
Private Sub SRNumber_Select_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SRNumber] = '" & Me![SRNumber_Select] & "'"
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
End If
End Sub

Thanks, Rich