Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loop through records (Access 2000)

    I have a query that grabs all the records that have the current date in the date field.
    What I would like to do is when a form is opened, a message box would open for each record in the query. With the code below I can grab the first record but I can't get the others.

    For example:
    Three records in the query
    Hello
    Goodbye
    SeeYa

    When the form is opened I would like a message box to open saying "Hello", when the user closes this message box another opens and says "Goodbye", when the user closes this one the next opens, etc.

    Here is the code I am currently using
    Private Sub Form_Current()

    Dim rs As DAO.Recordset
    Dim qd As DAO.QueryDef
    Set qd = CurrentDb.QueryDefs("QuRemindersMsg")
    qd.Parameters(0) = Me.DateX
    Set rs = qd.OpenRecordset

    MsgBox rs!reminder

    End Sub

    Any ideas would be greatly appreciated.

    Thanks,
    Eric

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Loop through records (Access 2000)

    Eric,

    You are very close. After your "set rs = .....", put this instead:

    Do Until rs.EOF = True
    MsgBox rs!reminder
    rs.movenext
    Loop
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop through records (Access 2000)

    It is now giving me the "Object variable or with block variable not set" error.

  4. #4
    New Lounger
    Join Date
    Sep 2002
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop through records (Access 2000)

    I don't know what was wrong but now it is working. Thanks for the code. I did not know that function existed.

  5. #5
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Loop through records (Access 2000)

    Hi Mark,
    I found this post while looking for help with looping through records. I tried to incorporate the Do until code but it doesn't go to the next record. Did I do something wrong? Here's my code:

    Dim rs As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim strPhotosPath, strPhotosSource, strPhotosDestination As String
    Dim stPolicy, stItem, stRnn, stEnn, stRelID As String
    Dim stFilepath As String
    Dim stAdvantekReportName, stAdvantekReportFilter, stAdvantekFileName As String
    Dim stSummaryReportName, stSummaryReportFilter, stSummaryFileName As String
    stFilepath = Me.PathToSaveTo

    On Error GoTo HandleErr
    stSummaryReportName = "rptInspectionSummary"
    stSummaryReportFilter = "qryrptSummaryPrintout"
    stAdvantekReportName = "rptAdvantek"

    Set qd = CurrentDb.QueryDefs("qryfsubBrokersInspections")
    qd.Parameters(0) = Me.SelectBroker
    Set rs = qd.OpenRecordset

    Do Until rs.EOF = True
    stPolicy = Forms!frmBrokersInspections!fsubBrokersInspections .Form!PolicyNumber
    stItem = Forms!frmBrokersInspections!fsubBrokersInspections .Form!ItemNumber
    stRnn = Forms!frmBrokersInspections!fsubBrokersInspections .Form!Rnn
    stEnn = Forms!frmBrokersInspections!fsubBrokersInspections .Form!Enn
    stRelID = Forms!frmBrokersInspections!fsubBrokersInspections .Form!RelID

    strPhotosSource = Me.PathToPhotosOnServer & stPolicy & "*.*"
    strPhotosDestination = Me.PathToSaveTo & stPolicy
    'Create the directory
    MkDir strPhotosDestination
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    strPhotosDestination = strPhotosDestination & ""
    fs.CopyFile strPhotosSource, strPhotosDestination
    'Print the Summary report to an RTF file
    stSummaryFileName = stFilepath & stPolicy & "Summary.rtf"
    DoCmd.OpenReport stSummaryReportName, acViewPreview, stSummaryReportFilter, ("[PolicyNumber] = Forms!frmBrokersInspections!fsubBrokersInspections .Form!PolicyNumber")
    DoCmd.OutputTo acOutputReport, stSummaryReportName, acFormatRTF, stSummaryFileName, 0
    DoCmd.Close acReport, stSummaryReportName
    'Print the Advantek report to an RTF file
    stAdvantekFileName = stFilepath & stPolicy & "Advantek.rtf"
    DoCmd.OpenReport stAdvantekReportName, acViewPreview, , ("[RelID] = Forms!frmBrokersInspections!fsubBrokersInspections .Form!RelID")
    DoCmd.OutputTo acOutputReport, stAdvantekReportName, acFormatRTF, stAdvantekFileName, 0
    DoCmd.Close acReport, stAdvantekReportName
    rs.MoveNext
    Loop

    ExitHere:
    Exit Sub

    HandleErr:
    Select Case Err.Number
    Case Else
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Form_frmBrokersInspections.cmdRun_Click"
    End Select

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Loop through records (Access 2000)

    Sue,

    Your code is a little hard to read without indents, but it looks like it should work. Does it appear to process the first record? That is, is it passing thru the Do...Loop even once? And are you absolutely sure there is more than 1 record in your query?

    The one thing I see that I think will be a problem, however, is the .openform that you do. Access doesn't stop at that those doCmd commands and wait for them to be completed. It moves immediately to the next line of code and processes it. The only way to make it sit and wait is to open a form in which "WindowsMode" is set to "acDialog".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Loop through records (Access 2000)

    Mark, are you referring to the DoCmd OpenReport (I cannot see any OpenForm commands)?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Loop through records (Access 2000)

    Yes, I meant the docmd.openreport lines. To see what is happening with your code, merely comment out all the "DoCmd" lines, and insert a single msgbox that displays some info about each record you read. Then when you run your code, how many times does that msgbox pop up.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop through records (Access 2000)

    If there were 20 records with a matching date I, as user, would get pretty fed up with having to click each one away. Is there a reason why you don't concatenate them together before display (eg. group in fives for readability should there be a large number) - you just use a msgbox, you haven't said if you act on the yes / no / cancel keypress that the msgbox can return.

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

    Re: Loop through records (Access 2000)

    Sue,

    None of the code between <font face="Georgia">Do Until rs.EOF = True</font face=georgia> and <font face="Georgia">rs.MoveNext</font face=georgia> refers to the recordset rs. There *are* references to controls on the subform fsubBrokersInspections, but from the point of view of this code, the values of these controls are constants. In particular, strPhotosDestination is the same throughout the loop, so <font face="Georgia">MkDir strPhotosDestination</font face=georgia> will cause an error on the second pass through the loop, because the directory has already been created in the first pass. Even if this error didn't occur, the loop wouldn't do what you want (I suppose): the report would be opened with the same WHERE-condition each time. Also, you're creating a new instance FileSystemObject in each pass, and never destroying it. This is inefficient and causes memory leakage.

    If you explain what you want to do, perhaps someone will be able to help. Perhaps you intend to loop through the records of the subform, but that's not what your code does.

  11. #11
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Loop through records (Access 2000)

    Here
    Attached Files Attached Files

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

    Re: Loop through records (Access 2000)

    The problem is that you refer to fields/controls of the subform inside the loop; their values will be those of the current record in the subform in each iteration of the loop, i.e. they will be the same each time. You need to refer to fields of the recordset instead.

    I have attached a revised version of your code as a text file; I have indicated my modifications by comments marked ***HV***.

    Notes:
    <UL><LI>If you declare variables as

    Dim a, b, c, d As String

    only d is actually declared as a string; the others are declared as variants (the default type). In VB/VBA, you must explicitly name the type of *each* variable:

    Dim a As String, b As String, c As String, d As String

    This is different from, for instance, Pascal/Delphi, and even Microsoft programmers sin against it!
    <LI> You only need to create the FileSystemObject object once, not in each iteration of the loop. It's good programming practice to set an object to Nothing even if an error occurs, so I put that part in the exit handler, and added Resume ExitHere to the error handler.
    <LI>Since you actually want to loop through the records of the subform, you could probably define the recordset as a clone of the recordset of the subform.
    <LI>In the DoCmd.OpenReport instructions, I had to put the references to fields of the recordset in the Where-condition outside the quotes; you can't refer to a recordset in a Where-condition as you can to a control on a form.[/list]HTH
    Attached Files Attached Files

  13. #13
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Loop through records (Access 2000)

    Thank you so much Hans. It works like a charm! I'm learning so much from you guys. Thanks for helping me out again.

Posting Permissions

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