RecordCount Problem (1 Viewer)

[Novice1]

The following code is an event on a timer. The code works fine, with one exception.

When the form is refreshed, if there's a new customer, the Wav file is played (that's good). But after the customer is served and there are no customers in the queue, the Wav file is played once more. The Wav file doesn't play every 5 minutes, it only plays one more time, despite no records in the form. In other words, the Wav file is played one more time after the queue is empty.

It appears the form is counting a record but I don't understand why or how to correct the problem. Any help would be appreciated.
_______________________

Dim VarCount As Variant
VarCount = Forms!frmCustomersInQueue2.Recordset.RecordCount

'Refreshes the screen every 5 minutes; 300000 is equal to 360000 seconds (5 minutes)

DoCmd.Requery
DoCmd.RefreshRecord

'If there's no one in the queue then no sound is played
     If VarCount = 0 Then
          Else

'If there's one customer then a sound is played
     If VarCount = 1 Then
          Call PlayWaveFile("C:\Users\Public\Documents\SignInPlus\SignInTool\doorbell.WAV")
     Else

'If there's two customers then a sound is played
     If VarCount = 2 Then
          Call PlayWaveFile("C:\Users\Public\Documents\SignInPlus\SignInTool\doorbell.WAV")
     Else

End If
End If
End If

 

[Gasman]

No indentation due to no code tags.
Perhaps if you had indentation, the error would be easy to spot?
I do not even want to attempt to decipher that without indentation, and you have been here since 2004?
Have you walked through the code to see what value you get for varcount?
Could be 1 or 2?

 

[Novice1]

I indented with spaces ... if didn't save as such. So sorry

Varcount shows properly. I ran a Msgbox Varcount to see the counts

 

[theDBguy]

I indented with spaces ... if didn't save as such. So sorry

Varcount shows properly. I ran a Msgbox Varcount to see the counts

Hi. I added the code tags for you to preserve the indents in your original post.

 

[The_Doc_Man]

Here is my suggestion. You didn't say what to do if you had more than 2 waiting, but I would bet that really doesn't matter. You would want that doorbell to ring until the queue is empty.

Dim VarCount As Long

'Refreshes the screen every 5 minutes; 300000 is equal to 360000 seconds (5 minutes)

DoCmd.Requery

VarCount = Forms!frmCustomersInQueue2.Recordset.RecordCount

DoCmd.RefreshRecord

'If there's no one in the queue then no sound is played

If VarCount = 0 Then
          'do nothing
     Else
          Call PlayWaveFile("C:\Users\Public\Documents\SignInPlus\SignInTool\doorbell.WAV")
    End if

If it happens that you have used a named query (i.e. a STORED query) rather than an SQL statement as formCustomersInQueue2's recordset, you could simply run a DCount of that query rather than using the requery. In fact, if that code is NOT being run from frmCustomersInQueue2 then the requery won't be effective anyway. And if it IS run from frmCustomersInQueue2 then you could just use Me.Recordset.Recordcount to get the desired effect AND in that case, the requery would be more effective.

As to why the wave file was played one more time, it may have been because the count was originally being taken BEFORE the requery. But I moved it in my suggested alternative.

EDIT: Looks like theDBguy and I both wanted to do the edit. I think mine got there first since my edit note is there. Sorry, guy!

 

[Novice1]

Hi. I added the code tags for you to preserve the indents in your original post.

Thanks

 

[theDBguy]

Thanks

No worries, but it looks like @The_Doc_Man actually beat me to it. Cheers!

 

[Mike Krailo]

This is a classic case of requery or refreshing throwing the timing off. Just put a doevents after the refresh line so it finishes before the if logic runs.

 

[The_Doc_Man]

Mike, if this is native Access, it always refreshes in time. Native Access is synchronous. Now, if this is a back-end of SQL Server or any other external SQL engine, then you are correct.

 

[Mike Krailo]

Doc, I only mentioned that because it does happen that a refresh takes time to complete and if the next line of code executes right after sending the refresh, it might get the old value instead of the expected new updated value. That usually comes into play with form controls directly that may have not refreshed yet, but in this case the value being monitored is the record count of the form and not a specific control on the form so you might be right. I tested the original code the OP posted and it works just fine without modification at all. So there is something else going on in his project that was not shown to us that pertains to playing the wav file.

@Novice1, if you're still having issues, we need more information and preferably a sample database that demonstrates the problem. I had no issue at all with your posted code and cannot duplicate the problem.

 

[Novice1]

Here is my suggestion. You didn't say what to do if you had more than 2 waiting, but I would bet that really doesn't matter. You would want that doorbell to ring until the queue is empty.

Dim VarCount As Long

'Refreshes the screen every 5 minutes; 300000 is equal to 360000 seconds (5 minutes)

DoCmd.Requery

VarCount = Forms!frmCustomersInQueue2.Recordset.RecordCount

DoCmd.RefreshRecord

'If there's no one in the queue then no sound is played

If VarCount = 0 Then
          'do nothing
     Else
          Call PlayWaveFile("C:\Users\Public\Documents\SignInPlus\SignInTool\doorbell.WAV")
    End if

If it happens that you have used a named query (i.e. a STORED query) rather than an SQL statement as formCustomersInQueue2's recordset, you could simply run a DCount of that query rather than using the requery. In fact, if that code is NOT being run from frmCustomersInQueue2 then the requery won't be effective anyway. And if it IS run from frmCustomersInQueue2 then you could just use Me.Recordset.Recordcount to get the desired effect AND in that case, the requery would be more effective.

As to why the wave file was played one more time, it may have been because the count was originally being taken BEFORE the requery. But I moved it in my suggested alternative.

EDIT: Looks like theDBguy and I both wanted to do the edit. I think mine got there first since my edit note is there. Sorry, guy!

Thank you. It was as simple as moving the RecordCount after the Requery. Thank you very much