469,282 Members | 2,123 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

How copy date from main form to subform?

4
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
Sep 26 '10 #1
8 7909
slenish
283 100+
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3. Me.TextBox1 = [Forms]![MainForm]![TransactionDate]
  4. Me.TextBox2 = [Forms]![MainForm]![TransactionDetails]
  5.  
  6. End Sub
  7.  
  8.  
Sep 27 '10 #2
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
Sep 28 '10 #3
Melo3
4
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
Sep 28 '10 #4
Melo3
4
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
Sep 28 '10 #5
@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:
Expand|Select|Wrap|Line Numbers
  1. Me.Voornaam = [Forms]![InvoerDownloads2]![Voornaam]
  2. Me.Adres = [Forms]![InvoerDownloads2]![Straat]
  3. Me.Aanhef = [Forms]![InvoerDownloads2]![Aanh]
  4. Me.Voorletters = [Forms]![InvoerDownloads2]![Voorletters]
  5. Me.Voorv = [Forms]![InvoerDownloads2]![Tussenvoegsels]
  6. Me.Adres = [Forms]![InvoerDownloads2]![Straat]
  7. Me.Postcode = [Forms]![InvoerDownloads2]![Postcode]
  8. Me.Woonplaats = [Forms]![InvoerDownloads2]![Woonplaats]
Apr 29 '14 #6
slenish
283 100+
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...

Expand|Select|Wrap|Line Numbers
  1. If forms!mainform!text1 not is null then
  2.      Me.text1= forms!mainform!text1
  3. End if
  4.  
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
Apr 29 '14 #7
jimatqsi
1,260 Expert 1GB
Or just use the NZ function. NZ(forms!mainform!text1) will not return an error even if the value is NULL.
Expand|Select|Wrap|Line Numbers
  1. Me.Voornaam = [Forms]![InvoerDownloads2]![Voornaam]
should be
Expand|Select|Wrap|Line Numbers
  1. Me.Voornaam = nz([Forms]![InvoerDownloads2]![Voornaam])
Jim
Apr 29 '14 #8
zmbd
5,400 Expert Mod 4TB
@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.
Apr 29 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by William Wisnieski | last post: by
2 posts views Thread by Claude | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.