Page 1 of 4 1234 LastLast
Results 1 to 15 of 57
  1. #1
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44

    how to distinguish between the first and all other records

    Hi,

    I need to copy some data from one form to another. I have made two buttons and each has its own code. What I’d like is now is to combine both codes and trigger it all together with one button in one code only.
    Basically, both codes are the same with just one exemption that in the first code have additional parameters to transfer data to the FIRST line only. For all other lines the second code (button) is used.



    The question is how to distinguish between the first line and all the following records. Is this possible?

    In the example in the attached picture the first code is used for the record id 69 and the second code for all other records (ID 70,71,72)?

    I appreciate any help in leading me to the right direction.
    Attached Thumbnails Attached Thumbnails vat transfer.jpg  

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    It would help to see your code.

  3. #3
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Here is hope it's not too illogical, i'm more of a beginner at coding.

    How can I get info when a filter is applied to a form and I am currently on FIRST record. How to obtain this property? After that I could apply that info in my code.

    Thank you.

    Code:
    Private Sub PostVATID1_Click()
    
    '' FIRST ROW:
    
    
    '1.a) Set focus to (first) New record in GL subform
        Forms!EntryHead!GeneralLedger.SetFocus
       
             
    '1.b) Copy from Main Form to the first row Record in a subform GL:
        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
        
        
    '2. Go to the next (second row) Record in a subform GL
      
         On Error Resume Next
        Forms!EntryHead.SetFocus
        Forms!EntryHead!GeneralLedger.SetFocus
        DoCmd.GoToRecord
        Me.SetFocus
        
        
    '' SECOND ROW:
      
    '3. Copy from Main Form to a subform GL (second record):
       
        'copy from Main Form (Refer, Narration)
        Forms!EntryHead.GeneralLedger.Form.Refer = Forms!EntryHead.Refer
        Forms!EntryHead.GeneralLedger.Form.Narration = Forms!EntryHead.Narration
    
    
    
    
    '4. Copy from VAT Form (COST):
        
    
    
    If cmbVATtr.Value = 0 Then
    
    
        'copy from VAT form COST data(account,amount)
        Forms!EntryHead.GeneralLedger.Form.Account = Me.ContraAccount
        Forms!EntryHead.GeneralLedger.Form.Debit = Me.NetValue00
    
    
    
    
    ElseIf cmbVATtr.Value = 1 Then
    
    
        'copy from VAT form COST data(account,amount)
        Forms!EntryHead.GeneralLedger.Form.Account = Me.ContraAccount
        Forms!EntryHead.GeneralLedger.Form.Debit = Me.NetValue
    
    
         On Error Resume Next
        Forms!EntryHead.SetFocus
        Forms!EntryHead!GeneralLedger.SetFocus
        DoCmd.GoToRecord
        Me.SetFocus
    
    
    
    
    '' THIRD ROW:
    
    
        'copy from Main Form (Refer, Narration)
        Forms!EntryHead.GeneralLedger.Form.Refer = Forms!EntryHead.Refer
        Forms!EntryHead.GeneralLedger.Form.Narration = Forms!EntryHead.Narration
    
    
    
    
        'copy from VAT Form VAT data (VAT acc., VAT amount)
        Forms!EntryHead.GeneralLedger.Form.Account = Me.VATAccount
        Forms!EntryHead.GeneralLedger.Form.Debit = Me.VatValue20
    
    
    
    
     ElseIf cmbVATtr.Value = 2 Then
     
        'copy from VAT form COST data(account,amount)
        Forms!EntryHead.GeneralLedger.Form.Account = Me.ContraAccount
        Forms!EntryHead.GeneralLedger.Form.Debit = Me.NetValue
    
    
         On Error Resume Next
        Forms!EntryHead.SetFocus
        Forms!EntryHead!GeneralLedger.SetFocus
        DoCmd.GoToRecord
        Me.SetFocus
    
    
        'copy from Main Form (Refer, Narration)
        Forms!EntryHead.GeneralLedger.Form.Refer = Forms!EntryHead.Refer
        Forms!EntryHead.GeneralLedger.Form.Narration = Forms!EntryHead.Narration
    
    
    
    
        'copy from VAT Form VAT data (VAT acc., VAT amount)
        Forms!EntryHead.GeneralLedger.Form.Account = Me.VATAccount
        Forms!EntryHead.GeneralLedger.Form.Debit = Me.VatValue10
    
    
    
    
    ElseIf cmbVATtr.Value = 9 Then
    
    
        'copy from VAT form COST data(account,amount)
        Forms!EntryHead.GeneralLedger.Form.Account = Me.ContraAccount
        Forms!EntryHead.GeneralLedger.Form.Debit = Me.NSV
    
    
        End If
    
    
        
    End Sub


    Private Sub PostVATIDx_Click()


    '2. Go to the NEXT Record in a subform GL

    On Error Resume Next
    Forms!EntryHead.SetFocus
    Forms!EntryHead!GeneralLedger.SetFocus
    DoCmd.GoToRecord
    Me.SetFocus

    '3. Copy from Main Form to a subform GL (second record):

    'copy from Main Form (Refer, Narration)
    Forms!EntryHead.GeneralLedger.Form.Refer = Forms!EntryHead.Refer
    Forms!EntryHead.GeneralLedger.Form.Narration = Forms!EntryHead.Narration


    '4. Copy from VAT Form (COST):

    If cmbVATtr.Value = 0 Then


    'copy from VAT form COST data(account,amount)
    Forms!EntryHead.GeneralLedger.Form.Account = Me.ContraAccount
    Forms!EntryHead.GeneralLedger.Form.Debit = Me.NetValue00


    ElseIf cmbVATtr.Value = 1 Then


    'copy from VAT form COST data(account,amount)
    Forms!EntryHead.GeneralLedger.Form.Account = Me.ContraAccount
    Forms!EntryHead.GeneralLedger.Form.Debit = Me.NetValue

    On Error Resume Next
    Forms!EntryHead.SetFocus
    Forms!EntryHead!GeneralLedger.SetFocus
    DoCmd.GoToRecord
    Me.SetFocus

    'copy from Main Form (Refer, Narration)
    Forms!EntryHead.GeneralLedger.Form.Refer = Forms!EntryHead.Refer
    Forms!EntryHead.GeneralLedger.Form.Narration = Forms!EntryHead.Narration


    'copy from VAT Form VAT data (VAT acc., VAT amount)
    Forms!EntryHead.GeneralLedger.Form.Account = Me.VATAccount
    Forms!EntryHead.GeneralLedger.Form.Debit = Me.VatValue20

    ElseIf cmbVATtr.Value = 2 Then

    'copy from VAT form COST data(account,amount)
    Forms!EntryHead.GeneralLedger.Form.Account = Me.ContraAccount
    Forms!EntryHead.GeneralLedger.Form.Debit = Me.NetValue

    On Error Resume Next
    Forms!EntryHead.SetFocus
    Forms!EntryHead!GeneralLedger.SetFocus
    DoCmd.GoToRecord
    Me.SetFocus

    'copy from Main Form (Refer, Narration)
    Forms!EntryHead.GeneralLedger.Form.Refer = Forms!EntryHead.Refer
    Forms!EntryHead.GeneralLedger.Form.Narration = Forms!EntryHead.Narration


    'copy from VAT Form VAT data (VAT acc., VAT amount)
    Forms!EntryHead.GeneralLedger.Form.Account = Me.VATAccount
    Forms!EntryHead.GeneralLedger.Form.Debit = Me.VatValue10


    ElseIf cmbVATtr.Value = 9 Then

    'copy from VAT form COST data(account,amount)
    Forms!EntryHead.GeneralLedger.Form.Account = Me.ContraAccount
    Forms!EntryHead.GeneralLedger.Form.Debit = Me.NSV

    End If


    End Sub

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I'm a little confused why your copying from form to form. Are the fourms unbound?
    Cant you use recordsets?

  5. #5
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Basically what I do is first calculate VAT on one separate form (VAT) and then I passed results from that form in a form GeneralLedger which is a subform of a main form Entry Head.
    I don’t know much about recordsets. Do you think I should use recrodserat instead? Something like shown in this video? https://youtu.be/WNm17l54z1c or you maybe have any other references I can take a look at?

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    First you have to determine : what do you mean by first record? This has only sense in combination with a sort order. In principle the records in a recordset have no order. If you change the sort order, another record will be the first.
    Forms are a way to display the record sets. Here you can find some information about recordsets: https://docs.microsoft.com/en-us/off...set-object-dao https://docs.microsoft.com/en-us/off...ith-recordsets

  7. #7
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    By first record I mean first row in current filtered table. I have solved that so that I put new field with formula CurrentRow=([ID]-Min([ID])+1) in. So that way can it now recognize which is the first line.

    Now I have to trigger the code for each line in this form separately. How do I scroll through the lines sequentially.
    So for example. When I am in first row (field CurrentRow show =1) then I need to process code 1. When I am in row 2 (CurrentRow=2) then make process code 2 and so on for each next row just use code 2.
    How can I do this, do I need to use some kind of Loop to make it work?

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    This may be easier to understand if you post an example DB.

  9. #9
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    If I look at your logic I strongly get the idea that row 1 = header record and the other records are depending records in a one to many relationship. I strongly suspect that your database structure needs to be reworked. As moke said, an example of your database could help.

  10. #10
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Ok, here is example:

    Invoice net value: 100 (see Credit in Entry form)
    VAT: 20
    Zero-Rated: 20
    Not subject to VAT: 10
    Total Invoice: 150


    This is filled in VAT2 form in the FIRST row:

    • Net value for VAT tax (base) = 100
    • VAT (20%) = 20 (see column »VAT deductible 20%«)


    This is filled in VAT2 form in the SECOND row:

    • Zero-Rated, Exemptions 0%: 20


    This is filled in VAT2 form in the THIRD row:

    • not subject to VAT: 10



    When the VAT2 form is completed and when you click all the buttons in sequence as shown in the “VAT” image in the attachment then you get the correct postings as shown by Form Entryhead in attached image “Result”.

    So how can I put all clicks from 1 to 4 into just one single click or in one code alltogether?
    Attached Thumbnails Attached Thumbnails 1_entry.jpg   2_vat.jpg   3_result.jpg  
    Attached Files Attached Files

  11. #11
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Put all your code into one sub that takes the record ID (69 for first, etc.) or CurrentRow (1 for first) as argument then in the header of the form add a button that loops through your records:

    Code:
    Dim rs as DAO.Recordset,lndID as Long
    Set rs= Me.recordsetclone
    
    if rs.recordcount=0 then exit sub
    
    Do Until rs.EOF
       lngID=rs("ID")  'lngID=rs("CurrentRow")
       Call ProcessVAT (lngID)
    rs.MoveNext
    Loop
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Thank you Gicu for your response and code. Sorry bus I still have a problem how to put both codes into one sub? Where I need to put both codes, before your code or somewhere in between your code. Can you help me any more? Thank you!

  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    One thing you may want to consider is getting rid of all the lookup fields in your tables.

  14. #14
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    But you can tell me briefly why not? The reason I like to use it is because i like to have drop-down list to pull the right data.

  15. #15
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at the updated file,I think it does what you wanted but I would definitively reconsider the design, copying between forms is not the recommended way, would say you should either use queries or VBA with recordsets.
    As for the lookups you can have your drop downs (in forms as users shouldn't interact with tables or queries directly) by simply using a combobox control where needed. Read a bit about the lookup fields here:http://access.mvps.org/access/tencommandments.htm.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-09-2016, 08:58 AM
  2. Replies: 1
    Last Post: 04-06-2016, 09:26 AM
  3. Replies: 2
    Last Post: 07-29-2015, 07:50 AM
  4. Replies: 4
    Last Post: 03-29-2014, 01:29 AM
  5. Replies: 1
    Last Post: 01-24-2013, 05:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums