Help with a Recordset IF statement

[jtm013]

Hello All,



I am trying to set up a Recordset to loop through a table and check if any records contain a specific value.
I have the code below in place which seems to do the job, except it returns a message box for every record checked.
I only require the code to check all the records and then supply one message box with the requisite information.
How would I go about making this adjustment?

Thanks for any and all assistance!

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Select * FROM TblLogin WHERE [Login]=1")



'Check to see if the recordset actually contains rows
If Not (rst.EOF And rst.BOF) Then
    rst.MoveFirst
    Do Until rst.EOF = True
        If rst![statusCurrent] = "Online" Then
            MsgBox "Another User is Online" 
            Else
                MsgBox "No Users Online"
        End If
        'Move to the next record. Don't ever forget to do this.
        rst.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If


MsgBox "Finished looping through records."


rst.Close 'Close the recordset
Set rst = Nothing 'Clean up

[pbaldy]

Declare a string variable, replace the message box in the loop with

VariableName = VariableName & rst!UserName & vbCrLf

then after the loop, if the variable length is greater than zero, give a message box using it. Replace rst!UserName with the appropriate field from the recordset.

Personally I would add the status to the recordset SQL so that only the applicable records were returned.

[jtm013]

Personally I would add the status to the recordset SQL so that only the applicable records were returned.

Wow, I must really need more coffee- that is a much simpler idea than I was trying to do...
Final code ends up looking like this and works great.

Dim rst As DAO.RecordsetSet rst = CurrentDb.OpenRecordset("Select * FROM TblLogin WHERE [Login]=1 AND [statusCurrent]='Online'")
'"Select * FROM 'Tbl_Corrective_Action_Group' WHERE [Login] = '1'")


'Check to see if the recordset actually contains rows
If Not (rst.EOF And rst.BOF) Then
    rst.MoveFirst
    Do Until rst.EOF = True
        rst.MoveNext
    Loop
Else
    MsgBox "There are no other Users Online."
    rst.Close
    Set rst = Nothing
    Exit Sub
End If


MsgBox "There is another User Online."                                  


rst.Close 'Close the recordset
Set rst = Nothing 'Clean up


End Sub

Thanks for the assist! - SOLVED.

[pbaldy]

Happy to help! I thought you wanted a list of the users, so it can be even simpler as you don't need the loop:

Dim rst As DAO.RecordsetSet rst = CurrentDb.OpenRecordset("Select * FROM TblLogin WHERE [Login]=1 AND [statusCurrent]='Online'")
'"Select * FROM 'Tbl_Corrective_Action_Group' WHERE [Login] = '1'")


'Check to see if the recordset actually contains rows
If Not (rst.EOF And rst.BOF) Then
  MsgBox "There is another User Online."
Else
  MsgBox "There are no other Users Online."
  rst.Close
  Set rst = Nothing
  Exit Sub
End If

rst.Close 'Close the recordset
Set rst = Nothing 'Clean up

[John_G]

Here's an even easier way - doesn't even need a recordset or a Dim statement:


if DCount("*","tblLogin","[Login] = 1 AND [statusCurrent]='Online'" ) > 0 then
MsgBox "There is another User Online."
Else
MsgBox "There are no other Users Online."
End If

[June7]

Since you just want to know if the value exists and don't really do anything with the recordset, an alternative is domain aggregate DLookup() or DCount().

If Not IsNull(DLookup("ID", "TblLogin", "[Login]=1 AND [statusCurrent]='Online'")) Then

[jtm013]

Happy to help! I thought you wanted a list of the users, so it can be even simpler as you don't need the loop:

OOOOOOhhhh right. Thanks again for the assist. I am using this code as a checker to make sure no users are logged into the database before putting it down for maintenance. Okay revised final code is now:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Select * FROM TblLogin WHERE [Login]=1 AND [statusCurrent]='Online')


'Check to see if the recordset actually contains rows
If Not (rst.EOF And rst.BOF) Then
    MsgBox "There is another User Online." _
        & vbCrLf & "Use Maintenance Shutdown to Log Off the Users.", vbInformation, "Information"
Else
    CurrentDb.Properties("StartupForm") = "frmDbDown"


        MsgBox "There are no other Users Online." _
            & vbCrLf & "DB is down for Maintenance.", vbInformation, "Information."
                Me.lblDBIS.Caption = "DB Is Currently DOWN"
    rst.Close
    Set rst = Nothing
    Exit Sub
End If


rst.Close 'Close the recordset
Set rst = Nothing 'Clean Up


End Sub

[jtm013]

Since you just want to know if the value exists and don't really do anything with the recordset, an alternative is domain aggregate DLookup() or DCount().

If Not IsNull(DLookup("ID", "TblLogin", "[Login]=1 AND [statusCurrent]='Online'")) Then

I know a DLookup will work, but to be honest I wanted an excuse to use a Recordset since I am not very familiar with them and how they work, figured why not learn something new.

[June7]

Now you know they can be overkill in some situations.