Not in the list event

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Hi,

I have combo box the takes data from Customerstbl. If a user type into the
combo box i would like to propmt him with massage and give hm the option to
add it to the table.

What is the correct code in the Not in the list event?

Thank you ,

Tom
 
Hi,

The comboBox in the form called CustomerName.
Table name : Customers
Field in the table: CustomerName

Here is the code for NotinList event:

Private Sub CustomerName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "MyProject")
If i = vbYes Then
strSQL = "Insert Into Customers([CustomerName]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Why it's not working?

Thanks,

Tom
 
Ok, I tried a different code:
Private Sub CustomerNamecbx_NotInList(NewData As String, Response As
Integer)
Dim db As Database
Set db = CurrentDb


If MsgBox("Do you want to add this value to the list?", vbYesNo +
vbQuestion, "Add new value?") = vbYes Then
db.Execute "INSERT INTO MyCustomers (CustomerName) VALUES (""" & NewData &
""")", dbFailOnError


Response = acDataErrAdded

Else


Me.CustomerNamecbx.Undo

Response = acDataErrContinue

End If

db.Close
Set db = Nothing

End Sub


Still, it does not work.

Please HELP

TIA,

Tom



Tom said:
Hi,

The comboBox in the form called CustomerName.
Table name : Customers
Field in the table: CustomerName

Here is the code for NotinList event:

Private Sub CustomerName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "MyProject")
If i = vbYes Then
strSQL = "Insert Into Customers([CustomerName]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Why it's not working?

Thanks,

Tom

Daniel said:
Tom

Take a look at

http://www.cardaconsultants.com/en/msaccess.php?lang=en&id=0000000011#notinlist

for an example, this is something that you can costumize depending on
the situation. If you search this forum, or google not in list even
you'll get tons more examples.
Click to expand...
Click to expand...
 
Back
Top