Refresh Continuous Sub-Form for Change in Main Form 1

[corycrum]

Hi all,

I'm building a simple accounting system in Access. This database contains two tables:

1) T_Ledger
2) T_TransactionDetails.

T_Ledger contains the following fields: TransactionId, AccountId, and Amount.

T_TransactionDetails contsins the following fields: TransactionId, VendorName, InvoiceNumber.

T_Ledger and T_TransactionDetails are linked based on the TransactionId field. This is a one-to-many relationship. A transaction will have only one vendor and one invoice in T_TransactionDetails, but may be assigned to several different accounts under T_Ledger. For example, if I go to Office Depot and spend $100 on pens & paper and $900 on a laser printer, I need to be able to stick $100 in the account "Office Supplies" and $900 in the account "Office Equipment - Printers." The sum of the Amount field in T_Ledger for this single TransactionId will total $1,000.

I have a data-entry form set up (F_TransactionDetails) with a continuous sub-form (F_Ledger).

The TransactionId control on F_TransactionDetails is locked and has the default value =Format(Now(),"yyyymmddhhnnss"). In this way, the TransactionId doubles as a time-stamp for when the record is entered into the database. The TransactionId control on the continuous sub-form (F_Ledger) is locked and has a default value set equal to =[Forms]![F_TransactionDetails]![TransactionId]. So, as multiple accounts and amounts are entered into the continuous sub-form, each record opens up with the same TransactionId as on the main form, F_TransactionDetails.

This setup seems to work beautifully, except for one problem...

If the user starts entering information (like VendorName) on the main form and then presses the escape key, the TransactionId control on the main form updates to the most recent date/time. The TransactionId control on the sub-form remains at the original date and time set when the form was opened. The exact opposite happens when the user is typing into the sub-form and presses escape. The sub-form TransactionId value updates but the main form does not.

How can I get all records that have been populated on the sub-form to update to the most recent TransactionId (date/time) when the user presses escape after entereing data on the main form? Or, similarly, how can I prevent the TransactionId control from updating when the escape key is pressed.

If their is an easier solution for creating a one-to-many value for a TransactionId, I'm willing to break-away from using the current date/time as the value. I just want something automated so the user doesn't have to invent a new TransactionId for each entry. AutoNumber would be ideal, but how can that be set up on a one-to-many relationship data-entry, continuous sub-form?

If anyone can point me in the right direction I would really appreciate it.

 

[ProgramError]

Regard the form and subform as two items. When you press escape you are undoing whatever you have entered into the form and the controls revert to there default in this case I suspect TransactionId is the current date and time. So what you have to do is refresh both forms in order for them to both change. Or am I totally barking up the wrong tree?

Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[corycrum]

You are correct. The trick is that I need them to update simultaneously so that they are both reflecting the exact same date and time down to the second.

I need the controls to be linked similar to cells in an Excel spreadsheet so that when the main form changes, the subform updates immediately to reflect the exact same value.

 

[PHV]

Aren't the mainform and subform linked together ?
Have a look at the LinkMasterFields and LinkChildFields properties.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[corycrum]

That worked! Thanks!