Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How can I update foreign key in child table

Posted on 2002-07-18
6
Medium Priority
?
224 Views
Last Modified: 2012-05-04
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.


0
Comment
Question by:mmiya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Expert Comment

by:emeel
ID: 7164255
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
0
 

Author Comment

by:mmiya
ID: 7167393
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
0
 
LVL 7

Expert Comment

by:ildc
ID: 7167472
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
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 7

Accepted Solution

by:
ildc earned 150 total points
ID: 7167474
BTW,

If you do not like emeel's answer, just reject it.


Emeel, it is policy on this forum that all questions are answered with a comment, and not with an answer because this blocks the question and puts it in a section that is often visited by other experts.

Regards
0
 

Author Comment

by:mmiya
ID: 7235618
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
0
 
LVL 7

Expert Comment

by:ildc
ID: 7237890
Glad to be of assistance.

(Hope y had a nice ?Holiday?)

Regards
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question