Just a thought about a somewhat different approach:
Put a combobox on your form (cboFindPatient) with LimitToList enabled
and Autoexpand enabled. Base the combobox on your tblPatient with the
underlying query returning the primary key and patient last name and
patient first name and MI sorted Ascending.
The combobox Wizard will help you set it up and generate the
appropriate code for the operation you indicate. You want to go to a
particular record....
The selected record will populate the form and your users can inspect
and compare the record there with the one they know actually applies
to the current patient. If they're definitely not the same person
then add a new record.
If you were unable to find the Patient's name then continue to type
the Last Name you're looking for and click out of the combobox. That
will fire the NotInList event. You will have to write some code to
handle that but essentially, you'll put up a msgbox that will inform
your user that the name doesn't exist, would they like to add it? If
they want to add it, send the form to a new record and place the name
already entered in the Last Name field.
If the name that was not in the list is simply a typo the user will
simply indicate that they don't want to add it.
There is Access Help on the NotInList event. I've enclosed a working
example FYI. Your mileage may vary. This procedure makes use of
"fSendKeys" a safer version of the standard SendKeys provided by
Access. The code for that functionality is available at
www.mvps.ort/access It was contributed by Ken Getz.
Where the sample code opens a different form, you may wish to simply
go to a new record with the current form.
----------------------------------------------------------------------
-----------------------------------
Private Sub cboFindPractitioner_NotInList(NewData As String, Response
As Integer)
'============================================================
' Purpose: If not in list, does she want to add it?
' Copyright: Larry Daugherty
' Company: Business Process Solutions
' Programmer: Larry Daugherty
' Called From:
' Date: 11/13/02
' Parameters:
'============================================================
On Error GoTo cboFindPractitioner_NotInList_Err
Dim strErrMsg As String 'For Error Handling
Dim strMsg As String
Dim Holder As String
Holder = NewData
strMsg = "'" & NewData & "' is not listed. "
strMsg = strMsg & "Would you like to add an entry?"
If MsgBox(strMsg, vbYesNo, "Practitioner") = vbNo Then
fSendKeys "{ESC}"
Response = acDataErrContinue
Else
Response = acDataErrAdded
'Open the form to add the new one and stay there until done
DoCmd.OpenForm "frmContactPractitioner", , , , , acDialog
End If
cboFindPractitioner_NotInList_Exit:
On Error Resume Next
Exit Sub
cboFindPractitioner_NotInList_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number)
& vbCrLf
strErrMsg = strErrMsg & "Error Description: " &
Err.Description
MsgBox strErrMsg, vbInformation,
"cboFindPractitioner_NotInList"
Resume cboFindPractitioner_NotInList_Exit
End Select
End Sub
----------------------------------------------------------------------
--------------------------
HTH
--
-Larry-
--
You could open your table to design view and click on the address field. at
the bottom where it says Indexed, select Yes, No Duplicates.
- Show quoted text -
I only want the database to warn the user if another record contains
some of the same information, for instance it is entirely possible for
two people to have the same first and last name (we have 20,000
patients), also more than one member of the same family (same
address).
