Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 57
  1. #31
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Hi Vlad,
    I have made some little changes (see my CAPITAL comments in the code) so that process can continue copying into second and the third row. What I did is that I just removed the Else statement after (If lID = lMinID Then) and then I put back extra code to move to next record. This work just fine if I have only one row in VAT form.

    However, since I removed ELSE statement the process got stuck when I am in the second row in VAT form. So that would be something like (If lID > lMinID Then….) or that I put back ELSE statement somewhere in the code. I am not sure how and where to put that.

    Could you help me please?
    So in short: I have current updated code let’s say CODE1 which works fine for first row.
    Now I need to put back ELSE statement where the process will continue as written in CODE2 below.



    CODE1:
    Code:
    Private Sub ProcessVAT(lID, lMinID As Long)Dim rs As DAO.Recordset
    If lID = lMinID Then 'first record
        '1a) Set focus to (first) New record in GL subform
            Forms!EntryHead!GeneralLedger.SetFocus
                 
        '1b) 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
    
    
    '
    'HERE I REMOVED "ELSE" STATMENT
    '
    '1c) Go to next row in a subform GL
        Forms!EntryHead.SetFocus
        Forms!EntryHead!GeneralLedger.SetFocus
        DoCmd.GoToRecord
        
        
        Set rs = Me.RecordsetClone
    
    
    
    
        rs.Filter = "[ID]" = lID
    
    
    
    
        '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+VAT):
        
        If cmbVATtr.Value = 0 Then
        
            'copy from VAT form COST data(account,amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("ContraAccount")  'Me.ContraAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("NetValue00") 'Me.NetValue00
            
        ElseIf cmbVATtr.Value = 1 Then
            'copy from VAT form COST data(account,amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("ContraAccount")  'Me.ContraAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("NetValue") 'Me.NetValue
            
        '
        'HERE I ADDED THIS:
        '
            '1c) Go to next row in a subform GL
        Forms!EntryHead.SetFocus
        Forms!EntryHead!GeneralLedger.SetFocus
        DoCmd.GoToRecord
        
        
          'copy from VAT Form VAT data (VAT acc., VAT amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("VATAccount") 'Me.VATAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("VatValue20") ' Me.VatValue20
            
    
    
          
        ElseIf cmbVATtr.Value = 2 Then
        
            'copy from VAT form COST data(account,amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("ContraAccount")  'Me.ContraAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("NetValue")  'Me.NetValue
            
            'copy from VAT form COST data(account,amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("ContraAccount")  'Me.ContraAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("NetValue")  'Me.NetValue
            
             'copy from VAT Form VAT data (VAT acc., VAT amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("VATAccount") 'Me.VATAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("VatValue10") 'Me.VatValue10
            
            
        'HERE I ALSO ADDED THIS:
            '1c) Go to next row in a subform GL
        Forms!EntryHead.SetFocus
        Forms!EntryHead!GeneralLedger.SetFocus
        DoCmd.GoToRecord
    
    
            
        ElseIf cmbVATtr.Value = 9 Then
        
            'copy from VAT form COST data(account,amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("ContraAccount")  'Me.ContraAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("NSV") 'Me.NSV
             
        End If
        rs.Close
        Set rs = Nothing
    End If
    
    
    
    
    End Sub

    CODE2:
    Code:
               
     
        '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+VAT):
       
        If cmbVATtr.Value = 0 Then
       
            'copy from VAT form COST data(account,amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("ContraAccount")  'Me.ContraAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("NetValue00") 'Me.NetValue00
           
        ElseIf cmbVATtr.Value = 1 Then
            'copy from VAT form COST data(account,amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("ContraAccount")  'Me.ContraAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("NetValue") 'Me.NetValue
           
            '1c) Go to next row in a subform GL
        Forms!EntryHead.SetFocus
        Forms!EntryHead!GeneralLedger.SetFocus
        DoCmd.GoToRecord
       
       
          'copy from VAT Form VAT data (VAT acc., VAT amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("VATAccount") 'Me.VATAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("VatValue20") ' Me.VatValue20
           
     
         
        ElseIf cmbVATtr.Value = 2 Then
       
            'copy from VAT form COST data(account,amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("ContraAccount")  'Me.ContraAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("NetValue")  'Me.NetValue
            
            'copy from VAT form COST data(account,amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("ContraAccount")  'Me.ContraAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("NetValue")  'Me.NetValue
           
             'copy from VAT Form VAT data (VAT acc., VAT amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("VATAccount") 'Me.VATAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("VatValue10") 'Me.VatValue10
           
           
          '1c) Go to next row in a subform GL
        Forms!EntryHead.SetFocus
        Forms!EntryHead!GeneralLedger.SetFocus
        DoCmd.GoToRecord
     
           
        ElseIf cmbVATtr.Value = 9 Then
       
            'copy from VAT form COST data(account,amount)
            Forms!EntryHead.GeneralLedger.Form.Account = rs("ContraAccount")  'Me.ContraAccount
            Forms!EntryHead.GeneralLedger.Form.Debit = rs("NSV") 'Me.NSV
            
        End If
        rs.Close
        Set rs = Nothing
    End If
     
     
    End Sub

  2. #32
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Hi Steve,

    Thanks for your suggestions. Since I'm more inexperienced in coding and it's harder to follow different codes i will try to continue on the code from Vlad.

  3. #33
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by Greg View Post
    I don’t know much about recordsets. Do you think I should use recrodserat instead?
    You do realize that your forms are displaying recordsets? By copying from form to form you are writing from recordset to recordset.
    when going row to row in your form you are actually going from record to record in the recordset. Constantly setting the focus to the next record on the form could be disasterous. I'm sure the code runs fast enough to avoid this but if the focus is shifted (perhaps by clicking on another control) your code breaks.
    It seems to me you are doing this the hard way.

    I dont know how far along you are in your project so I can appreciate your not wanting to go backwards. The most important part of building a database is to start with a good foundation. Without a proper foundation you will do nothing but try to apply fixes to correct the defects.

    Maybe take a look at some data models and see if theres one that fits in with your project . . .http://www.databaseanswers.org/data_models/

  4. #34
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Greg,
    Sorry but I am not sure I follow your logic, in the code 1 section you are moving next multiple times. I agree with moke123 that you should use recordsets (I suggested that earlier in post # 15). Can you please explain the flow in a bit more detail. I think what you are trying to do is to use the VAT table as a temporary holder for records that eventually need to be copied to the GeneralLedger table. Is the VAT2 form meant to edit existing records in GeneralLedger table, create new ones or both?
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #35
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Hi Vlad,
    The VAT table serves as the basis for calculating VAT. Based on that table, the data are copied to the GeneralLedger table. I couldn’t find any other way to calculate VAT direct in EntryHead or generalledger form. There could be several tax cases (vat transactions). In the current database there are currently 7, but the list will be updated.

    I’m not sure if in the existing structure is this possibkle, but it would be very good that the data in generlledger table could be updated if changes are made later in the VAT table?

  6. #36
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    If I go back to my table in connection with VAT transactions. So I have an example invoice of 130 where I use a 2 VAT transaction which in addition means two rows in VAT form. In the general ledger, I was able to copy the first row from the VAT form, which means three lines in the generalledger (120 = 100 + 20). Now I have to book the remaining amount (10) which is in the second row in VAT form. I stayed here while the “second” code need to be processed.

  7. #37
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Another thing that may be confusing us is all the clutter on your vat form. Are all those controls necessary or are there a lot of leftovers from things you tried?
    Could this be as simple as
    Invoice amount
    Vat 1 (if necessary)
    Vat 2 (if necessary)
    Vat 3 (if necessary)
    Vat N (if necessary)
    Remaining amount

    Meaning do you only need to select one or more items from the comboboxes and the code does the calculations.

    another thing is your master/child links to the subform. Do you need all three or just the ID_EH foreign key. I suspect you only need the one.

  8. #38
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Greg,

    Please have a look at the updated file, I replaced the copying between forms with one recordset .AddNew (for the first record) and one append query for the rest. I am not sure about the account and debit field in the query, please review the expressions to see if that is how it should be, I tried to follow the logic in the code (in some cases you overwrite the Account field in the GL subform twice).

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

  9. #39
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Hi Vlad,
    First of all, thank you very much for your new example. It works If I have invoice without VAT, this mean cmbVATtr = 0 or 9, since in that case there are only two rows need to be posted. In case VAT there are 3 rows. (120 = credit and in debit side 100 and VAT is 20). In that case it correctly post 120 and VAT, however it doesn’t post net value (debit =100). I tried to add that somehow in query but with no avail. I am not sure if that possible to add in IIf function? Sorry to confuse you with unclear instructions.

    Cheers

  10. #40
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Greg,

    Glad to help, can you please post an example with the entries in the VAT form and the expected results in the GL table/subform? I am sure we can make it work, I am not sure I follow the right logic.

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

  11. #41
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've been looking at your dB and I think you need to make some changes to your tables and relationships.
    I hid the look up tables an focused on the two tables "EntryHead" and "GeneralLedger".

    So it appears that the process is to:
    1) Enter the initial data into the form "EntryHead".
    2) Click on the button "VAT Form 2"
    3) You click on the button "Step1" which copies the data from "EntryHead" and creates a record in table "GeneralLedger". This is duplicating the data See the image below

    I drew the red lines if two fields match in the two tables.
    As you can see, there are only 2 fields that don't match: "Balance", and "ID_GL" (the PK field in "GeneralLedger").
    Click image for larger version. 

