Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Match Against Oldest (97)

    I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Match Against Oldest (97)

    I have attached a demo database that processes the payments in a VBA procedure. You may have to open the module, select Tools | References..., clear the check box for a reference starting with MISSING, and set a reference for the Microsoft DAO 3.5* Object Library.

    The code opens a recordset on the payments table, and another recordset on the invoices table, sorted on invoicenum and on invoicedate. The code loops through the first recordset and tries to find a match in the second one.

    Sub ProcessPayments()
    Dim dbs As DAO.Database
    Dim rstINV As DAO.Recordset
    Dim rstPYM As DAO.Recordset
    Dim strSQL As String
    Dim strWhere As String

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    strSQL = "SELECT * FROM tblPayment WHERE MatchTo_PYM Is Null"
    Set rstPYM = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    strSQL = "SELECT * FROM tblInvoice ORDER BY InvoiceNum_INV, InvoiceDate_INV"
    Set rstINV = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    Do While Not rstPYM.EOF
    strWhere = "InvoiceNum_INV = " & Chr(34) & rstPYM!InvoiceNum_PYM & Chr(34) & _
    " AND PaymentRcvd_INV = False"
    rstINV.FindFirst strWhere
    If rstINV.NoMatch Then
    MsgBox "Can't find a suitable match for " & rstPYM!InvoiceNum_PYM & "!", vbInformation
    Else
    rstINV.Edit
    rstINV!PaymentRcvd_INV = True
    rstINV.Update
    rstPYM.Edit
    rstPYM!MatchTo_PYM = rstINV!AutoID_INV
    rstPYM.Update
    End If
    rstPYM.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rstINV.Close
    Set rstINV = Nothing
    rstPYM.Close
    Set rstPYM = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Against Oldest (97)

    Hans,

    Thanks so much - I'll give it a try and let you know.

    Texas Pink Bird

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •