VBA Login to pass Criteria to a query and the Dlookup

[carlchapman]

Hi peps,

Sorry to post this thread if it has already been answerd, but i have not been able to work out what i need through general forum searching and google searching.

Ok, here is my problem.

I am designing my data base to include a chase system (phone call log if you will). I built a log in form in VBA that gets the user name from a table to log in.

I want the table, record (usrName) to be passed to a query on log-in, so i can run a query based on the passed usr login if that is the selected user.

As well as creating a query on theFly for user name, i also need the query to perform 2 more criteria fields, 1 is to match all records that (If user name is TRUE) THEN match chsDate to todays date and chsFlw = True.

Basicaly the 3 fields that need to be true is 1) UsrName 2) Todays Date and 3) if a tick box is true, or false (is either is easier to code).


Here is the code i have used so far,

login VBA.
<!--
Private Sub cmdLogin_Click()
If IsNull(Me.usrNameInput) Or Me.usrNameInput = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.usrNameInput.SetFocus
Exit Sub
End If

If IsNull(Me.usrPasswordInput) Or Me.usrPasswordInput = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.usrPasswordInput.SetFocus
Exit Sub
End If

If Me.usrPasswordInput.Value = DLookup("usrPassword", "tblUsr", "[salesID]=" & Me.usrNameInput.Value) Then

DoCmd.Close acForm, "Login", acSaveNo
DoCmd.OpenForm "Switchboard"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.usrPasswordInput.SetFocus
End If

End Sub
--!>

Ok, so i need to save the user name into a varible for use in a query, i am new to Access and VBa so any help would be great.

This is the query criteria i was looking at <!-- =dlookup( [FieldToLookup] , [TableToLookIn] , [FieldToMatchOn] = & [ControlNameContainingCriteria] & ) --!> So now i am hoping the above can use todays date and also make sure the other field has been selected.

Chears.

P.S, I had a few thoughts about a workaround to solve the VBA variable problem, its a badf workaround, but i figured it might help, i was thinking that within the above VBA, If user == user1 then run this query, if == usr 2 then open this 1. The only problem this would have, is that for every user i add to the database, i woulod have to manualy create a new query, and add an If statement in the VBA. ( im not being lazy, their just must be a simplier way to perform that action)