Not on list event as module

  • Thread starter Thread starter Annelie
  • Start date Start date
A

Annelie

I used the following code, which I got from a website, to create a not on
list event. It worked just fine. However, since I have Jobs, Employees and
Contractors with addresses, I wanted to use it for all forms and I made it a
module.

I can't figure out how to call the module. I have tried to call it, from
NotInList Event, on update, on change, but nothing works.
Perhaps I need to do some Dim statement? The module is option explicit.
Annelie

Public Sub CbxCity_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available City Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
Table?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCity", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!City = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
 
Hi,



In the NotInList event of the control, xyz, add the call:

======================
Private Sub xyz_NotInList(NewData As String, Response As Integer)
CbxCity_NotInList NewData, Response
End Sub
======================

Be sure the LimitToList property of the xyz control is set to True (it is
false by default).

Be sure you ***DON'T*** use ByVal for Response:

Public Sub CbxCity_NotInList(NewData As String, ByVal Response As Integer)

(you didn't type ByVal, but be sure it is not there; it should be it like
you typed it here).



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top