In a form check if an existing record exists in the table, if so go to the record, if not add new

[MSommer]

I am trying to check for existing client records in a table based off user entry into a form using three fields: FirstName, LastName and DateofBirth. Once the user has entered the first name and last name and dob, if the record exists, go to the existing record so user can continue entry in the subform. If the record does not exist I'd like it to create a new client record. Below is the code. I currently have this code on the frmDateofBirth field AfterUpdate event. I'm wondering if the 'Dim ID as Long' is not compatable with the date field? I've tried several attempts using different methods but have not been successful. I've spent way too much time trying to figure this out. Any help is appreciated.

Private Sub frmDateofBirth_AfterUpdate()


Dim ID As Long

ID = Nz(DLookup("FirstName", "Clients", "FirstName=""" & Forms!EnrollClients!frmFirstName & """ And LastName=""" & Forms!EnrollClients!frmLastName & """ And DateofBirth=#" & Forms!EnrollClients!frmDateofBirth & "#"""), 0)
If ID = 0 Then
' Doesn't exist
If MsgBox("Client does not exsit. Add as new Client?", vbYesNoCancel + vbQuestion, _
"Add New?") = vbYes Then
' Add as new client
DoCmd.GoToRecord , , acNewRec
Forms!EnrollClients!frmFirstName = [FirstName] And frmLastName = [LastName] And frmDateofBirth = [DateofBirth]
Else
' Do nothiing
Exit Sub
End If
Else
'Client Exists
DoCmd.GoToRecord "ClientID=" & ID
End If




End Sub

[ranman256]

show a form that shows all clients : Last, First, Dob, continuous form
a text box to find the name, when user enters name, the AFTERUPDATE will filter:

sub txtBox_afterupdate()
if isNull(txtBox) then
   me.filterOn = false
else
   me.filter = "[LastName]='" & txtBox & "'"
   me.filterOn = true
endif
end sub

then the user can select the correct name, click DETAIL button to open the detail form
or
click ADD NEW button if not there.

[MSommer]

Thank you ranman256. I actually already have a button on the main form to open another search form where they can search by name & DOB then select the correct record to open or if it doesn't exist, close the form and start a new record. However, my fear is that they may forget to use the button to open and search for the record first so I was hoping to catch the possible dup if they just start typing in a new record on the main form. Also because clients can have the same name, was hoping to incorporate the DOB. I can train them and use the way it's functioning but my bet is I end up with dup client records if they forget to search first hence catching in the input form.

[ranman256]

exactly, hence the filter to see available existing names.
if not there, start new.

[MSommer]

So what you are saying is have the search form open when the main form loads to remind them to search first? That's a good idea. Let me try that. Technically, they could still close the search form and begin entering in the main form and still end up creating duplicates which is why I was trying to make it work at the time they are trying to create a new record in the main form but the search pop-up may work. I just hate messy data (dups). Thanks again.

[Gicu]

Where do you run the code you posted in post #1? You mention in post # 3 that is being run in the actual data entry form, so you must be loading that with its Data Entry property set to true which puts you at a new record already.
You could try something like this:

Private Sub frmDateofBirth_AfterUpdate()
Dim ID As Long
Dim sFname as string, sLName as String, sDOB as date


ID = Nz(DLookup("ClientID", "Clients", "FirstName=""" & Forms!EnrollClients!frmFirstName & """ And LastName=""" & Forms!EnrollClients!frmLastName & """ And DateofBirth=#" & Forms!EnrollClients!frmDateofBirth & "#"), 0)
If ID = 0 Then
	'Vlad: Doesn't exist so keep adding
	'If MsgBox("Client does not exist. Add as new Client?", vbYesNoCancel + vbQuestion, _
	"Add New?") = vbYes Then
	'Add as new client	
	'DoCmd.GoToRecord , , acNewRec 'Vlad: already a new record
Else
	sFname=Me.frmFirstName
	sLName=Me.frmLastName
	sDOB=Me.frmDateofBirth
	'we need to undo the data entry in the new record and navigate to the existing record
	me.Undo
	'change the data entry property to No
	Me.DataEntry=False
	'nagivate to record
	Dim rs as Dao.Recordset
	Set rs=Me.RecordsetClone
	rs.FindFirst "ClientId = " & ID
	Me.Bookmark=rs.Bookmark
	Set rs=Nothing
	'Exit Sub
	'add set focus for the next control after DOB
	Me.frmNEXTCONTROL.SetFocus
End If


End Sub

Cheers,

[MSommer]

The original code in #1 I was handling on after update of the DateofBirth field. User enters FirstName, LastName and DateofBirth right in the main entry form then I was hoping to check for the client and if they exist, start the user on the existing client record. If the client doesn't exist create new client and ID. This way the user doesn't have to open another form to search for the client or worse close that form and end up creating a duplicate client record. Seems like this should be doable and be less entry on the user part. Also better protects data integrity. For now, I have a separate search form that opens on load of the main form. Thank you Gicu. I will definitely try your option.

[Gicu]

I tried it and it seems to work, you need to open the form in Data entry mode (to be on a new record) and probably you'll need to add some checks before the dLookup that retrieves the Client ID to ensure that all three fields are populated. Give it a try and post back if stuck.
Cheers,