Loop records in infinite loop until record is made

[Ruegen]

I had a hard time understanding some of your posts, but it sounded like what you needed to do was just stop the Timer while the code was running. Which can also be done easily - either by coding the Form's Timer property to 0, or else by wrapping your Timer event in an If statement that tests for a Boolean variable, which you'll flag to "do not run" (etc) when running the code.

Just like in Excel programming, you must turn off Application Events during the code that runs within an application event, so that infinite loop (or prematurely broken loop) is not created.

Yes normally I would do that but I also use the timer for other events, like a clock that ticks away on the form. We call different states so having the time on a form is important.

[ipisors]

It seems to me that you must stop the Timer event from firing during such time as the code IN your timer event is running.
Up to you ...

PS, you may also be able to write some of the things you desire as 'background' runs in VBscript. It would require some very creative looping and possibly some ugly GoTo statements, but at least you would get the benefit of continuous running AND with zero interface.

[Ruegen]

I got this working without having to stop the timer event. I put an exit do on the loop - which then ends the if statement. Then when the user clicks the ok button on the popup form it sets the temp var back to true and the process loops through all over again.

works like a charm and all in the background

Here is the code

If TempVars!tmpTO_Messages = True Then


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim T As Integer
    Dim strSQL As String
    
    Set db = CurrentDb
    
    strSQL = "SELECT tblMessages.MessageID, tblMessages.MessageText, tblMessages.MessageSentDate, tblMessages.MessageReceivedDate, tblMessages.MessageOrganiserID " _
    & "FROM tblMessages " _
    & "WHERE (((tblMessages.MessageReceivedDate) Is Null) AND ((tblMessages.MessageOrganiserID)=" & [TempVars]![tmpTourOrganiserName] & "));"
    
    Set rs = db.OpenRecordset(strSQL)
    
    T = 0
    
    Do Until T = 1000
        If rs.RecordCount > 0 Then
        TempVars!tmpTO_Messages = False
        DoCmd.OpenForm "frmMessagesRecieved"
        Forms("frmMessagesRecieved").Form.Requery
        Exit Do
        End If
    
    T = T + 1
    Loop
    
    
    rs.Close
    Set rst = Nothing
    End If

So now I have made myself an internal messaging system