Adding to combo box

[Leo]

I have a combo box and the list of choices are drawn from a table. But the
choices are not limited to the table (Limit to the the list is No on combo
property).
The combobox name is ComboRecc
The table is called tblRECC
The only field in the table is named RECC (text field with max chr at 150)
When the user writes his own and does not select one that is already there I
want that answer (If it truely not there) added to the table.

What will be the code and where will I write that?

Thanks
Leo

 

[Marshall Barton]

I have a combo box and the list of choices are drawn from a table. But the
choices are not limited to the table (Limit to the the list is No on combo
property).
The combobox name is ComboRecc
The table is called tblRECC
The only field in the table is named RECC (text field with max chr at 150)
When the user writes his own and does not select one that is already there I
want that answer (If it truely not there) added to the table.

What will be the code and where will I write that?

You can use the NotInList event to detect when the user
enters a new item, then Execute an append query to add it to
the table. Use the Response argument tto tell Access to
suppress the message and requery the list.

Dim SQL As String
SQL = "INSERT INTO tblRECC (RECC ) Values(""" & NewData &
""")"
CurrentDb.Execute SQL
Response = acDataErrAdded

An alternative is to use the AfterUpdate event:

Dim SQL As String
If Me.ComboRecc.ListIndex = -1 Then
SQL = "INSERT INTO tblRECC (RECC ) Values(""" &
Me.ComboRecc & """)"
CurrentDb.Execute SQL
End If

 

[RonaldoOneNil]

You need to set Limit to list to Yes. This will then fire the On Not in List
Event.
You can then put code in this event to inform the user that they have
entered a value not currently in the list, fo you wish to add it to the table
?
If they answer yes then write your code statements to update the table with
the new value.

 

[Leo]

That means every time they add they have to say yes. Is there any way to
bypass that?
What will be the exact code to add it to the table. I am relatively new to
this as you can see!

Thanks
Leo

 

[Leo]

That means every time a new item is typed the user has to select yes or no.
Is there any way to by pass that and add it directly to the table?

In any event, what will the code to add it to the table? As you can see I am
relatively new to this!

Thanks
Leo

 

[RonaldoOneNil]

You can do it without a prompt if you like.

Private Sub ComboRecc_NotInList(NewData As String, Response As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [tblRECC] ([RECC]) VALUES ('" & NewData & "')"
DoCmd.SetWarnings True
Response = acDataErrAdded
End Sub

 

[Marshall Barton]

You can do it without a prompt if you like.

Private Sub ComboRecc_NotInList(NewData As String, Response As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [tblRECC] ([RECC]) VALUES ('" & NewData & "')"
DoCmd.SetWarnings True
Response = acDataErrAdded
End Sub

The Execute method does not generate any warnings and it is
synchronous (so you know that the record was added when the
next line of code runs).

 

[Leo]

Thank you for your interest
Will try and let you know

Thanking you
Leo

You can do it without a prompt if you like.

Private Sub ComboRecc_NotInList(NewData As String, Response As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [tblRECC] ([RECC]) VALUES ('" & NewData & "')"
DoCmd.SetWarnings True
Response = acDataErrAdded
End Sub

That means every time a new item is typed the user has to select yes or no.
Is there any way to by pass that and add it directly to the table?

In any event, what will the code to add it to the table? As you can see I am
relatively new to this!

Thanks
Leo