Name:	Tables1.png 
Views:	16 
Size:	25.4 KB 
ID:	42531
    Why are you duplicating data?


    Continuing on:
    4) You enter data into some controls on Form "VAT"- one control would be "Vat Transaction" (cmbVATtr). I haven't determined what other controls need data.
    5) After data is entered, you click the button "Step2". I think this button calculates the VAT values.
    6) #4 and #5 are repeated for the number of times you need to enter whatever values you need in "Vat Transaction". (2 to 4 times)


    I don't know what happens to the data in table "VAT" - maybe it is used to create a report.

  12. #42
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Hi Vlad,
    here are 3 examples.
    Invoice 1: Amount 100. Here is no VAT ("zero rated"); VATtr = 0
    Invoice 2: Gross amount 120, VAT 20, net amount 100; VATtr = 1
    Invoice 3: Amount 135 which consists of both combinations above and additional NSV: (Gross amount 120, VAT 20, net amount 100) + (zero rated 10) + (NSV 5); So here in the last example there are all three VAT Transaction used at the same invoice 3. (VATtr = 1 + VATtr = 0 and VATtr = 9)

    Cheers

    Attached Files Attached Files

  13. #43
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Hi Steve,

    "Why are you duplicating data?"
    Yes you are right the data is duplicating. I have already been warned about this. However, at the beginning I could not do otherwise to connect the master field with the child field in a form and subform. I did follow this video https://youtu.be/Jdg1H_nS2OM and I will have to make some changes later.

    "I don't know what happens to the data in table "VAT" - maybe it is used to create a report."
    Table VAT was made as base for VAT2 Form. However, I will keep it while I could make additional report from it. There are saved all VAT transactions which are entered in the VAT2 Form.

    About Step1 or Step2 this is no longer relevant, I just wanted to explain how the process works. The goal is to do everything in one step.

  14. #44
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, thanks for the explanation. Since Vlad is helping you, I will back out.

    Remember. it is easier to fix things now, rather than getting everything done, then trying to make major changes.


    Good luck with your project......

  15. #45
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by ssanfu View Post
    OK, thanks for the explanation. Since Vlad is helping you, I will back out.

    Remember. it is easier to fix things now, rather than getting everything done, then trying to make major changes.


    Good luck with your project......
    Especially when those changes eliminate the need for the work around.

Page 3 of 4 FirstFirst 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