code from several steps together (1 Viewer)

[GregAcc]

Hi,

I am having a problem setting up a code that copy data from other forms into each single line into one datasheet subform.

What I managed to do was copy an individual parts of code into just one row in a form.

What I want to is how to compile that code that will make all the steps at once, something like that:

Make first point (1a, 1b) in first row

then go to next row and do 2a, 2b, 2c

and finally got to to next (third row)

and do 3a, 3b, 3c

Since I am more of a beginner in VBA I would be very happy for any idea or if you write the right code for me.

Thank you!

Private Sub PostVAT20_Click()
'Copy (Post) from VAT Form to Sub Form

    'FIRST ROW:
    '1a) First set focus to (first) New record in subform
    Forms!EntryHead!GeneralLedger.SetFocus
    DoCmd.GoToRecord , , acNewRec
    
    
    '1b) Copy from Main Form to first row Record in a subform:
    Forms!EntryHead.GeneralLedger.Form.Account = Forms!EntryHead.Account
    Forms!EntryHead.GeneralLedger.Form.CustCode = Forms!EntryHead.CustCode
    Forms!EntryHead.GeneralLedger.Form.Refer = Forms!EntryHead.Refer
    Forms!EntryHead.GeneralLedger.Form.Debit = Forms!EntryHead.Debit
    Forms!EntryHead.GeneralLedger.Form.Credit = Forms!EntryHead.Credit
    Forms!EntryHead.GeneralLedger.Form.Narration = Forms!EntryHead.Narration
    
    
    'SECOND ROW:
    '2a) Then set focus to the NEXT (second) record Record in a subform
    Forms!EntryHead!GeneralLedger.SetFocus
    DoCmd.GoToRecord , , acNewRec
    
    '2b) Copy from Main Form to that (second) row Record in a subform:
    
    Forms!EntryHead.GeneralLedger.Form.CustCode = Forms!EntryHead.CustCode
    Forms!EntryHead.GeneralLedger.Form.Refer = Forms!EntryHead.Refer
    Forms!EntryHead.GeneralLedger.Form.Narration = Forms!EntryHead.Narration
        
        
    '2c) Copy from VAT Form to that (second) row Record in a subform -NET Value:
    Forms!EntryHead.GeneralLedger.Form.Account = Forms!VAT.ContraAccount
    Forms!EntryHead.GeneralLedger.Form.Debit = Forms!VAT.NetValue20
    
    
    
    'THIRD ROW:
    '3a) Then set focus to the NEXT (third) record Record in a subform
    Forms!EntryHead!GeneralLedger.SetFocus
    DoCmd.GoToRecord , , acNewRec
    
    '3b) Copy from Main Form to that (third) row Record in a subform:
    
    Forms!EntryHead.GeneralLedger.Form.CustCode = Forms!EntryHead.CustCode
    Forms!EntryHead.GeneralLedger.Form.Refer = Forms!EntryHead.Refer
    Forms!EntryHead.GeneralLedger.Form.Narration = Forms!EntryHead.Narration
        
    '3c) Copy from VAT Form to (third) row Record in a subform -VAT Value:
    Forms!EntryHead.GeneralLedger.Form.Account = Forms!VAT.VATAccount20
    Forms!EntryHead.GeneralLedger.Form.Debit = Forms!VAT.VAT20
    
        
    
End Sub

 

[theDBguy]

Hi. Welcome to AWF!

Not sure I understand your question. Are you trying to add records to a table? Are you talking about a bound form in continuous view? Tables or forms, don't really have rows in Access (like they dobin Excel). You'll need a primary key to identify which record you want to work with.

 

[GregAcc]

I am trying add record to a subform which is in Datasheet view, yes this i a bound form. I can pass data in first row if we I run only one part of the code.

 

[theDBguy]

I am trying add record to a subform which is in Datasheet view, yes this i a bound form. I can pass data in first row if we I run only one part of the code.

Okay, to add multiple records to a form bound to a table, you can append the record to the table one at a time or all at once and then requery the form afterwards.

 

[GregAcc]

I didn’t quite understand what that would mean. Can I not add just row by row to this subform? This subform ("GeneralLedger") is based on a table with the same name "GeneralLedger" where I already get data from that subform?

 

[Uncle Gizmo]

I think you need to post your database here. If it's too big and/or contains confidential information, then create a new database with just the Forms in question, along with some sample data. One of the best places to get sample data is the Microsoft Northwind Sample Database. It's also worth having a copy, because you can explore it and pickup some ideas. You can download a copy here:-

Northwind Downloads - Nifty Access

Northwind Downloads Microsoft provide some very nice downloadable databases which you can use to get an insight into how Microsoft Access functions. In particular, and I believe it was the first, or at least one of the first examples, "The Northwind Database" is a fine example of an invoicing...

www.niftyaccess.com

alternatively there's a video demonstrating how you can download it directly from within MS Access

Setup the Northwind dB - Nifty Access

Northwind Database I'm not sure when the Northwind database became available, but it seems to me like it's been around right from the beginning. I have blogged about the Northwind database several times on the Nifty Access Site...See below for info on How to set Northwind up Setup the Northwind...

www.niftyaccess.com

 

[GregAcc]

I think I will just confuse you with this base because it is still a developing base and it need some core changes. Anyway I'm sending a base..

I would just like to copy the data from form “VAT” by clicking "POST" to the subform GeneralLedger. That being said, it works without problems, but I would like just that all that steps code would do ALL at once.

The final result in subform should be as shown in image “2-POST FINAL.png”

 

[GregAcc]

Any ideas would be appreciated?