Create New Record and Update another table based on form results

[CascadeDog]

I have created a form based on a query that pulls records from a table [AccountingTbl] where the deposit date is null.

I now need to create a record in another table [DepositTbl] with the ID, Date and Amount.

Then I need to update the [AccountingTbl] [DepositDate] field and [DepositID] field to match the new record.



I'm stumped at how to make all these cogs work together. Please help.

[Micron]

Then I need to update the [AccountingTbl] [DepositDate] field and [DepositID] field to match the new record.

No,you don't. It violates normalization rules to store the same data in 2 different places if it relates to the same thing. If Account records is the one side of a relationship and Deposits are the many, the PK value of the account record should get stored in the deposits table as a FK. That same fk value would be used for every deposit made by that account. Since the deposit date relates to the deposit info, it goes there - not in the account table. That table would be account info, which is relatively static.

[June7]

Why would you need date in both tables? If you have the DepositID then DepositDate can be retrieved in query.

Perhaps you should have a form bound to DepositTbl with combobox to select from AccountingTbl. Exactly what is nature of records in AccountingTbl?

If you want to provide db for analysis, follow instructions at bottom of my table.

[CascadeDog]

On my ReceivablesFrm, I'd like a button that creates a record in the depositstbl with the date and amount total and then updates the accountingtbl with the depositid just created. I appreciate your help. (as an aside, I'm helping to expand what this db does and at the same time attempting to clean up the data. if you have suggestions on that I'm open and appreciative.) DBKM12-9.zip

[June7]

I can't open your db because I only have Access 2010. There must be features used that are not backward compatible.

[CascadeDog]

Hi. I'm sorry it took so long to get back with you. I believe I've gotten it to where you can open it.

I created a form named DepositFrm. The button runs the query DepositQry so the user can review the deposit prior to creating a deposit in the DepositTbl.

So here's where I'm having issues. Once reviewed, the user will need a button to create a record in the DepositTbl and update the records in the AccountingTbl with the DepositTbl ID number.

To minimize errors, I prefer that these processes are automated.

I really appreciate your help.

[June7]

Advise not to use spaces nor punctuation/special characters in naming convention. [Membership #] would be better as MemberNum.

Why are MembersTbl and MasterListTbl not one table?

Deposit amount should be calculated as a Sum of related payment transactions, not saved into table.

DepositSlipQry has a fixed date criteria. This should be supplied dynamically by reference to a form control.

Users should not review data via tables and queries, only interact with forms and reports.

It would be easiest if payments are attached to a deposit ID when payments are entered. This would be done with a form/subform arrangement. Create deposit record on main form and payment records in subform. Master/Child Links properties will automatically save DepositID into payment record. As is, user needs to create Deposit record then code runs an UPDATE action to save the generated DepositID into records using same filter criteria as used in DepositSlipQry.

[CascadeDog]

Thank you so much for helping. I took this over from someone else and have been trying to normalize while still creating additional functions for them. If I make changes to the field names will it cascade down through the queries/forms, etc.?

The Members/Master tables was my first question as well. The master is head of household and the members table collects info on other members in the home. Is there a better way to set this up?

[June7]

Changes to field names will cascade into queries except where field names are used in expressions. Will not cascade into textbox ControlSource or macros or VBA.