Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 57
  1. #16
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664


    Quote Originally Posted by Greg View Post
    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.
    See The evils of Lookup fields


    You take the time to create the Look up FIELDS in tables, then have to spend the time to set a combo box row source (which is the same as the look up field).
    Why take the time set it up twice?

    Users should never be allowed access to any tables.

  2. #17
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Thank you Vlad for your new code. I still have problems. If I click the button, it correctly copies data to the first row only in the subform. It does not move forward to the next lines. If I give to your code additional syntax (see below) it otherwise moves to another line. But now I don't know how to continue process of copying data to that and next row??

    I could also have noticed that below in the immediate window it correctly displays the current ID when it is in loop process.


    May I ask what the terms in the ProcessVAT code (lID, lMinID As Long) mean, or otherwise what does lID and lMinID stand for?

    This is the syntax if I added to your code to go to the next row in subform:
    Forms!EntryHead.SetFocus
    Forms!EntryHead!GeneralLedger.SetFocus
    DoCmd.GoToRecord
    Me.SetFocus

  3. #18
    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,

    lId is the ID pass to the sub from the loop in the click event of the button, lMinID is the minimum ID (first id as you have them sorted ascending by ID, not always fool proof by the way).
    So if lID=lMinID you treat that as the first record, if lID>lMinID then it is a subsequent one.

    With your added code is it working now? You should have that code inside the loop just before calling the new sub (befole Call ProcessVAT(lngID,lMinID).

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

  4. #19
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, remains the question: why copy data? Databases are all about having no redundant data.

  5. #20
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Hi,
    I copy data while data are input in Forms. First step is to fill data in Main Form, where also one unique Document is given. Based on that Document are all other data linked and entered in subform where I can immediately see "live" how it is posted. I want to see this right while I’m posting, for the control and not somewhere behind the tables!
    The VAT form is made because I couldn’t find any other way how to calculate and divide one amount into more amounts and into different accounts and rows etc. So the only way I found out is through Form. So from that Form on the data need to be copied (transferred) to subform. Only then is a ONE document successfully posted.

  6. #21
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    OK, as I understand it you have a main table with the base document and a related table with the records split over several accounts and amounts.
    First of all, you only need to copy the primary key of the main table to the linked table where it serves as foreign key. If you want to automatically create several linked records, I would do this using a recordset (I always use ADODB, but ADO can do the trick as well) based on a select statement on the main table looking like "select * from tbl1 where ID = " & me.txtID (substitute the object names with your own names).
    Then , using ADODB or ADO : calculate the amounts to create the new records for the subform in a recordset and add these directly to your linked table.
    Then refresh the form and subform, all new records should be available in the subform.

    But first check the normalization of your data structure. If that isn't correct, you will run into more and more problems.

  7. #22
    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'll admit I dont understand your data or your workflow, or your design for that matter. It may be your explanation as you refer to "Main Form", "unique document", "Posted" and "seeing live"
    As previously noted your using lookup fields in your tables and calculated fields and have some redundant info your tables.
    Most ledger type apps that I've seen dont use credit and debit fields, they use TransactionType(credit,debit) and TransactionAmount fields.

    Again I admit I dont understand this but it just seems to me that this should be much simpler. This type of work around leads me to believe that your structure needs to be better normalized.

  8. #23
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Ok design may not be understandable, but I am just sure that instead of 3 click on 3 different buttons I can use only ONE click and one button instead. There is definitely a way to do that. Is this really so complicated to write all that in one code?? I am looking all over the internet what all that loop, Recordsets, arguments, Call statement etc. means just to learn and move on to write this code to the end.

  9. #24
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    So it is still not working? Have you made the change I suggested?

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

  10. #25
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Yeah still stuck with your code but it is at least good point to study from it... By the way, does anyone know why I can't use the F5 and F8 keys to check the process oof code in my Access VBA. Something like what those two video shows?
    When I press F5 it only pops up a dialog box about selecting a Macro??


    https://youtu.be/yyC5VaYYoOA?t=349
    https://youtu.be/6UIypRy1iE8?t=171

  11. #26
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Greg,
    Can you please post the code you currently have and fails? Have you added the docmd.gotonext before calling the sub as suggested inside the loop?
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #27
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Hi Vlad, I haven't made any changes to your code excep that I added code to add new row when set focus in subform. Have you any idea why I cant' run sub with F5 or F8 key. I’d like to see how the process in code flow so that I can better understand where the current problem is.
    Thank you for help!

  13. #28
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you please try this updated sub:
    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
    
    
    Else
        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
            
            '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
        
        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
    To step through the code put a break on the start of the loop and click the button; the code should stop on that line (the line will be highlighted in yellow). Now keep pressing F8 to move through the code one line at the time.
    Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	59.4 KB 
ID:	42507
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #29
    Greg is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    44
    Hi Vlad, again thank you for the updated code.
    I dide just a quick test and It’s seemed that it still doesn’t work all the process. If I have example with Document of amount of 130 (100+20+10) it goes to the second row and after that it doesn’t continue any more. If I have Document with 120 (100+20) it stays in first row. I need to check all that myself again and let you know If I’ve made any progress.

    Thank you very much for showing me how to process with F8. So I need to use button and then it works!

  15. #30
    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 following this thread and I have to say I am very confused. It looks like you are overwriting existing records in the many table....??
    Also, there are several issues in the table designs, but if it works for you......


    Looking at Vlad's code in Post #28, there is an error at the line
    Code:
        Set rs = Me.RecordsetClone
       rs.Filter = "[ID]" = lID
    The recordset "RS" does not get filtered.

    Recordset.Filter property (DAO) says
    Sets or returns a value that determines the records included in a subsequently opened Recordset object (Microsoft Access workspaces only). Read/write String.
    Using the code examplein that site, I modified Vlad's code to use 2 recordsets.
    "rsT" gets the current form's recordset clone then (the T = temp)
    "rs" gets the filtered recordset from "rsT".
    Code:
            Set rsT = Me.RecordsetClone
            rs.Filter = "[ID] = " & lID
            Set rs = rsT.OpenRecordset
    I also changed the "IF...ELSE...END" IF structure to a "SELECT CASE...END SELECT" structure - to me it is cleaner.
    Attached Files Attached Files

Page 2 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