Returning User login- Sometimes it's quicker to just ask

[GohDiamond]

I'm still working on creating that HR Database. I've successfully created the NEW USER logon. Now I'm stuck on getting the "Returning User" verified during logon.
From a simple form with 2 unbound controls, we ask for the allegedly existing UserID and password. The premise is as follows:
There is a query qry_Employees linked to the form in the background. We're not editing anything directly in the query.

1) To Lookup/goto the record with the EmployeeID in the qry_Employees which matches the UserID on the "Returning User" Form.
2) To Check whether the Password "UserPW" provided on the form is the same as the one in the query "qry_Employees.EmpPW" for that EmployeeID/UserID
3) If its the same then send the user to the main screen and apply a FILTER to the qry_Employees.EmployeeID so that records reated to that one EmployeeID are all that's viewable. The filter is important for Privacy Concerns.

I can't seem to get the UserID isolated and the filter on so that only the User with that unique UserID is browsing the related data in the subforms.

Could anyone help with this one?

Thanks,

Goh

 

[KeithG]

You can use the DLookUp function to retrieve the password into a variable.

 

[GohDiamond]

Thanks KeithG,

I was able to get the Password by looking up the Record that matched the EmployeeID with the form's UserID then compared the form's Password with the Password in that record/row.

= DLookup("[EmployeeID]", "tbl_Employees", "[EmployeeID] = '" & UserID.Value & "'")
If UserPW = EmployeePW Then .... etc etc


I think I found that statement in another post here in the forum after some extensive searching on keyword "DLookup"

After that, I had some trouble getting the EmployeeID on the Main form to be filtered according to the UserID that was entered on the Returning User form. I want to do it all in VBA but I'm not good at syntax I guess. I was able to use a macro to:
1) open the main form,
2) go to the control EmployeeID on the main form,
3) find the record whose EmployeeID matched the existing UserID in the "Returning User" form using What =[Forms]![frm_ReturningUser]![UserID]
4) apply the filter where contition = [EmployeeID]=[Forms]![frm_ReturningUser]![UserID]
5) Close the Returning User form

Closing the Returning User form after applying the filter was important since I was using the value of the UserID as the filter parameter.

I know all this is possible in VBA but I couldn't get the syntax and the number of "" right or in the right place.

However, I do have the macro solution, but would like some insight into the VBA way, if anyone can show me a clearcut example.

Thanks to all!

Goh

 

[GohDiamond]

Oh Well, afterall my dlookup didn't work correctly.
I've simplified this for testing.
2 Forms and a table
USERS table has the UserID and UserPW

Form1 is the LoginForm with 2 unbound fields: FUserID and FUserPW
Form2 is the MainForm with lots of outer fields for data entry into table "Info"

Dim X as integer
Dim Y as integer
Dim VarX As Variant
'set VarX as the UserPW content'

VarX = DLookup ("[UserPW]", "Users", "[UserID] = " _ &Forms!Users!FUserID)

I'm thinking this should make the value of VarX = the UserPW if an equivalent UserID is found to match FUserID

then follow up with a statement

'Check if the UserPW = the FUserPW'

IF VarX = FUserPW then
'send user to the macro that switches to the main form and applies the UserID Filter'
DoCmd.RunMacro "blah blah blah... which sends the user to the main form and filters the mainform data on the UserID"

ElseIf VarX is null then
'Display custom message if there is no value set for VarX because no UserID with a UserPW'
x=MsgBox("No user with this UserID and UserPW",vbokonly)
'then do something to reset the form'

else
'display custom message when the UserPW value found for VarX doesn't match the FuserID'
y=MsgBox("Incorrect UserID or Password",vbokonly)
'then do something to reset or close the form'

End if

------------------------------------------------------------------------


Do you have any advice?

Thanks in advance!
Goh