add new record if not found in search

[Guest]

I have a combo box that lists all projects. I want to use it to search the
form (which has status records for some of the projects) and if it finds a
match position the cursor for the user to start editing the record. That part
works swell.

If there is no match I want to add a new record, populate a few fields, and
position the cursor for the user to start entering data.

How do code "if there isn't a match" ?

 

[Guest]

I'm going to assume that you are not adding new entries to the list of
projects. That the combo box is not bound and is in the form header of a
continuous form. And that you are not using a subform for your status
records.

When you do not have a match use DoCmd.GoToRecord , , acNewRec
This will put the focus on a new entry in which you can populate the columns
with VBA.

You can put default values into the form controls before the DoCmd that will
be used when the DoCmd is executed.

If the combo box is in a main form and the status records are in a subform
then put the DoCmd and VBA in a property in the subform. You can call the
subform property from the main form.

 

[Guest]

The correct approach is to use the Not In List event of the combo box.
You must have the Limit To List property of the combo set to Yes for the
event to fire.

One approach is to use SQL to create a new record with the primary key
field, then move the record to the form's current record. Here is an example
from one of my apps:

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
With Me.RecordsetClone
.FindFirst "[Activity] = '" & NewData & "'"
If Not .NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub

 

[Guest]

Thanks Steve. Your assumptions are correct.

I figured I'd use the "DoCmd.GoToRecord , , acNewRec" to create the new
record. What I can't figure out is WHEN to use it. That is, what is the
coding to use to say, "if there is no match to the search" do this...

 

[Guest]

Thanks for your response, Dave. My situation isn't quite "Not in List". The
unbound combo box has a complete list of all possible projects. If the user
selects one that already has a record in the status table, that record is
displayed. If the user selects one that does not have a record, I want to add
a new record.

It is not that the desired record is "Not In List" in the combo box. It is
that there isn't a matching record in the record set of the form.

Sorry that wasn't clear ...

The correct approach is to use the Not In List event of the combo box.
You must have the Limit To List property of the combo set to Yes for the
event to fire.

One approach is to use SQL to create a new record with the primary key
field, then move the record to the form's current record. Here is an example
from one of my apps:

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
With Me.RecordsetClone
.FindFirst "[Activity] = '" & NewData & "'"
If Not .NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


--
Dave Hargis, Microsoft Access MVP

I have a combo box that lists all projects. I want to use it to search the
form (which has status records for some of the projects) and if it finds a
match position the cursor for the user to start editing the record. That part
works swell.

If there is no match I want to add a new record, populate a few fields, and
position the cursor for the user to start entering data.

How do code "if there isn't a match" ?

 

[Guest]

With Me.RecordsetClone
.MoveFirst
.FindFirst "[pkChangeReasonID] = " & lngRecordNum
If .NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = .Bookmark
End If
End With