Add new foreign ID to record (4 Viewers)

[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.