Results 1 to 4 of 4
  1. #1
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368

    Loop through records

    Hey guys,

    I made a code that enables users to set an appointment in Outlook through my application and then optional select another computer to set the same appointment in.
    When the other computer opens up the application, the appointments are automaticly extracted to outlook.

    This works well with one appointment at a time, but i want to loop through the records untill criteria are met.



    Code:
        Dim strUsername As String
        'FOSUserName is a function that extracts the network login name of users.
        strUsername = fOSUserName()
    
        'Look if there are records that meet criteria. When the username matches a record and the appointment is not exported yet then we move on
       Dim strSQLid As String
        strSQLid = "SELECT DISTINCT IDagenda FROM tblAgenda " _
         & "WHERE ChoosePostBox = '" & strUsername & "' AND AfspraakGeexporteerd = False"
          With CurrentDb.OpenRecordset(strSQLid)
           If .RecordCount > 0 Then
       
      'I want to loop as long as the recordcount returns greater then zero
       Do Until .RecordCount > 0
       
       Dim IntSQL As Integer
       IntSQL = DLookup("IDagenda", "tblAgenda", "ChoosePostBox = '" & strUsername & "' AND AfspraakGeexporteerd = False")
       
       With CreateObject("Outlook.Application").CreateItem(1)
            .Subject = DLookup("OnderwerpAgenda", "tblAgenda", "IDAgenda = " & IntSQL & "")
            .start = DateValue("" & (DLookup("DatumAgenda", "tblAgenda", "IDAgenda = " & IntSQL & "")) & "") + TimeValue("" & (DLookup("TijdAgenda", "tblAgenda", "IDAgenda = " & IntSQL & "")) & "")
            .Duration = 5
            .Location = DLookup("NaamPlaatsAgenda", "tblAgenda", "IDAgenda = " & IntSQL & "")
            .Body = DLookup("BerichtAgenda", "tblAgenda", "IDAgenda = " & IntSQL & "")
            .Save
       End With
       
     'Flag records exported to Outlook
       Dim strFlagTrue As String
       strFlagTrue = "Update tblAgenda set AfspraakGeexporteerd = true where IDagenda = " & IntSQL & ""
       DoCmd.RunSQL strFlagTrue
     
     'When all records are done (AfspraakGeexporteerd = true) then outta here 
       If .RecordCount = 0 Then Exit Do
       Loop
       
      End If
     End With
    I know im doing it wrong (hence i am here). Dlookup can handle only one record so i need to tab through the records.
    Any pointers ?

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Dim rs1 as Recordset
    Set rs1 = CurrentDb.OpenRecordset(strSQLid)

    Do Until rs1.EOF

    "Perform Action"

    rs1.MoveNext
    Loop

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I've never been able to nail down if Do Until is any different than Do While Not rs.EOF in these cases. Some claim that the loop will still execute on an empty recordset, thereby generating an error on the move next, but I haven't found that to be the case. The gods (small g) and MS use the latter in their code examples, so that is good enough reason for me. I have also found that many more posted solutions use the latter over the former. So aside from deciding which is "more" correct, the only thing I think is missing is that the recordset type should always be declared as either DAO (preferred) or ADODB.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Thanks Bulzie, works like a charm !

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

Similar Threads

  1. Loop to add multiple records
    By roarcrm in forum Forms
    Replies: 3
    Last Post: 05-19-2015, 07:02 AM
  2. Replies: 17
    Last Post: 04-07-2014, 07:48 PM
  3. Replies: 5
    Last Post: 10-08-2012, 12:05 PM
  4. Loop through records
    By sam10 in forum Programming
    Replies: 12
    Last Post: 07-07-2011, 02:30 PM
  5. Loop through Records and Make ID
    By rob4465 in forum Programming
    Replies: 3
    Last Post: 01-14-2010, 10:46 AM

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