Primary Key and Subform

[cleaver]

Any advice given will be much appreciated....I have a form
and a subform that I want to use for data entry. On the
form, there is a Customer ID Combo box and a Customer Name
field. When I select the Customer Id it automatically
fills in the Customer Name and retrieves all related
records into the subform. Customer Id is the primary key
for the Customer Name table. So when I go to add a record
to a customer, I keep getting an error message saying that
the primary key will be duplicated. I don't want the
Customer Id to be changed but mainly to retrieve the
related records.

How can I make my form so I'm able to add records without
duplicating a record in my Primary key table????

Thanks for any advice that can be given!!

 

[Sandra Daigle]

It sounds as though you are confusing a bound control with an unbound
control that can be used for record navigation. If the recordsource of the
main form is the Customer table and you want to add a control that allows
you to select the Customer whose record you want to view/edit, then you
should have an unbound combo. Unbound simply means that the ControlSource
property of the control is empty and that what you put into that control is
not saved anywhere after the form closes.

In your case, the combo you describe is probably bound to the Custid field.
I would suggest that you move it to the header of the form and then clear
the ControlSource property of the control. Also rename the combo to
something other than the field name. You will need to add some code to the
AfterUpdate event of the combo so that record navigation will occur to
display the record which has the Custid selected in the now unbound combo.

Here's some sample code:

In the AfterUpdate event of the combo/listbox put the following code:

with me.recordsetclone
.findfirst "Custidid=" & me.Custid
if not .nomatch then
me.bookmark=.bookmark
endif
end with


Finally, add a textbox control to the form and bind it to the Custid field.

 

[GVaught]

Your customer ID can't be used as a lookup and data entry at the same time;
it must reflect the record that is being entered. The Customer Name is the
equivalent of picking the CustomerId.
In your present setup only the subform part would be editable.

The main form should contain all the fields from the Customer table; the
subform contains the related records from another table; not the same table.