Mainform - Subform record query

[techkenny1]

Hi all,
Please excuse my ingorance on how to explain this issue.
The issue is how to get a text box on the subform to update when the mainform is updated.

On the mainform there is a textbox [blue]ACS[/blue]
The same textbox is on the subform.

[blue]The structure of the tbls has this textbox in both tables;tblaccounts, & tblAccountsdata.
The Linkchild Fields is: InvoiceNo;ACS
The Link Master Fields is:InvoiceNo;ACS [/blue]

Where the link from the mainform to the subform is via the InvoiceNo and the ACS fields.

When the invoice form is opened the [blue]InvoiceNo[/blue] appears on both the mainform and subform. So does the ACS field which records as [blue]"In Process"[/blue]

After data entry into the subform when the mainform is refreshed the data on the subform dissapears..

The ACS field changes on both the mainform and the subform to [blue]"Invoiced",[/blue] which is supposed to happen.

When I look into the tables, the [blue]mainform table[/blue] shows the [blue]invoiceNo[/blue] has incremented and the ACS status to [blue]"invoiced"[/blue], which is correct.

However when I look into the [blue]subform table[/blue] the [blue]invoiceNo[/blue] has incremented with the mainform table but the [blue]ACS field[/blue] in the subform remains as [blue]"in process"[/blue]

It seems that while the subform shows a change to the [blue]ACS[/blue] field when the mainform is refreshed it does not update the [blue]subform table.[/blue]

So the question is this, How can I get the subform table to update the ACS field to correspond with the main table?

This would account for the data on the subform dissapearing when the main form is refreshed.

I have not given any form code as I was not sure if it was warranted.

Thank you

kp

 

[kjv1611]

Are you sure you need such duplication of data? It sounds like you've got a lot of duplication between your tables. In other words, it sounds like your database is not normalized.

So, we're looking at invoicing...

You could have a Customers Table, Products Table, Services Table, and an Invoice table, for instance.

If in your Invoice table, you have a field which specifies whether the invoice is In Process or is Invoiced, then why do you need that somewhere else?

I think if you fix your table organization, that'll eliminate any such issues as this altogether.

 

[techkenny1]

Hi kjv1611
Many thanks for your reply.
I have read your reply. I have looked at the tables. There is no duplication of data.This is a database inherited when we bought this business. I am just trying to improve the invoicing side of it, which has a few flaws.

kp