Updating multiple tables from a main form and subforms (1 Viewer)

LanHua

Registered User.
Local time
Today, 04:56
Joined
Feb 19, 2016
Messages
15
Good afternoon everyone,
I am hoping someone can help as I can't seem to find an answer - although I may not be seeing the forest for the trees and if that is the case I apologise.

Anyway I have a database that at the moment contains tables - tblPeople (ID,name, email etc, also addressID and companyID), tblAddress (street, city, postcode), tblCompany (name, main switchboard number, general email) and also a quotes and document table however these are not applicable to this problem.

I have created a query that is shown via a form (contact information) so that all the information is viewed for each contact. If a quote is required a contact has to be in the database so it can be logged against it (you have to be speaking to someone who has requested the quote - right!) and so the contact information form is searched and if the contact is not there then they need to be added.

I have a new contact form, main form is bound to tblPeople with 2 sub forms bound to tblAddress and tblCompany. The master/child link fields are PersonID. There is no problem with being able to enter the details but when the entry is saved the information is added to each table but the AddressID and CompanyID are not showing on tblPerson and therefore not showing in the Contact information form.

I have placed a text box in the main form (linked to tblPeople) for AddressID and CompanyID (secondary keys) and if I set the control source to pick up the ID from the subforms it shows it no problem, but still wont save to tblPeople. If I add another box for AddressID and CompanyID and set the control source to AddressID/CompanyID and manually type in the numbers displayed in the other boxes it saves to tblPerson no problem and shows in the Contact Information form.

Is there anyway round having to type in the ID numbers, as if the end users don't do it then it wont show in the Contact Information form and they will either try to add it again (duplicating the information) or wont be able to create and assign a quote.

I have tried adding a default value and and event to save the expression as a number but I cant get anything to work.

Sorry about the long winded post, hope it made sense.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:56
Joined
Jul 9, 2003
Messages
16,244
I'm guessing you are not using the subform subreport control. This will link your information together. In other words you set up the linking information in the aforementioned control.

Sent from my SM-G925F using Tapatalk
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:56
Joined
Jul 9, 2003
Messages
16,244
See this video for more information:-

Excel to Access Part 5
https://youtu.be/fPq5H8nyNtc

Sent from my SM-G925F using Tapatalk
 

LanHua

Registered User.
Local time
Today, 04:56
Joined
Feb 19, 2016
Messages
15
Thank you, thank you, thank you.

I was using the sub form/sub report control however upon taking a closer look (after watching the video its what I needed the form to do, so it should have worked), I realised that the queries were joined on foreign keys (AddressID and CompanyID) in the tblPeople (that were not there!) so I changed them to link on PersonID (Primary in tblPeople and foreign in tblAddress and tblCompany) and everything seems to be working now!

Thank you again, now just 2 more problems to solve.......
 

LanHua

Registered User.
Local time
Today, 04:56
Joined
Feb 19, 2016
Messages
15
Ok, I think I jumped the gun here saying it was solved....

I can only get this to work if there is only one PersonID for each Address and Company so the PersonID is a foreign key in tblAddress and tblCompany. But as my tblAddress and tblCompany can have more than one person assigned to them they need to be foreign keys in the tblPerson, which is what keying in the IDs does (as per my first post) but is there a way to eliminate having to type in the IDs when they are able to be displayed from an expression?
As the expression doesn't seem to save in the tblPerson is there a way to save the expression as a plain number into tblPerson?

Thanks
 

Users who are viewing this thread

Top Bottom