Search for record and add new if not found

[spoole]

Good morning,
I have a form that is being used to add new clients to a database as well as enter enrollment information into our program. There is a subform that acts as a mini report showing how many hours of classes the client has taken in any of our different class types (the attendance is entered on a daily basis for each of the classes). In the form header I have a search box that allows the client to be searched for by last name (although I think I am going to change it to look up the client by an ID number assigned by the state that every client has and is unique and known by the end users). There is also a button for "Add New Client" that takes the user to the next blank record.


Currently when you search for a person who is in the database already, their records will populate the form and all is good. However when you try to find a record of a person not in the database the "Select an item from the list, or enter text that matches one of the listed items" pops up. Then the user has to delete the entry that they already entered before they can add a new client. The "Add New Client" button does not work unless the search box is empty.
What I would like to happen is to have a prompt to enter a new record pop up and the cursor to move to the first field that needs to be entered in the form. I'm assuming that I need to do something in the "On Not In List" property but I'm not exactly sure what.
Help would surely be appreciated!!!
Thanks,
Shannon

Also, I have been building this database in 2003 but it will be used in 2010. Will I run in to trouble with keeping my formatting, properties and code? I can stop now and install 2010 if need be.....

[Bob Fitz]

Can post a copy of your db in A2003 mdb format, with just just a few dummy records.

[spoole]

Database Fake Data.mdb

Hope this works. I had to really pare down the database to get it under 500 kb. The subform no longer works so just ignore it. An error pops up about the subform since I didn't delete it only the data sources for it. Also got rid of a bunch of tables that go to combo boxes on the form so ignore those, too. The form that I want to have the lookup work on is frmEnterLIFTClient.
Thanks again for any help!!!
Shannon

[June7]

Next time zip the file. Up to 2mb zip allowed.

Form open errors because missing tblTraining.

Try:

Private Sub Combo41_NotInList(NewData As String, Response As Integer)
If IsNull(DLookup("LiftClientID", "tblLIFTClient", "[LiftClientID] = " & Nz(Me.Combo41, 0))) Then
    MsgBox "Client Not Found"
    Me.Combo41 = Null
    Response = acDataErrContinue
End If
End Sub

Private Sub Combo41_AfterUpdate()
With Me.RecordsetClone
    .FindFirst "[LiftClientID] = " & Nz(Me.Combo41, 0)
    Me.Bookmark = .Bookmark
    Me.Combo41 = Null
End With
End Sub

Consider this as the RowSource for combobox (and revise other properties as needed):
SELECT LiftClientID, ClientLast & ", " & ClientFirst AS ClientName, SIDNumber FROM tblLIFTClient ORDER BY ClientLast, ClientFirst;

[spoole]

Hi June7,
That does get the "Client not Found" box to pop up which is far superior to the error box, but it doesn't take the user to the next field to enter the new data. Which if it's too difficult I could always add "Please click on Add New Record" to the pop up box except that the Add new record box won't work after you search for a record that isn't already there! When I click onn Add NEw Record I get a Run-Time error (2046: The command or action 'RecordsGoToNew' isn't available now. To make it work I need to go to an existing record first and then I can click that button. Any ideas? I added the full db zipped up this time. Thanks for the tip!

[June7]

If you want to set focus on date box after search then in both events: Me.PFDateInfoForm.SetFocus

The AddNewClient button works even after a bad search. It doesn't work if already on a new record. So:

If Not Me.NewRecord Then DoCmd.RunCommand acCmdRecordsGoToNew

Recommend moving the button so the combobox dropdown doesn't cover it. Also, better names for controls, like btnAddRec instead of Toggle66. The button acts like a toggle. I would use a command control instead of toggle but Access won't let me change to command. Delete and create command button.

[spoole]

Thank you, thank you!
I moved my button as well and I will rename it. Still working out the bugs (obviously!)

[June7]

Did you change it to a command button? It makes more sense to me than a toggle.