Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    Loop records in infinite loop until record is made

    Would it be possible that when a form loads - a dao recordset opens in the background and loops through a table and keeps looping or loops to the end and the requeries?



    the reason being I want to have records appear if notnull in a date

    then a form would open suddenly to alert the staffer about that record

    at the moment I currently already do this however I use the timer event to loop a hidden form and make it appear after requery which is done every 10 seconds and in my mind isn't the best way of doing this...


    if the same thing is all done in vba - then I don't see anything.

  2. #2
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Something like

    Code:
    If Me.Form.RecordsetClone.RecordCount = 0 ThenMe.Form.Visible = False
    End If
    
    
    Me.Text64 = 1
    Me.Text64 = 50
    Dim rs As DAO.Recordset
    
    
    Set rs = Me.RecordsetClone
    
    
    
    
    Do Until Me.Text68 = 100
    
    
    If rs.RecordCount > 0 Then
    
    
        Me.Form.Visible = True
        
    End If
    
    
    
    
    Me.Text64 = Me.Text64 + 1
    
    
    Loop
    This just hangs the system... of course

  3. #3
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    It's just a big overhead, don't use the timer event for that. when the db opens check your values. then when the table that you are checking gets changed "many ways to check it" show your form, it can only be changed from the locations you provide, forms, vb code. unless your users have direct access to the table, "not a good idea"

  4. #4
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    P.S. You can also ensure an entry is made by using the table properties, required = yes, allow zero lengh = no, and validation rule.

  5. #5
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    The concept I have working with the timer ticks away in the background (hidden) and after a requery displays a record (because the record count is no longer 0) it displays the form - this gives me a pop up form that acts like an internal message system. The staff can select another staff member on an entry form and message a new message back (creating a new record with that persons id from the selection in a field of that new record)

    Of course once they've checked "ok" the form hides again

    But I was hoping for something else to refresh that - without doing a timed requery, something that just checks the records continuously until it changes.

  6. #6
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    You only have to check them when they change, whatever action/code changes them can include a check of the field.

  7. #7
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    Do Until Me.Text68 = 100
    If rs.RecordCount > 0 Then
    Me.Form.Visible = True
    End If
    Me.Text64 = Me.Text64 + 1
    Loop

    do you meen text68 text64 for the check, they don't Match! that's an infinite loop there.

    Doesn't need the form open, uses vba only the time it takes to loop will depend on various things, cpu, apps running, other timer avents, perhaps use the timer interval.

    Do Until T = 1000 ' If rs.RecordCount > 0 Then
    docmd.Openform "Your form"
    End If
    T = T + 1
    Loop

  8. #8
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    I think you're already doing this the only way Access can. Unfortunately there aren't many asychronous ways to do things in VBA code. Yes it's an overhead (whether small or big probably depending on your database, size of recordset, way your SQL is written, etc. etc.).

    In the past when I have had a need to constantly refresh display on a form depending on data, I have also used the Timer event with a recordset.

    The problem with the Timer event is eventually you find other things you want to put in it - even more critical to the app, probably (for example possibly something that reads whether to kick users out of the database, notification etc).

    Have to move to vb.net in order to have background workers.

  9. #9
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by trevor40 View Post
    Do Until Me.Text68 = 100
    If rs.RecordCount > 0 Then
    Me.Form.Visible = True
    End If
    Me.Text64 = Me.Text64 + 1
    Loop

    do you meen text68 text64 for the check, they don't Match! that's an infinite loop there.

    Doesn't need the form open, uses vba only the time it takes to loop will depend on various things, cpu, apps running, other timer avents, perhaps use the timer interval.

    Do Until T = 1000 ' If rs.RecordCount > 0 Then
    docmd.Openform "Your form"
    End If
    T = T + 1
    Loop
    so open a recordset based off a table and have it loop until timer gets to 1000

    but then it breaks as it is at the end of the loop

    how would I get the loop to restart in order for it to loop again

    is the concept is that every second (using timer) it checks the table/string query using rs then closes (unless >0)

    but in order for me to loop that task again? I would need it to run more than once after timer has reached 1000

    oh wait: just run the loop in the timer. got it.

    I'd probably only get it to loop every 10 seconds to (10,000) keep CPU etc down...

  10. #10
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    I'm using

    Code:
    Dim rs As DAO.Recordset
    Dim T As Integer
    Dim strsql As String
    
    
    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 = CurrentDb.OpenRecordset(strsql, dbOpenDynamic, dbReadOnly, dbOptimistic)
    
    
    Do Until T = 1000
    If rs.RecordCount > 0 Then
    DoCmd.OpenForm "frmMessagesRecieved"
    
    
    End If
    
    
    T = T + 1
    Loop
    
    
    
    
    
    
    rs.Close
    Set rs = Nothing
    However I am not sure how to make a query into a recordset

  11. #11
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    I was thinking

    Code:
    Dim rs As DAO.Recordset
    Dim db As DAO.Database 
    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)
    
    
    
    
    
    
    Do Until T = 1000
    If rs.RecordCount > 0 Then
    DoCmd.OpenForm "frmMessagesRecieved"
    
    
    End If
    
    
    T = T + 1
    Loop
    
    
    
    
    
    
    rs.Close
    Set rs = Nothing

  12. #12
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Made it work using

    Code:
    Me.txtTimeCurrent.Value = "Current time: " & Format(Now(), "medium time") & "  Date: " & Format(Date, "long date")
    
    
    
    
    
    
    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
    If TempVars!tmpTO_Messages = True Then
    Do Until T = 1000
    If rs.RecordCount > 0 Then
    DoCmd.OpenForm "frmMessagesRecieved"
    TempVars!tmpTO_Messages = False
    End If
    
    
    T = T + 1
    Loop
    
    
    rs.Close
    Set rst = Nothing
    End If
    once message received you click ok on the row and it hides the form and sets the tempvars to true

  13. #13
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    If TempVars!tmpTO_Messages = True Then

    ' this will run forever
    Do Until T = 3
    T = 1 'resets T to 1 it will never get to 3
    If rs.RecordCount > 0 Then
    Do Events
    DoCmd.OpenForm "frmMessagesRecieved"
    TempVars!tmpTO_Messages = False
    End If
    T = T + 1
    Loop

  14. #14
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by trevor40 View Post
    If TempVars!tmpTO_Messages = True Then

    ' this will run forever
    Do Until T = 3
    T = 1 'resets T to 1 it will never get to 3
    If rs.RecordCount > 0 Then
    Do Events
    DoCmd.OpenForm "frmMessagesRecieved"
    TempVars!tmpTO_Messages = False
    End If
    T = T + 1
    Loop
    the problem I find now is yes it opens the form (in turn unhiding it because I have it as visibility false) but it doesn't requery the form - I need it to requery the form when it gets to the action in the if statement. I don't put the tempvars to false in the statement (I let the ok button on the message form do that, there might be more than one message, but I will try that one anyway). If I put the requery after dicmd.openform - it loops that continuously which is not good.

    I think if I put a requery on form open it might fix that problem but I will have to test that on monday when I get back to work.

  15. #15
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 09-19-2013, 03:07 PM
  2. Replies: 3
    Last Post: 03-10-2013, 07:04 AM
  3. Possible Infinite Loop
    By dandoescode in forum Access
    Replies: 1
    Last Post: 05-18-2012, 11:03 AM
  4. Loop through records
    By sam10 in forum Programming
    Replies: 12
    Last Post: 07-07-2011, 02:30 PM
  5. Replies: 9
    Last Post: 04-28-2010, 11:20 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