Single Textbox in a form updating multiple table entries

[JReneau35]

I am a novice to MS Access. I have about 10 to 15 tables that link to a single form. I use unique ID's to link all of these tables together. Since the ID for the customer never changes, I was hoping that someone could give me a hand and let me know if it is possible to add/update multiple table fields with a single textbox in a form.

Thanks,
JReneau35

[MMcCarthy]

I was hoping that someone could give me a hand and let me know if it is possible to add/update multiple table fields with a single textbox in a form.

I need more information on exactly what you are trying to do.

[JReneau35]

I need more information on exactly what you are trying to do.

I have a access form that has textboxes and other data controls that link to about 10 to 15 different tables. the tables all connect by using the same INVOICE# key. Since I need the invoice# key to be in all of the tables I was hoping that there was a way to have a single textbox control in the form take the invoice# and plug it into more than one table.

[MMcCarthy]

I have a access form that has textboxes and other data controls that link to about 10 to 15 different tables. the tables all connect by using the same INVOICE# key. Since I need the invoice# key to be in all of the tables I was hoping that there was a way to have a single textbox control in the form take the invoice# and plug it into more than one table.

It sounds like you have multiple tables of Invoices all using the Invoice# as the primary key.

This doesn't make sense. I understand there are 10 to 15 tables, which seems like an awful lot. Can you post the table structure for 3 or 4 of them so I can see what you're doing.

[JReneau35]

Unfortunately I cannot post the tables because they contain important client information which I cannot divulge.

Basically the primary key is linking all of these tables up in queries. I am working with a form to plug in client information into different tables. The primary key is the same for all of the tables and a single textbox in my form is where you enter the primary key. Just hoping that instead of having to enter the primary key in the form in different textboxes about 10-15 times (to enter them into each table) I was hoping that one textbox could link to 10-15 tables. I don't know if I can make it much more clear I can be.

Sorry that I cannot give you an example of the project that I am working on, but I am not allowed.

Thank You,
Justin Reneau

[NeoPa]

Justin,

In a case like this (and it's not a bad idea generally too), you should set up a situation - as small as possible - which reflects your problem using only test data.
This can then be posted without any problems, and has the further benefit that the problem is more concisely contained as you will only create the smallest amount necessary.
A lot of the difficulty involved in posting answers is going through and sorting the relevant info from the dross anyway.

[MMcCarthy]

Justin,

Your table structure is incorrect and going to cause you numerous problems. If each of these tables has a primary key whose value is the same in each client related record of each table then this is not a unique identifier. It is the the same as having one table of records with 10 - 15 copies of a primary key.

I assume there are different Invoice Numbers to all these records and I would recomment using that as the primary key of a new table to which all records would be appended. These records would then be related to the client in a standard database design using the ClientID (PK) as a foreign key. See sample structure below.

tblInvoice
InvoiceNo (PK of this table)
InvoiceDetails
Amount
VAT
TotalAmount
ClientID (PK of tblClients and Foreign Key in this table)

If you could explain the reasons for the 10 to 15 tables we might be able to help you come up with a different solution.

BTW when I asked for table structure I was only looking for something like the above. I didn't need to see the actual data.

Mary

Unfortunately I cannot post the tables because they contain important client information which I cannot divulge.

Basically the primary key is linking all of these tables up in queries. I am working with a form to plug in client information into different tables. The primary key is the same for all of the tables and a single textbox in my form is where you enter the primary key. Just hoping that instead of having to enter the primary key in the form in different textboxes about 10-15 times (to enter them into each table) I was hoping that one textbox could link to 10-15 tables. I don't know if I can make it much more clear I can be.

Sorry that I cannot give you an example of the project that I am working on, but I am not allowed.

Thank You,
Justin Reneau

[JReneau35]

Here is my table structure for each table:

tblDometicAddress
InvoiceNumber (pk)
MailCode
AddressLine1
AddressLine2
City
State
Zip
Country

tblDomesticContract
InvoiceNumber (pk)
OriginalContractDate
OriginalContractAmt
ContractAmt
ContractDate
AmendmentDate
Terms
Conditions
Increases

tblDomesticCustomer
InvoiceNumber (pk)
MasterClientNumber (fk)
CustomerName
Fka

tblDomesticInvoice
InvoiceNumber (pk)
Product
Tax
Notes
InvoiceDesc

Here is a couple of tables. Again just seeing if I can have all this information in one form and one textbox to enter the InvoiceNumber in all tables, and different controls for each of the other fields.

Thanks

Justin,

Your table structure is incorrect and going to cause you numerous problems. If each of these tables has a primary key whose value is the same in each client related record of each table then this is not a unique identifier. It is the the same as having one table of records with 10 - 15 copies of a primary key.

I assume there are different Invoice Numbers to all these records and I would recomment using that as the primary key of a new table to which all records would be appended. These records would then be related to the client in a standard database design using the ClientID (PK) as a foreign key. See sample structure below.

tblInvoice
InvoiceNo (PK of this table)
InvoiceDetails
Amount
VAT
TotalAmount
ClientID (PK of tblClients and Foreign Key in this table)

If you could explain the reasons for the 10 to 15 tables we might be able to help you come up with a different solution.

BTW when I asked for table structure I was only looking for something like the above. I didn't need to see the actual data.

Mary

[MMcCarthy]

tblDometicAddress
DomAddressID (new PK) use autonumber - needs to be created
InvoiceNumber (needs to be removed as address is tied to customer not to invoices. You can use new PK of tblDomesticCustomer as a foreign key instead)
MailCode
AddressLine1
AddressLine2
City
State
Zip
Country

tblDomesticContract
DomContractID (new PK)use autonumber - needs to be created
InvoiceNumber (needs to be removed as contract has many invoices.
You can use new PK of tblDomesticCustomer as a foreign key instead)
OriginalContractDate
OriginalContractAmt
ContractAmt
ContractDate
AmendmentDate
Terms
Conditions
Increases

tblDomesticCustomer
DomContractID (new PK)use autonumber - needs to be created
InvoiceNumber (needs to be removed as contract has many invoices. You need to use the PK from here as the foreign key in all other tables.)
MasterClientNumber (fk)
CustomerName
Fka

tblDomesticInvoice
InvoiceNumber (pk)
DomContractID (FK to tblDomesticCustomer as a customer can have many invoices)
Product
Tax
Notes
InvoiceDesc

Your table structure using InvoiceNo as the PK for all tables would never have worked as tables can only have a 1 to 1 or 1 to many relationship. Almost all of yours have a many to many relationship.

If you follow the restructuring I've outlined above the relationships will allow you to create a main form based on tblDomesticInvoice and any other table it has a 1 to 1 relationship with by joining them together in a query using PK to FK. In cases where there is a 1 to many relationship you can create subforms for the tables containing many records per customer like Invoices and joining the subform to the main form using the DomCustomerID.

You cannot continue to operate under the current design as you cannot create relationships between your tables.

Try restructuring using the above model and come back with any questions.