NotInList event

[Moira Triance]

OK I give up, I have been round and round this problem and searched the forums for the last 2 hours and I am still stuck.
Here is my simple code for adding a record to a combo box, which works fine except I still get the message "the text you entered isn't in the list" AFTER it runs the event proc and it has added it to the list. Please help because I am going greyer by the minute! It has got to be something really obvious but I just can't see it. Thanks for any help.

Private Sub Amount_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
Dim db As Database

Set db = Currentdb
Set rst = db.OpenRecordset("tbMedIns")

'add value to tbMedIns if not in list

If MsgBox("Amount is not in list. Add it?", vbOKCancel, "Medical Insurance") = vbOK Then
' Set Response argument to indicate that data is being added.

rst.AddNew
rst!MedInsRate = NewData
rst.Update
Response = acDataErrAdded
Else
Response = acDataErrContinue
Exit Sub
End If

End Sub

 

[BLeslie88]

Access 2000?

Dim rst As DAO.Recordset
Set rst = db.OpenRecordset("temp1", dbOpenDynaset)

Was 2 lines I made corrections on to add the string to the table.

If this fails too, re-assign or requery the recordsource of the table to the combo box after adding it.

Regards,

 

[Moira Triance]

Thanks for your help. I should have said that I am using Access 2002.
I have made the changes but unfortunately still getting the same problem. My code now looks like this:

Private Sub Amount_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
Dim db As DAO.Database

Set db = Currentdb
Set rst = db.OpenRecordset("tbMedIns", dbOpenDynaset)
'add value to tbMedIns if not in list

If MsgBox("Amount is not in list. Add it?", vbOKCancel, "Medical Insurance") = vbOK Then
' Set Response argument to indicate that data is being added.

rst.AddNew
rst!MedInsRate = NewData
rst.Update
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Requery
Exit Sub
End If

End Sub

Could it be anything to do with my combo box being in a subform? (I am really clutching at straws here) Any more help greatly appreciated.

 

[ColinEssex]

Hi Moira

I did this ok with this code

Private Sub Amount_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox("Amount is not in list. Add it?", vbOKCancel, "Medical Insurance") = vbOK Then

Set rst = CurrentDb.OpenRecordset("tbMedIns")

rst.AddNew
rst!MedInsRate = NewData
rst.Update
Set rst = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
Amount.Undo
End If

End Sub

Having said all that - I haven't tried it in A2000 but you never know - it might help.

Good luck
Col

 

[Moira Triance]

Thanks Colin, I tried it but it still gives the standard "text not in list message" after adding it to the list, which means the user has to cancel the message then reselect the newly added item from the list. If it works fine in earlier versions, could this be a problem with A2002 perhaps? I now have this same problem occuring with 3 other forms and another database so I am starting to think it might be 2002 that is the problem.
Thanks for helping.

 

[Moira Triance]

NotInList event in Access 2002

This might help anyone else who has had this problem in Access 2002. I took up the problem with Microsoft and they reported this is a bug in 2002. To get around the problem of the standard "text not in list" message showing even with NotInList event code, this is what they recommend and it worked for me

1. Set LImitToList property to No
2. Remove NotInList property
3. Insert this code for After Update and Before Update events on the combo box.

Private Sub Combo28_AfterUpdate()
Me.Combo28.Requery
End Sub


Private Sub Combo28_BeforeUpdate(Cancel As Integer)
Dim rst As Recordset
Dim db As DAO.Database
If DCount("ControlInTable", "tbTableName", "ControlInTable = " & Me.Combo28) < 1 Then
Set db = Currentdb
If MsgBox("Combo28 is not in list. Add it?", vbQuestion + vbYesNo) = vbNo Then
MsgBox "Please try again."
Cancel = True
End
Else
Set db = Currentdb
Set Rs = db.OpenRecordset("tbMedIns", dbOpenDynaset)
Rs.AddNew
Rs!MedInsRate = Me.Combo28
Rs.Update
End If
End If
End Sub

 

[kfulton]

Not in list

Hello,

I'm really new at this, I'm trying to figure out how to get this to work with my database. Do I substitute Combo28 with my own combo box name? Should I substitute tbTableName with my own table name, and what do I substitute for ControlInTable? Are there anymore substitutions I should be making? Sorry for all the questions, I am totally clueless....!

 

[Moira Triance]

Hi
Yes - you need to substitute all occurrences of Combo28 with the name of your combo box, tbTableName with the name of your table and ControlInTable with the name of the control in your table.
In the line:
Set Rs = db.OpenRecordset("tbMedIns", dbOpenDynaset)
you should substitute tbMedIns with the name of your table (within the quote marks) and in the line
Rs!MedInsRate = Me.Combo28
you should substitute MedInsRate with the name of the control in your table (I should have edited those out before posting my code, oops!)

Hope that helps.
Moira