OnNotInList Event (Not Working) 1

[mommom]

I have put in the code listed below. However, Nothing is happening. It will not ask me if I want to add to the list or anything else. Can someone tell me what I did wrong?


Private Sub SSN_NotInList(NewData As String, Response As Integer)
'Suppress the default error message.
Response = acDataErrContinue
'Prompt user to verify they wish to add new value.
If MsgBox("The SSN is not in list. Add it?", vbYesNo) = vbYes Then
'Set Response argument to indicate that data is being added.
'Open a recordset of the SSN Table.
Dim db As Database
Dim rstSSN As Recordset
Dim sqlSSN As String
Set db = CurrentDb()
sqlSSN = "Select * From SSN"
Set rstSSN = db.OpenRecordset(sqlSSN, dbOpenDynaset)
'Add a new SSN with the value that is stored in the variable NewData.
rstSSN.AddNew
rstSSN![SSN] = NewData
rstSSN.Update
'Inform the combo box that the desired item has been added to the list.
Response = acDataErrAdded
rstSSN.Close 'Close the recordset

End If

End Sub

 

[PHV]

What is the value of the SSN.LimitToList property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mommom]

The LimitToList is set to Yes.

 

[TheAceMan1]

How are ya mommom . . .

Try this:

[blue]   Dim db As DAO.Recordset, rst As DAO.Recordset, SQL As String
    
   If MsgBox("The SSN is not in list.  Add it?", vbYesNo) = vbYes Then
      Set db = CurrentDb()
      SQL = "Select * From SSN"
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
      rst.AddNew
      rst![SSN] = NewData
      rst.Update
      Set rst = Nothing
      Response = acDataErrAdded
   Else
    Response = acDataErrContinue
   End If[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

mommom . . .

Hit submit too soon. Should be:

[blue]   Dim db As DAO.Recordset, rst As DAO.Recordset, SQL As String
    
   If MsgBox("The SSN is not in list.  Add it?", vbYesNo) = vbYes Then
      Set db = CurrentDb()
      SQL = "Select * From SSN"
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
      rst.AddNew
      rst![SSN] = NewData
      rst.Update
      Set rst = Nothing
      Response = acDataErrAdded
   Else
      [purple]Me.Undo[/purple]
      Response = acDataErrContinue
   End If[/blue]

See Ya! . . . . . .

 

[mommom]

Hello AceMan1,

I changed it, but it still doesn't work. What happens is I put in the new SSN but for some reason when I hit either tab or enter key it goes blank.

Not sure why that is happening.

 

[mommom]

What I am getting now is Run-time error '13':

Type mismatch

 

[mommom]

Forgot to add where the error is coming from:

Set db = CurrentDb()

 

[mommom]

Forgot to add where the error is coming from:

Set db = CurrentDb()

 

[PHV]

Replace this:
Dim db As DAO.Recordset
with this:
Dim db As DAO.Database

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

PHV . . .

Thanks! ;-)

See Ya! . . . . . .

 

[mommom]

The change worked, it does go into the list. However, now once it you click yes add to list it automatically goes to a new record instead of allowing me to continue to work on the same page. Do you know what can be causing this??

 

[TheAceMan1]

mommom . . .

Is the combobox the last field in the [blue]Tab Order[/blue] ([blue]MenuBar - View - Tab Order[/blue])?

See Ya! . . . . . .

 

[mommom]

Thanks AceMan - that did it.