Hi Sue,
You should have error handling code in every procedure. Your code can never
be bulletproof unless you do. I believe there is some sample error handling
code at
www.mvps.org/access There is a lot of other great Access lore there
as well.
There is a pretty good way of doing what you're doing that I use pretty much
by havit: The City field would be a combobox with property Autoexpand set
on and Limit To List set to yes. The effect of that is that if the city is
allready present in the list it will quickly come to the fore, you needn't
even type the whole thing if the first few haracters are unique. However,
if the entry doesn't match a city already in the list, the NotInList event
of the combobox will fire. That's where you'll have code to put up a msgbox
telling them what they tried to enter and asking if they really want to do
that. Your cole will have to handle both Yes and No responses properly, for
Yes, you have to open the recordset and put in the new city. You'll have to
handle the Data Error Added and Data Error continue properly so that the
other message won't still come up after you've already handled the error.
In the Added path you'll call yoour form to add the city.
I've include a sample NotInList procedure from one of my applications.
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
DoCmd.OpenForm "frmContactPractitioner", , , , , acDialog
Forms![frmContactPractitioner]![cboFindProvider].SetFocus
fSendKeys "{F4}", True
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
DoCmd.Beep
MsgBox strErrMsg, vbInformation, "cboFindPractitioner_NotInList"
Resume cboFindPractitioner_NotInList_Exit
End Select
End Sub
HTH
