Results 1 to 13 of 13
  1. #1
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118

    VBA or Macro to copy and paste?

    I have two open continuous forms, one showing available PO Numbers, the other showing lines on an invoice that require a PO Number.
    The user could:



    • Copy PO Number from Available PO Number Query
    • Paste into PO Number field on Invoice table
    • Enter current date into UsedDate field in Available PO Number Query

    But this would be time consuming given that several hundred lines need to be done. So what I would like is a button that will do the 3 above steps in one click. I am sure this should be quite basic VBA but I am a novice when it cones to VBA. Any help appreciated

    See attached diagram

    Thanks
    Attached Thumbnails Attached Thumbnails Copy Query.jpg  

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Looking at your example data tables I think it might be a good idea to first take a step back. Can you explain the big picture of what's going on here? The whole process? I suspect there could be a way to automate this.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMO your tables are not set up right. There is no unique value that relates one table record to any other record in the other table. Also, you are duplicating information (client ID as well as name). I think what you're expecting is that when the row button is clicked, the PO number will go into every record in Invoices where client id in Invoices = client id in PO. So every invoice for a given client that's in the table will always get the same PO? Odd but doable. However it suggests there is something amiss with the design. When invoices are created, there ought to be a tie in between the PO and the invoice at the time of invoice record creation.
    Last edited by Micron; 08-19-2021 at 02:26 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    More Clarity on question as requested.
    The tbl_Invoice is a temporary table used purely to create a CSV file that can be uploaded into the accounting software, it will be cleared and re used for the next invoice run. This table has many fields made up by linking related tables and concatenating fields, so they are in the correct format for the upload (other fields not shown on example as irrelevant to question).
    The Qry_Available_PO_Numbers is derived from a table listing PO (Purchase Order) numbers that where “DateUsed” is null. This is linked to the Client table using a 1 to many relationship on Client ID.
    Scenario:
    The company (a company will send us many clients) will send a batch of PO numbers (around 30 at a time) for each client. A client’s invoice (under that company) should include the next available PO number in order that the invoice is paid.
    Issues:

    • Not all companies provide PO Numbers therefore not all invoices require one.
    • Some Client invoices have several lines therefore the same PO Number will be used.

    I did originally want to use an update query to automate the allocation of PO numbers to the invoice however because of the many variables this is impracticable. Therefore my (interim) solution is a human decision on what PO numbers to allocate to what invoice lines. As mentioned in my original question this could be done by copying the next available PO Number, flagging it a used by entering the current date in the DateUsed field then pasting that PO number against the appropriate invoice line in the invoice table. I am just trying to speed this up for the user. Ideally it would be good to drag and drop the PO Number to the appropriate invoice live but I am not sure if this is possible in VBA (certainly not at my skill level) so I would like to:

    • Click the button (or PO Number field)
    • Copy value in that field
    • Automatically enter the current date into the DateUsed field
    • Then click the appropriate PO Number field in the tbl_Invoice and pasts the value in from the clipboard.


    I understand that the one bits of code need to be behind the ‘On Click’ property of the PO Number field or button and the pasting code needs to be behind the ‘On Click’ property of the PO Number field in the tbl_Invoice.But I have no idea how to write that code (only been using VBA a few weeks)

    Thanks, in anticipation.I hope this explanation make my problem clearer.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with micron re possible table/relationship issue. It may help to know more about the database design in your accounting software - specifically the relationship between Invoice and PurchaseOrder. If an Invoice can be associated with multiple PurchaseOrders, how to assign these correctly? It isn't clear where the Access starts and stops and where the accounting software deals with payments/invoices/purchase orders. (Invoice against PurchaseOrder; Payment against Invoice)
    I think manual assignment of Invoice-PurchaseOrder leaves a lot of room for issues. If we knew more about the overall architecture, more focused advice may occur.
    See this link for info on similar topic.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So if I explained the goal this way - I click the button and the PO number on that record gets written to Tbl_Invoice.[PO Number] wherever client ID = client Id on the button row/record and update the date field to the current? The problem I see there is, which record in the other table do you update - the first that is Null?

    This ought to be doable with just an update query instead of line by line button clicking and just update all where Tbl_Invoice.[PO Number] wherever client ID = client Id.
    I suppose if going the button route it would make sense to only show the PO once in the table and update all (as above) with one click.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Thanks Micron, that was my original thinking however if a client has several invoice lines on the same day (one invoice) each line would share the same PO Number so I don't think that would work (would it?).

    The CSV I am trying to produce for upload into the accounting software is required to have the following structure:
    *ContactName
    EmailAddress
    POAddressLine1
    POAddressLine2
    POAddressLine3
    POAddressLine4
    POCity
    PORegion
    POPostalCode
    POCountry
    *InvoiceNumber
    Reference (this is the field I am trying to put the PO Number into)
    *Invoice date
    *DateDue
    Total
    InventoryCode
    Description
    *Quantity
    *UnitAmount
    Discount
    *AccountCode
    *TaxCode
    TaxAmount
    TrackingName1
    TrackingOption1
    TrackingName2
    TrackingOption2
    Currency
    BrandingType

    The field prefixed with an asterisk are mandatory.
    My tbl_Invoice includes all these field and is populated with all the correct invoicing data apart from the PO Number which is what I am trying to do.
    My Qry_Avalable_PO_Numbers contains all the “Available PO Numbers” for each client (If ClientID is 1234 then this is a PO Number that must be used for client with ID 1234) Although the ClientID is not required in the CSV upload file, I have also included it in the Invoice table.

    There is no hard a fast rule as to what invoice lines require a PO number (other than it must be for the same client it was issued against) Sometimes I client may have several invoice lines and would share one PO Number. Sometimes on PO Number would be required, sometimes a PO number would be required but there would be none available. This is just an interim solution and I am happy with how all of the above is working.
    What I am looking for is two bits of code:
    On Click of Button or PO_Number field in Available_PO_Numbers
    Copy value of PO_Number field
    Enter Current Date into DateUsed field
    Then
    On Click of PO_Number field in tbl_Invoice
    Paste the value

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Thanks Micron, that was my original thinking however if a client has several invoice lines on the same day (one invoice) each line would share the same PO Number so I don't think that would work (would it?).
    Then I have to go back to what I originally said - I don't see a relationship or why (for example) PO-777 in the 1st record couldn't/wouldn't go against ID 3 in Invoices table. I suspect you can draw all the arrows you want but it doesn't help me (us?) with the logic. Perhaps out of all the other fields you're not showing, there's something there that would make sense of it. Maybe there are other wise readers who will come up with something before I can review your last detailed post and see if I can make sense of everything.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Micron, Sorry but I think we are over complicating my question.

    Which is:

    What VBA code would I use to copy a value from a field (lets say the field name is Forms![Qry_Avalable_PO_Numbers]![PO_Number])
    What VBA code would I need to enter the current date into a field (lets say the field name is Forms![Qry_Avalable_PO_Numbers]![DateUsed])
    What VBA code would I need to paste the value on the clipboard into a field (lets say the field is called Forms![tbl_Invboice]![PO_Number])


    All the other information was given on request but not relevant it could be any form any field.

    Thanks

  10. #10
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If the values are on the same form then the VBA is simple

    Copy a value

    Me.MyDestinationControlName = Me.MySourceControlName

    Put todays date into a Control

    Me.MyDateControl = Date()


    Put todays date and the time into a Control

    Me.MyDateControl = Now()

    The clipboard thing is much harder.
    Access doesn't intrinsically understand the clipboard, so you need a fair chunk of code to get to the clipboard contents. (If someone suggests using Send-keys please ignore them!)

    An easier and more reliable way would be to lookup the value you want and update the appropriate value on the form you are on, or run an update query to update the value if it's not on the form you are on.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Thanks! I'll give that a go

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    A PO has many invoices (PO line items) ? So you just need a mechanism to assign invoice (line items) to a PO? One PO per day?

    Does the user need to pick what goes where or can your app just assign all unassigned invoices to one unused PO number (per client)?

    I'm still thinking you can automate the whole process
    Step 1. Query a list of clients with unassigned invoices
    Step 2. Looping through each client:
    A. Query or dlookup one unused PO number for that client
    B. Execute update query to set PO number for all unassigned invoices for that client
    C. Execute update query to set date used for PO in PO table

  13. #13
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Here is an example VBA sub that is intended to automate the process as I described in my previous post. I've highlighted items in red that you will need to change to correspond to your table and field names, do this carefully! I noticed in your example data that in one table the field was [PO Number] while the other table was [PO_Number], small differences like that can lead to headaches. (This relationship should really be an ID field btw)

    This is untested. If you want to try this do so in a backup database!

    Code:
    Private Sub BatchAssignInvoicesToPO()
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim rsClientIDs As DAO.Recordset
        Dim rsPO As DAO.Recordset
        Dim qry As String
        
        Set db = CurrentDb
        
        'Get a list of Clients by ID that have unassigned invoices
        qry = "SELECT DISTINCT [ClientID] FROM [Invoice Table Name] WHERE [PO Number] Is Null;"
        Set rsClientIDs = db.OpenRecordset(qry, dbOpenSnapshot)
        
        'If the list is not empty
        If Not (rsClientIDs.BOF And rsClientIDs.EOF) Then
            'move to the first record in the list (maybe not necessary? nothing wrong w/ being explicit)
            rsClientIDs.MoveFirst
            
            'loop through list of client IDs until we reach the end
            Do While Not rsClientIDs.EOF
                'lookup an unused PO Number. EDIT: using 'order by' clause to get the next sequential po_number
                qry = "SELECT TOP 1 [PO_Number], [DateUsed] FROM [PO Table Name] WHERE [DateUsed] Is Null AND [ClientId] = " & rsClientIDs![ClientId] & " ORDER BY [PO_Number] ASC;"
                Set rsPO = db.OpenRecordset(qry, dbOpenDynaset)
                
                'make sure we actually found an unused PO
                If Not (rsPO.BOF And rsPO.EOF) Then
                    'assign all unassigned invoices for this client to the PO
                    qry = "UPDATE [Invoice Table Name] SET [PO Number] = '" & rsPO![PO_Number] & "' WHERE [PO Number] Is Null AND [ClientID] = " & rsClientIDs![ClientId]
                    db.Execute qry, dbFailOnError
                    
                    'set the date used on the PO
                    rsPO.Edit
                    rsPO![DateUsed] = Date
                    rsPO.Update
                End If
                
                rsPO.Close
                
                rsClientIDs.MoveNext '<-- EDIT: Don't forget this or you'll get hung up in an infinite loop... don't want to cause a gravitational singularity, that was a close one!
            Loop
            
        End If
        
        rsClientIDs.Close
        
    ExitHandler:
        Set rsClientIDs = Nothing
        Set rsPO = Nothing
        Set db = Nothing
        Exit Sub
        
    ErrHandler:
        MsgBox Err.Description, , "BatchAssignInvoicesToPO ERROR #" & Err.Number
        Resume ExitHandler
    End Sub
    Does your [PO Number] field allow zero length values in the invoice table?
    Last edited by kd2017; 08-20-2021 at 12:26 PM. Reason: Fixed coding error

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Ho to create a copy and paste macro
    By George F in forum Macros
    Replies: 9
    Last Post: 02-26-2018, 10:02 AM
  2. Macro to copy and paste
    By chr1stoper1 in forum Macros
    Replies: 2
    Last Post: 04-11-2016, 06:51 AM
  3. Replies: 3
    Last Post: 03-19-2015, 02:11 PM
  4. Replies: 1
    Last Post: 01-21-2015, 11:33 PM
  5. Replies: 2
    Last Post: 06-06-2011, 04:18 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