By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,079 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Cascade Updating in forms and subforms

P: 6
I created two table in access 2003.one is one side and other is many side.i designed a form based on primary table and inserted it one subform based on many side table.my problem is i enterd one new record in primary through main form and related data in subform.later i found the value of primary key wasnt true and now when i change the primary value related data in subform(many side table)is change but the value in primary dosnt change and error massage appears telling me search key in not found.as well as i coudnt establish cascade update in relationship menu and telling me you have data in many side but you ndont have correspond data in primary.icheck the table but i have all data is needed in primary
i would like help me.thanks a lot
Dec 26 '13 #1
Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,397
Bita2901:
Can you clarify this:
i found the value of primary key wasnt true
Do you mean that the the value you were using was not correctly recorded between the parent and child tables
OR
Do you mean that the the value you were using for a primary key was not the correct value to begin with?
Dec 26 '13 #2

NeoPa
Expert Mod 15k+
P: 31,494
What do you have as a linking key?

How are the [Link Master Fields] & [Link Child Fields] set on your Sub-Form control?

PS. Please include in your next post the versions of Windows and Access that you're using.
Dec 26 '13 #3

P: 6
@zmbd
the problem is:I have one table with a primary Key Contract_No,and a link table with foreign Keys,Contract_No,Event_Code,I Created a form bringing Primary table data and a subform in it For link Table.
I Activated referentional Integrity&Cascade delete,Update.I can add records to both table but when i want to delete records All records could be deleted except first record.when I want to delete first record the program displays a message saying "search key not found" as well as i cannot change the value of primary key from primarytable and have changed datain linked table.
Thanks for your help.Sicerely yours Majid
Dec 31 '13 #4

P: 6
@NeoPa
Hi
I have tow table one main and one linked with same fieldname called Contract_No.in the sub form in property box of it is set child and parent links with the Field Contract_No
thanks for Your help.Sincerely yours Majid
Dec 31 '13 #5

P: 6
@zmbd
Hi
I Created Tow Table one primary and one foreign with a same field link.the primary key is a field Contract_No
i inserted data in tow table.later i found that a Contract number for one record wasnt correct.noew when i want to change Contract number from primary table through form or direct in table it is not possible.cascade update and delete is active.when i want to delete all records in primary table all records could be deleted except the first recors saying this message : "The search key Not Found"
thanks for your help
sicerely yours Majid
Dec 31 '13 #6

zmbd
Expert Mod 5K+
P: 5,397
Sounds like you really goofed the records; however, you're also going around in circles...

From what I get:

- This is a prime example why I never, ever, use any value with meaning for a primary key - Instead I usually use either an autonumber or a full date/time serial number.

- The cascase-update/delete only works from the Parent to the Child. If you are attempting to change the child's record in an attempt to change the parent this will not work and if by some unusual means you managed to get child record to change to something that was not in parent table (for example, deleting the relationship and then re-establishing - and I'd be surprised if it let you re-establish) then you've thrown all of your records off.

- Which record will not delete? The first record in the parent table or the child? If it's the parent, then there is a record in the child that has the refernce to the parent record; however, something was broken in the linkage. Back-up the database file and do a compact and repair.

>>Consider>>
Changing your database so that you are not using the contract id as the primary key. You can index-noduplicate the field that has the contract id to prevent duplicates at the table level.
Dec 31 '13 #7

NeoPa
Expert Mod 15k+
P: 31,494
I would expect a field that is one of those included in the [Link Child Fields] property to be populated automatically by the sub-form.

Alternatively, if ever I'm working on a field that is not so linked, but which is common to both tables and is filtered anywhere on the form, I set the .DefaultValue property of the relevant control on the sub-form to match the item filtered. This approach may be worth a try.

Setting a .DefaultValue property correctly can be difficult, especially for string fields, but if done correctly always produces the desired results.
Dec 31 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.