Login Form - Masked Unique Key Problem

I designing the login form in Access 2003.But I have many problem since the unique key (primary key) use Masked.

Access Structure
Staff (Table)
- StaffID (PK field)'masked as S0001 ,S0002 ; actual storing is 1,2 and so on
- Password (field)
- Position (field)

I want to to check StaffID and Password match with the Staff.StaffID and Staff.Password.

If correct password match the open Main Menu.
Meanwhile,assign their position to globle variable to globle_StaffPosition


Currently the StaffID,user can key in the actual data which is 1 or 2 or 3 to log in.....In fact,they will key in S0001 or S0002 to log in

And the globle_StaffPosition part how to assign it ? Is it my SQL command wrong ?

Private Sub okButton_Click()
Dim UserTxt1 As Long

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

If IsNull(Me.UserTxt) Then
MsgBox "Please enter a valid user name.", vbExclamation, "Invalid User Name"
Me.UserTxt.SetFocus
Exit Sub
ElseIf IsNull(Me.PassTxt) Then
MsgBox "Please enter a valid password.", vbExclamation, "Invalid Password"
Me.PassTxt.SetFocus
Exit Sub
End If

'Stupid method convert userInput & field data into long format to avoid datatype error
UserTxt1 = CLng(UserTxt)
MsgBox UserTxt1, vbInformation, "Welcome"



rs.Open "SELECT StaffID , Password FROM Staff WHERE( CLng(StaffID) = """ & UserTxt1 & """ AND Password = """ & Me.PassTxt & """)", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.EOF Then
MsgBox "Invalid user name or password. Please try again.", vbExclamation, "Error"
Me.UserTxt.SetFocus
Exit Sub
End If

'Error here,anyone know how to assign it ?
globle_StaffPosition = rs.Open "SELECT Position FROM Staff WHERE( CLng(StaffID) = """ & UserTxt1 & """)", CurrentProject.Connection , adOpenKeyset, adLockOptimistic


MsgBox "Login Successful.", vbInformation, "Welcome"
DoCmd.Close
DoCmd.OpenForm "Main menu", acNormal

Sorry,I am new to VB & SQL
Hope someone can help me.....
Thanks for your reply

[elbweb]

all you need to do is take an extra step..

current:

'Error here,anyone know how to assign it ? 
globle_StaffPosition = rs.Open "SELECT Position FROM Staff WHERE( CLng(StaffID) = """ & UserTxt1 & """)", CurrentProject.Connection , adOpenKeyset, adLockOptimistic

should work:

'Error here,anyone know how to assign it ? 
rs.Open "SELECT Position FROM Staff WHERE( CLng(StaffID) = """ & UserTxt1 & """)", CurrentProject.Connection , adOpenKeyset, adLockOptimistic
globle_StaffPosition = rs!Position

[elbweb]

This got a little lengthy so it basically turned into a quick and easy explination about record sets, and how to use them.


explination:

rs.open does not return any values per se, what is does, is creates a recordset object full of every record that was returned from the query.

for example, say i have a table as follows:

tblPeople

ID Name Position
1 Joe Low
2 Bob Higher
3 Lisa Lower
4 Tom Middle
5 Jane Worthless


hard to read, i know, but oh well...
so now i do an rs.Open "SELECT * FROM tblPeople;"
what this returns the entire table as a recordset

defautly it would be on the first record, which in this case would be 1 Joe Low
you can change the order that they come in with an ORDER BY clause within the original SQL statement, for example, if i made is rs.Open "SELECT * FROM tblPeople ORDER BY Name;" the first record would then be 2 Bob Higher.

next, to pull data from the current record you use an exclimation point (pronounced bang when you speak it out, for example table People Bang ID would be tblPeople!ID). When you do this it gives you the information for that field in the current record.

you can also pull data by saying rs("ID"), that would also pull the ID for the current record. The difference here is that you notice the quotes around it, that represents an absolute. You can remove the quotes and then call on a variable for the first name, which can be useful sometimes. For example if i created a variable sField and set it equal to ="ID" then i could say rs(sField) and pull the information for the ID field in the table.

Next we need to learn about moving around the recordset. You can use the rs.MoveNext, MoveLast, MovePrevious, and MoveFirst to move around the recordset. useful for cyclying through records.

Also when opening record sets there is a WHERE clause to specify conditions, which you already know. I would like to just say that there is also a LIKE clause where you can find things that are not exaclty what you are looking for, but contain it with wild cards.For example rs.Open "SELECT * FROM tblPeople WHERE Name LIKE 'J*';" would return Joe and Jane, the names starting with a J, you can put astriks on both sides to find something containing the string, or the begining to find something ending in the string.

Now will be a time to talk about the .EOF property, this stands for End Of File. This would be true in two events, one you moved through the recordset past the last record, or two you created a recordset with no records, for example is i said rs.Open "SELECT * FROM tblPeople WHERE ID='6';" rs.EOF would be true.

last two things would be adding and removing records. rs.AddNew creates a new record. After this command you assign values to the parts of the record as shown before with the bang punction (for example rs!Name = "Jack" would set the new records name field to Jack.) after you finish setting all the fields, you do a rs.Update and that adds the new record to the table, and applys all auto numbers, and then moves to that record for you to use it.

and example would be:

rs.Open "SELECT * FROM tblPeople;"
rs.AddNew
rs!Name = "Jack"
rs!Position = "Pretty OK"
rs.Update
intNewID = rs!ID
rs.Close

that would open the table people, add a new record for jack, update the table, return jacks new ID number (assuming it was an autonumber field) and then close out the record set.

and last but not least, is rs.Delete adAffectCurrent that is the command to delete the current record, and afterwards you need to do a rs.Update just as before.


welll.. that turned into more then i thought, but hope it was helpful

also i would like to point out my default code for using a recordset, anytime is as follows:

Function xConnect()
    Dim rsTemp As ADODB.Recordset
    
    Set myConn = CurrentProject.Connection
    Set rsTemp = New ADODB.Recordset
    
    rsTemp.CursorLocation = adUseClient
    rsTemp.CursorType = adOpenStatic
    rsTemp.LockType = adLockOptimistic
    
    rsTemp.Open strSQL, myConn
    
    rsTemp.Close
    
    Set rsTemp = Nothing
    myConn.Close
    Set myConn = Nothing
End Function

i put it in a function, in a modular, so i can quilckly find it and copy it to where i want to use it, it has no real functionality.

elbweb,thanks for your information...
after few hours research,I found that rs.open and docmd.RunSQL do not support return value.....my godness...
Now I try to read and understand your post and figure out it later..........

again thank you....

[elbweb]

yup, the basic jist is that those return a set of records, not just a single value, so think of it as another table that the code is going through.

so if you have a table, and you tell code, get me the value of the table,its not going to work, you have to specifiy the record and the field within the table, my post just explains how to work through those records and fields.

[Lightwave]

That is a real good explanation elbweb