info in form to automatically find record (1 Viewer)

[kgcrowther]

It seems like this has been discussed, but I can't find it.

I want a form to find the first record that matches what has been filled out so far in the form. As you fill in boxes it will go to the first record that matches. As soon as you fill out a field that makes the form not match any records then you can just finish filling out the form and add the info as a new record.

Does anyone know how to do this or can anyone direct me to the discussion string where it was being discussed?

Kenneth

[This message has been edited by kgcrowther (edited 06-15-2001).]

 

[Jack Cowley]

Create a combo box on your form using the Wizard. On the first screen of the Wizard select the third item, "Find a record...". This will allow you to do what you want. If the user enters data that is not in the the list then you would use the Not In List event of the combo box to add the new data.

 

[kgcrowther]

That was a great idea. I went to the wizard and created the combo box and then looked at what it did.

The wizard put the following in the AfterUpdate event:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[field in record] = '" & Me![field on form] & "'"
Me.Bookmark = rs.Bookmark

I added the following to the On not in list and it works great:

On Error GoTo Err_Add_record_Click

DoCmd.GoToRecord , , acNewRec

Exit_Add_record_Click:
Exit Sub

Err_Add_record_Click:
MsgBox Err.Description
Resume Exit_Add_record_Click

The only problem is that I did this for multiple boxes so that as you go down it to the next box it finds a more specific record. But when you go back up it doesn't find a record, but rather adds a new record to match what you've entered. Is there anything I could do to prevent this?

Kenneth