NotInList Fails to Fire

[damian]

I've got a casacading combo box with the following code (see below) as the NotInList procedure. The only problem is that it fails to fire on occasions.
The only other code that I think may be contributing to the problem is a 'requery' of the same combo that has been inserted into the OnCurrent form event. i.e. Me.tblSiteAddress_ID.Requery



Private Sub tblSiteAddress_ID_NotInList(NewData As String, Response As Integer)
On Error GoTo tblSiteAddress_ID_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String


intAnswer = MsgBox("The site address " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Job Costing")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblSiteAddress([SiteAddress],[CompanyID]) " & _
"VALUES ('" & NewData & "','" & Form_JobCostingForm.tblCompanyName_ID & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new site address has been added to the list." _
, vbInformation, "Job Costing"
Response = acDataErrAdded
Else
MsgBox "Please choose a site address from the list." _
, vbInformation, "Job Costing"
Response = acDataErrContinue
End If
tblSiteAddress_ID_NotInList_Exit:
Exit Sub
tblSiteAddress_ID_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume tblSiteAddress_ID_NotInList_Exit
End Sub

Any ideas?

 

[Rich]

You shouldn't use Requery in the Form_Current event, move it elsewhere

 

[damian]

Thanks for the reply Rich.
It does appear that the OnCurrent code prevents the NotInList event from firing consistently.
However I need to get the contents of the combo to display everytime I scroll through the records - the OnCurrent requery is the easiest way to achieve this.
Are there any other options?

 

[damian]

To shed more light on the problem, I can edit the combo by simply adding a number, click on another control and the combo contents will lose the digit that was added - and strangely the On Not In List event isn't triggered.

 

[damian]

Further investigations reveal that with no current event code, this problems still occurs.
Any ideas would be appreciated.

 

[WayneRyan]

Damian,

Do you have LimitToList set to Yes?

Wayne

 

[damian]

Yes it is Wayne