How copy date from main form to subform?

[Melo3]

Greetings!

I have a table with the following fields:

TransactionID
TransactionDate
TransactionType
TransactionDetails
TransactionAccount
TransactionDeposit
TransactionWithdrawal

I created a main form called "Transfer Voucher" (The fields are: TransactionDate; TransactionType; TransactionDetails; TransactionAccount and TransactionWithdrawal) and included a sub-form called "Replenish" (The fields are: TransactionDate; TransactionType; TransactionDetails; TransactionAccount and TransactionDeposit). My primary objective create two transactions in one form - one for the withdrawal of amount from one account and another to deposit the same amount to another account. I was able to attain that.

To simplify the entry of records I want copy the TransactionDate and TransactionDetails in the main form to the TransactionDate and TransactionDetails in the sub-form. To do that I made an after update command in TransactionDate of the main form, as follows:

= [TransactionDate] = Forms![Replenish]![TransactionDate]

I did the same thing with TransactionDetails. When I tested the form, it indeed created two transactions (with different IDs) except that the Date and Details on the records from the sub-form were blank.

What can I do to automatically populate those common fields?

Thank you in advance.

Melo

[slenish]

Hello Melo,

First let me throw out this question for you...is the subform already open or does it open on a button click?

What you want to do is put the function you are trying to use in the subform either in the FormOpen function or FormGotFocus function. Depending on the answer to my first questions.

Do something like this

Private Sub Form_Open(Cancel As Integer)
 
Me.TextBox1 = [Forms]![MainForm]![TransactionDate]
Me.TextBox2 = [Forms]![MainForm]![TransactionDetails]
 
End Sub
 
 

[Melo Mendoza]

Hi Slenish,

Thank you very much for your help.

I am a bit confused, with your question as to whether my sub-form is open.

What I did was to create the subform first with the said fields and saved it as "Replenish". Then I created the main form and in the toolbox used the insert subform function. Then I saved the main form as "Transfer Voucher". When I open the Transfer Voucher form, I can already see that the subform is also open (meaning I can manually input the records in the fields). Does it mean that the subform is already open? I asked this question because I used a similar code but I got an error report that it cannot find "Replenish" and asked if it was open. Kindly enlighten me on this.

I am new at Access and I am not familiar with FormOpen and FormGotFocus functions. Where can I find them?

Kind regards

Melo

[Melo3]

Hi Slenish,

Thank you very much for your help.

I am a bit confused, with your question as to whether my sub-form is open.

What I did was to create the subform first with the said fields and saved it as "Replenish". Then I created the main form and in the toolbox used the insert subform function. Then I saved the main form as "Transfer Voucher". When I open the Transfer Voucher form, I can already see that the subform is also open (meaning I can manually input the records in the fields). Does it mean that the subform is already open? I asked this question because I used a similar code but I got an error report that it cannot find "Replenish" and asked if it was open. Kindly enlighten me on this.

I am new at Access and I am not familiar with FormOpen and FormGotFocus functions. Where can I find them?

Kind regards

Melo

[Melo3]

Hi Slenish,

Thank you for your patience.

I tried what you suggested and I entered the following code:

Private Sub TransactionAccount_AfterUpdate()

Me!TransactionDate = Me![Replenish].Form!TransactionDate.Value
Me!TransactionDetails = Me![Replenish].Form!TransactionDetails.Value

End Sub

Despite the code, the date and details were not copied. I don't know what was wrong?

Thanks

Melo

[Harry38]

@slenish
Hi Slenish,
I used yr code in my mainform/subform: if the name/address in the Mainform doesn't yet exist in the tbl in my subform; I push a button and the fields of the mainform are copied to the subform. It works excellent, except when the field in the MainForm is Null. How to ignore the fields that are Null.
Could you help me here.
Thanks - Harry

The code under the button:

Me.Voornaam = [Forms]![InvoerDownloads2]![Voornaam]
Me.Adres = [Forms]![InvoerDownloads2]![Straat]
Me.Aanhef = [Forms]![InvoerDownloads2]![Aanh]
Me.Voorletters = [Forms]![InvoerDownloads2]![Voorletters]
Me.Voorv = [Forms]![InvoerDownloads2]![Tussenvoegsels]
Me.Adres = [Forms]![InvoerDownloads2]![Straat]
Me.Postcode = [Forms]![InvoerDownloads2]![Postcode]
Me.Woonplaats = [Forms]![InvoerDownloads2]![Woonplaats]

[slenish]

Hi Harry,
Feels like I'm coming out of retirement to answer this one, haha. If I'm understanding the question correctly it sounds like you will want to use an IF statement or a Case statement to help catch a Null value. You could try something like...

If forms!mainform!text1 not is null then
     Me.text1= forms!mainform!text1
End if
 

I'm trying to recall this from memory so I haven't tested the above code to make sure that's 100% right. If it doesn't work let me know.

Take care,
Slenish

[jimatqsi]

Or just use the NZ function. NZ(forms!mainform!text1) will not return an error even if the value is NULL.

Me.Voornaam = [Forms]![InvoerDownloads2]![Voornaam]

should be

Me.Voornaam = nz([Forms]![InvoerDownloads2]![Voornaam])

Jim

[zmbd]

@Harry38:
1) You're actually very lucky that Slenish was still around to help with your question. In fact, if Slenish had not been so kind as to answer, your post most likly would have been removed because you've technically hijacked this thread. In the future, please create a new thread including a link to the original for context/reference if needed.

2) Please use the [CODE/] formatting button in the toolbar to format you posted code or tables. I realize that several posts in this thread do not have this formatting. At the time that may not have been a requirement; however, it is a requirement for all newer posts.

3) This thread appears to deal with a database that is not properly normalized as one does not normally store the same information in more than one table. If you are doing so, then please reconsider the design of your appliction.