Link to home
Start Free TrialLog in
Avatar of mmiya
mmiya

asked on

How can I update foreign key in child table

I have tables called "tblCustomers" and "tblOrders". tblCustomers has a autoNumbered primary key tblCustomers.CustID which linked to foreign key tblOrders.CustID. These CustIDs are hidden from user (not on forms).

When I add new record to tblOrders, how can I update the foreign key with primary key value. It seems form doesnt provide any function to syncronise both key. There is no obvious code to update foreigh key in class module when I look into sample database.

I just dont know how to do that. Please advise.


Avatar of emeel
emeel

You could define constraints in your SQL Server database on the used tables. This constraints will not allow you to use a foreign key if there is no corresponding primary key. Also, you wont be able to delete a primary key if there is still a corresponding foreign key.
Adding or modifying records that violate on these constraints, will result in an error in your interface. You could test on the error codes/description and give an appropiate warning to the end-user.
good luck
Avatar of mmiya

ASKER

Thanks for the advice emeel.

What I'm asing is that is there any way to update foreign key implictly. MS Access takes the data mostly from forms. And it will be added to the corresponding table implicitly (without writing code) when you moved the pointer or close the form. But doing it implicitly, is seems that all data must be on the form.

What I'm looking for is that when I added new record to the "tblOrder", how can I copy primary key value to the foreign key of the added record IMPLICITLY.

Thanks
Hi,

There are two ways to do this :

1. Create a main form for your customers and put a subform (by using the subform wizard) into this main form. The two forms will be linked with a master anc child linkfields, and the child linkfield is filled in automatically.

2. If you only want to have a order form, you only have to put a combobox showing all customers on that form and whilest your making the order, you'll only have to select the right customer. (therefore you can use also a wizard, but don't forget to state in the last step of the wizard "store this value in this field : select your foreign key)

If you need help, just give a buzz...

Regards
ASKER CERTIFIED SOLUTION
Avatar of ildc
ildc
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mmiya

ASKER

Thanks ildc for your comment. I was away from my PC for a while. My apology for delay in respond.

I think I can work out with subform's link chiled/parent fields.

Regards
Glad to be of assistance.

(Hope y had a nice ?Holiday?)

Regards