Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2012
    Posts
    56

    Unanswered: Do while Loops ( 3 )

    Hi I have been stuck for a couple of weeks and its time to call for help.

    Code
    --------------------------------------------------------------------------------------
    Set rsTempRentalPymt = db.OpenRecordset("tblTempRentalPayments", dbOpenDynaset)
    Set rsRentalPymt = db.OpenRecordset("tblRentalPayments", dbOpenDynaset)
    Set rsShiftMain = db.OpenRecordset("tblTransMain", dbOpenDynaset)

    rsTempRentalPymt.MoveFirst

    Do Until rsTempRentalPymt.EOF

    rsRentalPymt.MoveFirst

    Do Until rsRentalPymt.EOF

    If rsTempRentalPymt!Trans_ID = rsRentalPymt!Trans_ID And rsTempRentalPymt!InvoiceNumber = rsRentalPymt!InvoiceNumber And _
    rsTempRentalPymt!Type = "Closed" Then
    rsShiftMain.Edit
    rsShiftMain!Payment_ID = 1
    rsShiftMain.Update

    ElseIf rsTempRentalPymt!Trans_ID = rsRentalPymt!Trans_ID And rsTempRentalPymt!InvoiceNumber = rsRentalPymt!InvoiceNumber And _
    rsTempRentalPymt!Type = "Paid Thru Shift" And rsRentalPymt!Type = "Paid Thru Shift" And rsTempRentalPymt!InvoiceNumber = rsShiftMain!Trans_ID Then
    rsShiftMain.Edit
    rsShiftMain!Payment_ID = 2
    rsShiftMain.Update

    End If

    rsRentalPymt.MoveNext

    Loop

    rsTempRentalPymt.MoveNext

    Loop

    rsShiftMain.Close
    rsTempRentalPymt.Close
    rsRentalPymt.Close

    I am trying to loop through two recordsets find a record then make an edit on a third recordset based on the values found in the second.

    Have found lots of information on two recordsets but nothing on a third.

    Thanks In Advance Bob

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    cant you do this as an update query?

    there's times when you have to iterate through recordsets, but they are relatively rare, and I don't think this one.

    youd need to construct the join carefully
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    This can be done instantly as a single query instead of VBA : X*Y*Z loops.
    It looks correct..whats it not doing?

Posting Permissions

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