Duplicate Values Error/No Primary Key Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Access 2003, I have a one to many relationship in which an Order ID is the
primary key in one table and the foreign key in another table. In a form I
want to allow users to be able to either select a current Order ID (so
related information will appear in the form) or input a new Order ID in the
same combo box. The problem is that if the combo box has a Control Source of
the Primary Key Field, the user cannot select a previous Order ID b/c no
duplicate values are allowed. If the combo box has a Control Source of the
Foreign Key Field, the user can select previous Order IDs yet they cannot
enter new Order IDs because there is not a matching field for this new value
in the Primary Key table. How do I solve this dilemma? Thanks for your help!
 
Is there any chance we could encourage you to use 2 combos: one for
navigating to an existing order, and the other to enter a new value? That
would solve all the issue, and make for a more consistent and meaningful
interface.

You could even use the NotInList of the unbound nav combo to ask the user
whether they want to go to a new record, and assign the value to the entry
control.
 
I would like to use one combo if possible in order to make the form as
efficient and user friendly as possible. If this isn't possible, then I can
work with 2 combos. Can you explain how to use the NotInList function in the
combo box in more detail. Will this function automatically populate the
Primary Key table with the new Order ID value if that value doesn't
previously exist? If that's the case I think I can get away w/ only 1 combo.
Thanks.
 
If you are displaying the primary key value in the combo, and you are happy
to write the record with no other value there (e.g. no client, no date for
the order), then yes: you may be able to get away with using the NotInList
event.

Details in:
NotInList: Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html
 
I have tried to incorporate the NotInList function & code you suggested and I
receive the following error.
"Error 3265: Item Not Found In This Collection."
Any suggestions? Note that the record source property uses a SQL statement
to display the Order ID and a Sub ID field when the combo box is opened.

I'm not sure I understand what you mean by "and you are happy to write the
record with no other value there (e.g. no client, no date for the order)".
No other value where? Are you saying that from the same form's other
controls I can't update other fields in the table on the one side of the
relationship ?
 
The value that the user types in the combo becomes the NewData argument of
the NotInList event. When you set the Response to acDataErrAdded, Access
checks to find that value in the bound column of the combo. If the value
went into another field (because the bound column is zero-width, and so is
different from what the user types), the approach will not work.

This all has to happen inside the NotInList event. In an event-driven
environment, you cannnot assume the order in which the user will visit the
controls. You therefore cannot know that the other required fields such as
ClientID and OrderDate have been entered, and therefore they may not be
available for you to use in the append query statement or AddNew operation
that needs to execute before the NotInList is completed.
 
Back
Top