Add new foreign ID to record

[ClaraBarton]

In order to transfer amounts from one account to another I have a subform that's
connected Master = TransactionID ; Child = TTransID. So a new record is created
with a new fAccountID (combobox). What I can't figure out is how to get the new record ID (orange)
into the original TTransID so that I know both are Transfer Amounts connected to each other.
I could use a recordset (beforeupdate) and do it in VBA but it seems like I'm overthinking a simple thing.
I tried putting the TTransID on the Master form but couldn't figure how to make it work.

 

[LarryE]

In order to transfer amounts from one account to another I have a subform that's
connected Master = TransactionID ; Child = TTransID. So a new record is created
with a new fAccountID (combobox). What I can't figure out is how to get the new record ID (orange)
into the original TTransID so that I know both are Transfer Amounts connected to each other.
I could use a recordset (beforeupdate) and do it in VBA but it seems like I'm overthinking a simple thing.
I tried putting the TTransID on the Master form but couldn't figure how to make it work.
View attachment 117059

Set the subforms foreign key (TTransID) Default property to =Forms![MAIN FORM NAME]![SUB FORM NAME]![TransactionID]
so when you enter a new record on the subform, it will use the main forms TransactionID as its new TTransID.

But the subform should have a textbox referencing the mainform TransactionID on it anyway, so if you have the Master/Child links set to TransactionID, then ACCESS will put the new foreign key in the subform automatically. But you need to make sure you have referential integrity enforced for the relationship.

 

[ClaraBarton]

Maybe I'm not getting you... The subform's Child is TTransID so it automatically gets the mainform ID. The problem I have is the main form does not pick up the subform Child key. I've tried setting it in the query: TTransID: Nz([Me]![subform]![form]![TTransID]) but the form doesn't want to open without error.

 

[ClaraBarton]

Is the form's Control Source query the wrong place to put this?

 

[ClaraBarton]

If I have Master TransactionID connected to Child TransactionID it doesn't create a new transaction. That's why I have Child = TTransID

 

[ClaraBarton]

I apologize... I may be overthinking this. I've spent many hours just getting to this point.

 

[mike60smart]

I apologize... I may be overthinking this. I've spent many hours just getting to this point.

Are you able to upload a stripped down version of the Db to include the Forms where you have the problem?

 

[Pat Hartman]

Set the subforms foreign key (TTransID) Default property to =Forms![MAIN FORM NAME]![SUB FORM NAME]![TransactionID]
so when you enter a new record on the subform, it will use the main forms TransactionID as its new TTransID.

This is already controlled by Access when you set the Master/Child links correctly.

Your picture looks like a spreadsheet so it is hard to figure out how to use a form to handle this. Probably you need to navigate to the record you want to move. Then you need a button on the subform that prompts for the ID of the target parent. Once the target ID is acquired, your code just modifies the FK field and saves the record. You can then requery the subform to see that the modified record is gone. Or you can add code to navigate to the target Parent record and you will see that the new record has been moved.

 

[Mark_]

It looks like you are trying to do a journal entry for double entry book keeping, but doing in the hard way.
I'd suggest a different record definition:
ID - Unique ID for your record
Date/Time - Date and Time entry is created
Amount - Amount of the transaction
Credit Acct - Which account this is credited to
Debit Acct - Which account this is debited from
Memo - Not regarding why this entry was made.

This means instead of having two entries and trying to link them, you have one entry that tells you what to credit and debit for each transaction.
You would then have your example data as

1, 13-Nov, 551.00, Checking, Undeposited, To Example 1
2, 14-Nov, 226.00, Charge Card, Checking, To Example 2

Does this make more sense?

 

[ClaraBarton]

Click on Transactions; Then on the Split button. Thank you for any criticisms.

 

[AngelSpeaks]

It looks like you are trying to do a journal entry for double entry book keeping, but doing in the hard way.
I'd suggest a different record definition:
ID - Unique ID for your record
Date/Time - Date and Time entry is created
Amount - Amount of the transaction
Credit Acct - Which account this is credited to
Debit Acct - Which account this is debited from
Memo - Not regarding why this entry was made.

This means instead of having two entries and trying to link them, you have one entry that tells you what to credit and debit for each transaction.
You would then have your example data as

1, 13-Nov, 551.00, Checking, Undeposited, To Example 1
2, 14-Nov, 226.00, Charge Card, Checking, To Example 2

Does this make more sense?

This isn't always feasible. Sometimes you will have multiple entries. For example:

Date. Check No. Amt
11/10/24 292. 500 credit Checking Acct
11/10/24 292. 200 debit Rent
11/10/24 292. 300 debit Security Deposit

 

[Gasman]

That is what a split transaction does in Quicken, but only for categories.
For a transfer, one account has the debit transaction and the other the credit.
In quicken, a category can also be an account. That is how they handle it.

 

[Mark_]

This isn't always feasible. Sometimes you will have multiple entries. For example:

Date. Check No. Amt
11/10/24 292. 500 credit Checking Acct
11/10/24 292. 200 debit Rent
11/10/24 292. 300 debit Security Deposit

For the above, how would you reflect it in your existing structure? Would you try to store a third (or fourth?) ID for reference?
If yes, take a look at how to do a self referencing table. Short of it is you have a 2nd table that has only "Parent ID", "Child ID", and only the details that show why you are doing the link. This would let you have one transaction in for the "Credit" with multiple references for how it gets broken out.

I've always tried to do journal entries though. They follow GAAP, so accountants don't get upset with what looks like unbalanced transactions.

 

[AngelSpeaks]

For the above, how would you reflect it in your existing structure? Would you try to store a third (or fourth?) ID for reference?
If yes, take a look at how to do a self referencing table. Short of it is you have a 2nd table that has only "Parent ID", "Child ID", and only the details that show why you are doing the link. This would let you have one transaction in for the "Credit" with multiple references for how it gets broken out.

I've always tried to do journal entries though. They follow GAAP, so accountants don't get upset with what looks like unbalanced transactions.

I've just imported transactions from QuickBooks to do this. When there are more than one offsetting entry, the Account says "-Split-" and QB expands the form so you can enter them and then creates the transactions. They must use a foreign key to tie them in for reporting purposes. I haven't figured that out yet.