Saving data entered in a combo box

[FoxGolfer]

I have a one field table that stores the names of ships. This table is the source for a combo box in a data entry form. It works fine except if the user enters a new ship name that is not in the table, it stores the ship name in the data entry table but not in the ship name table.
How do I save the new ship name in the ship table?
TIA,
Tom

 

[WayneRyan]

Tom,

You can use the NotInList event of the combobox to:

DoCmd.RunSql "Insert Into Ships (ShipName) Values ('" & Me.cboShipName & "'")
Me.cboShipName.Requery

Wayne

 

[FoxGolfer]

Thanks for the tip; I'll try that.
Tom

 

[FoxGolfer]

Wayne,
I keep getting error messages when I use your expression:
... Values ('" & Me.cboShipName & "'")
I added the code and tried entering a ship named TEST into the combo box. (No, I did not hit the drop down, I just added the name TEST and hit Tab.)
I started with your expression and I've tried every variation I can think of and when I'm in the debugger, when I mouse over cboshipname, it returns a
(cboshipname = null), not TEST. cboshipname is the control name and shipname is the field name.
Is the the error ocurring because the value is null and therefore it can not add a null value? Why is it null?
Thanks,
Tom

 

[PHV]

In the NotInList event procedure of cboshipname you may try this:
DoCmd.RunSql "INSERT INTO tblShips (shipname) Values ('" & NewData & "');"
Response = acDataErrAdded

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

 

[FoxGolfer]

PH,
Thanks, that did it. I'm a relative newbie in Access and I just noticed that the NotInList proc contains a parameter, "NewData".
Thanks for the tip.
Tom

 

[rainsworth]

Hi - I am having some trouble along these lines. I am entering the data that does not appear in my "list" table, and the NotInList event is not being triggered.

 

[PHV]

The LimitToList property of the ComboBox must be True.

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