Working with DAO RecordSet, Seek a Record (1 Viewer)

[khodr]

Hello Guys,
I have a user login form linked to a table tblUsers, I use code to search an existing user Name and if the user avilable then I get his password and level and then I click the button to move to another Form,
anyway what I want to do is to start using the recordset, I don't want to link the users table to the login form, what I want to do is.
build a form contains the following Unbound Controls.

txtUserName
txtUserPass
txtUserLevel

and whenever I enter the User Name in the txtUserName on the AfterUpdate event I want the RecourdSet to Search the table of tblUsers and prints in the text boxes the following Data

txtUserName = UserName
txtUserPass = UserPass
txtUserLevel = UserLevel
and if the txtUserName <> UserName then
Msgbox "User is not Exist"

Help will be appreciated because I think working with RecordSet is more professional for this kind of forms,

so far I know how to move to first and last record using DAO recordset, but I couldn't understand the Examples given in so many sites to how to Seek a record and get it in the active form to be a current record.

thanks in advance

 

[Beetle]

If you're only returning a single UserName then I see no reason to use the Seek method on the record set, as it should have either 1 or 0 records (assuming of course that you have no duplicate occurrences of a given UserName). Example;

Private Sub txtUserName_AfterUpdate()

    Dim strSQL As String
    
    strSQL = "Select * From tblUsers Where UserName = """ & Me.txtUserName & """;"
    
    With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If .RecordCount <> 0 Then
            Me.txtUserPass = !UserPass
            Me.txtUserLevel = !UserLevel
        Else
            MsgBox "User Name does not exist"
            Me.txtUserPass = Null
            Me.txtUserLevel = Null
        End If
    End With
            
    
End Sub

 

[khodr]

I got an error on this code, see the attached file....

 

[Beetle]

Your screenshot tells me nothing about what the error actually was, but the first thing you need to do is double check the spelling of all table/field/control names. This was just example air code, so all the naming may not be correct.

 

[khodr]

The error at the yellow highlited line the control names and field names are correct there is no problem with the spelling I am using this code on access 2010

 

[Beetle]

What is the actual error message?

 

[khodr]

thanks for the followup,
find the attached file for error descriptions

 

[JHB]

The error can occure if there is no field in the table called "UserName"!
Check if the table really contains a field with the name "UserName", yes I know you say it has but ....

 

[khodr]

Guys,
attached is the table and the form, its very basic but actually I have no Knowledge about Coding recordset on Access
I can get what I want by linking the table to the form and do search and get the data, but what I want is to Use the DAO recordset here.
again help is appreciated.

 

[JHB]

You don't have a field called "UserName" you call it "UnserName".

 

[khodr]

Lol, that was really tricky, I haven't notice it, actually it made me crazy because I tried all Recordset Tutorials and it wasn't working, so that is why, anyway I am sorry guys for that, I will check it and see how it will go, thanks again.

 

[JHB]

Beetle Wrote

..., but the first thing you need to do is double check the spelling of all table/field/control names....

You answer:

... the control names and field names are correct there is no problem with the spelling I am using ...

I simply couldn't resist.
Happy New Year to both of you.

 

[khodr]

Well it's a problem when we are sure about something it's not surely correct
Happy new year for you too

 

[Pat Hartman]

The query actually needs to include password also:

strSQL = "Select * From tblUsers Where UserName = """ & Me.txtUserName & """ AND UserPass = """ & Me.txtUserPass & """;"