Assistance with query in vba

[crisis2007]

I would like to have a user id checked. If the user id is in the table, then the user access number is changed to 2. If the user is not in the table, a message box appears stating they are not authorized. However I keep getting an error message stating, "Object doesn't support this property or method." I am using Access 2007 if that matters. I found the code and tried to adapt it to my database. Any help is greatly appreciated!


Dim db As Database
Dim sql As String
Dim rst As Recordset
Dim valid_user as Integer

Set db = CurrentDb
sql = "SELECT * FROM T_Employee WHERE UserID = Me.user_id"
Set rst = db.OpenRecordset(sql)
If rst.EOF And rst.BOF Then
MsgBox "Access to this application is not authorized"
valid_user = 0
Exit Sub ' Not a valid user
Else
valid_user = 2
End If

 

[Gammachaser]

First, your SQL statement won't work because your syntax is wrong.

It should be

sql = "SELECT * FROM T_Employee WHERE UserID = " & me.User_ID

That will get your value for me.User_ID in instead of the text 'me.user_ID' -- likely the source of your error. This syntax is good if User_ID is a number and not a string.

Second your

If rst.EOF AND rst.BOF then

is not necessary. All you need is

If rst.EOF then

If the recordset opens on the EOF marker, then it is empty. (But your code won't produce an error.)

 

[crisis2007]

Thanks for helping. Here is my code now with your suggestions. However I now receive an error, "Too few parameters. Expected 1." Did I do something wrong?

Sub display_menu()

Dim db As Database
Dim sql As String
Dim rst As Recordset
Dim valid_user As Integer

Set db = CurrentDb
sql = "SELECT * FROM T_Employee WHERE UserID = " & Forms!F_Logon.user_id
Set rst = db.OpenRecordset(sql)
If rst.EOF Then
MsgBox "Access to this application is not authorized"
valid_user = 0
Exit Sub ' Not a valid user
Else
MsgBox "Welcome"
valid_user = 2
End If


End Sub

 

[dhookom]

Is UserID numeric or text? If text then use:

sql = "SELECT * FROM T_Employee WHERE UserID = """ & Forms!F_Logon.user_id & """"

Duane
Hook'D on Access
MS Access MVP

 

[Gammachaser]

I should have accounted for that in my original post but I fell into the trap that ID would natually be numeric... and of course it could be text.

I strongly recommend that you have a primary key that is numeric.

 

[madonnac]

Sub display_menu()

Dim UserCount As long
Dim valid_user As Integer

'this line counts the number of records with User_ID matching the form field
'this is for string user ID
UserCount=dcount("*","T_Employee","User_ID=' " & Forms!F_Logon.user_id & " ' ")
' use UserCount=dcount("*","T_Employee","User_ID=" & Forms!F_Logon.user_id)
' if you are using a number
If UserCount=0 Then
    MsgBox "Access to this application is not authorized"
    valid_user = 0
    Exit Sub ' Not a valid user
Else
    MsgBox "Welcome"
    valid_user = 2
End If

End Sub

 

[dhookom]

I also reserve "ID" for numeric fields only.

Duane
Hook'D on Access
MS Access MVP

 

[crisis2007]

Thanks for your replies. My code pulls the username from the workstation computer network id which it checks against my table of usernames. I do use their employee numbers as the primary key in my database. I think I probably should have named the field "username" rather than UserID as the username is text to avoid the confusion. Thanks for the suggestions. I still have to work on this. But I currently use dCount in my existing code. I was hoping for a different method in hopes of speeding things up. However i think now my problem is basically network issues and how it is handling my database - not necessarily this code.