Limit Database Record Count

[abelingaw]

What i wanted to do is to limit the number of users to be registered for my system.

One for User type and one for Administrator account, and if there is already a user and admin account, adding will be disabled, else, enabled.

Here's the code im using.

Private Sub Usercount()

Con "Database.mdb"

With RecSet
    .Open "Select * From Users", DBLink, adOpenKeyset, adLockPessimistic
    
        If lvwUser.ListItems.Count = 2 And .Fields("UserType") = "Administrator" Then
            cmdAdd.Enabled = False
            cmdAdd.grayScale = aiGreenMask
            MsgBox "Limit of allowed users is reached. Adding of user is no longer allowed.", vbInformation, "Prompt"
        Else
            cmdAdd.Enabled = True
            cmdAdd.grayScale = aiNoGrayScale
        End If
        
       .Close
    
DBLink.Close

End With
End Sub

Any ideas? Thanks

[AndreRet]

You went the long way around.:)

With RecSet
    .Open "Select * From Users WHERE UserType = 'Administrator'", DBLink, adOpenKeyset, adLockPessimistic

If .Recordcount = 1 Then
MsgBox "Limit of allowed Admin users is reached. Adding of Admin user is no longer allowed.", vbInformation, "Prompt"
End If

You can use the same to check for "user", or you can just check if 2 records exist, then give message box. I would use the first part, because if there is no admin, create one etc.