make Hookom's AYS response form have custom event behaviors??

[tlyczko]

I have been working with Duane Hookom's AYS app in Access 2003
standalone and am having some difficulty making its Responses subform
"Rspns" dropdown box have certain custom behaviors depending on which
question is being asked.

I currently have code which compares the question code to activate the
NotInList event so I can ask the user whether they want to add the
entry to the list, plus two others where the Zoom portion is
automatically opened when the field gets the focus (I could live
without this one if necessary), and one more which validates a phone
number entry in the OnBeforeUpdate event.

The problem I am having is that the NotInList event does not always
fire, even though I set the field to be a Limit to List field.

Does anyone have experience with this little app?? I've been struggling
with this part for two days now...

Code I use in NotInList is here:

Private Sub Rspns_NotInList(NewData As String, Response As Integer)
'5/11/05 code to be able to add MSC people to the list
If InStr(Me.QstnText, "Person Assisting") Then
If MsgBox("The value you just entered (" & NewData & ") " & _
"is not in the list. Do you want to add it? " _
, vbQuestion + vbYesNo, "Add to list?") = vbNo Then
'They don't want to add it ... goto to exit label
Response = acDataErrContinue
GoTo Exit_Rspns_NotInList
Else 'ADD it to the list
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblResponsesList")
On Error Resume Next
rs.AddNew
rs.Fields("QstnID") = CLng(84)
rs.Fields("Rspns") = CStr(NewData)
rs.Update
Response = acDataErrAdded
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End If
Exit_Rspns_NotInList:
Me.Undo
'Me.Rspns.Value = Null
Exit Sub
Err_Rspns_NotInList:
GoTo Exit_Rspns_NotInList
End Sub

Code works correctly, but the NotInList event does not always
fire/happen, sometimes it does, sometimes it doesn't.

I have tried setting the Limit to List value for this particular field
in the survey design, and I tried setting the Limit to List property
for the field itself...I will go back to it soon...

Any suggestions would be much appreciated.

Thank you,
Tom

 

[tlyczko]

Thank you for reading...I started over again from scratch and figured
out that I had to test for QstnID instead of QstnText, as well that I
had to comment out a Me.Requery statement that was causing Access to
crash out. It seems to work properly now. If anyone is interested in
the other custom behaviors I created please email me.
Thank you,
Tom