Limit to List - problem

[David Ball]

Hi,
I have a “Complaints” table that has a field for the originator of the complaint. There is also a table for Originators.
There is a “Complaints” form with a Combo Box where the originator of the complaint can be selected from the drop-down list or entered into the combo box, if not already there. I have set the “Limit to List” property for this combo box to “No.”
This works fine when I select a value from the combo box but when I try to type a name into the combo box I get the message “You cannot add or change a record because a related record is required in table “tblOriginator”.
I would have thought that having “Limit to List” set to “No” would allow me to enter a value into the combo box that was not yet in tblOriginator, and then store that value in the table.
Any ideas on what the problem may be?

Thanks very much
Dave B

 

[John Big Booty]

Is your originator combo feeding off a table that contains an auto-number ID?

 

[David Ball]

No, there is just an Originator name (Joe Jones) that is the Primary Key.

 

[John Big Booty]

Have a look at this post for a method of adding records to a combo using the On Not In List and On Double Click events.

 

[apr pillai]

I think you have set relationships one to many, tblOriginator on the one side and Complaints on the many side, on the relationships grid. The Error message shows this situation very clear. You have to add the new Originator Record in the tblOriginator before you are able to add its corresponding record with the same orginator code in the Complaints Table.

When Limit to List is set to Yes, you can add a VBA Routine in the Not in List Event Procedure to add a new record, with the new Code entered into the combobox, directly into the tblOriginator first and refresh the combobox to accept the new code in the Complaint Table's Originator field.