Problem with NotInList Event 1

[mommom]

Below is a look at my NotInList. The problem is that it will not tell you that the Organization is not in the current list, and will not add. It just goes to the next record.

Private Sub ORGANIZATION_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 Organization " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new Organization to the current list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Organization?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Organization", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ORGANIZATION = 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


My first NotInList event works fine. They are basically the same one is just asking for SSN and the other Organization.

Can someone help me figure out what the problem is.

Thank you

 

[Remou]

Have you set the Limit to List property to Yes? Also, I would worry 'On Error Resume Next', it has no place in the code.

 

[TheAceMan1]

mommom . . .

Transfer your code to the [blue]NotInList[/blue] event . . .

See Ya! . . . . . .

 

[Remou]

TheAceMan1, mommom says:

"Private Sub ORGANIZATION_NotInList(NewData As String, Response As Integer)"

Which looks to me like the NotInList event.

 

[mommom]

Ramou - I didn't have the Limit to List property set to yes. I have changed it and it does work. But if I say no that I do not want it to save, receive Run-time error '91': Object variable or With block variable not set. What would I need to do to fix this problem?

Thanks for helping me with this.

 

[Remou]

These:
[tt]rs.Close
Set rs = Nothing
Set db = Nothing[/tt]
Need to be moved to before End If. You only set db and rs if the answer is yes, so they do not exist if you say no, hence the error.

 

[danvlas]

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Organization?") = vbNo Then
Response = acDataErrContinue
Exit Sub

or... get rid of rs.Close. Set rs=Nothing should be enough...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[mommom]

Thanks so much for your help with this.