Need macro to run if 2 form fields are correct (1 Viewer)

RachelKenyon · Feb 11, 2009

I have a form that has Start Date, End date, Employee ID and a "password". I have a table that has each ID and "password". How can I get the macro to run IF and only IF the Employee ID and "password" entered are correct (and not just both ON the table, but as a pair). I would prefer the simplest method as I do not know any programming or VBA or SQL.

Please help. I've been trying to work this out for a few days and I've done search after search and I am getting more and more confused.

pbaldy · Feb 11, 2009

You can use a DLookup to get the password from the table for the ID on the form, and compare the password on the form to that. If you search here for "login form" or something to that effect, you should find plenty of examples.

RachelKenyon · Feb 11, 2009

Where do I put the code? in the macro as a condition? Query? attach it to the button on my form?

DLookup("EmployeeID","tblEntry","Criteria = 'Ruby' or 'Wish'")

Does this look alright?

How/where do you compare?

RachelKenyon · Feb 11, 2009

I tried to put it in the Condition for the Macro and I got the following error:

The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'Criteria."

pbaldy · Feb 11, 2009

Typically a login form would have a button, which is where the test would be done (but it could be done other ways). A multiple criteria would look like:

DLookup("EmployeeID","tblEntry","Criteria = 'Ruby' or Criteria = 'Wish'")

RachelKenyon · Feb 11, 2009

I added the DLookup to the Where Condition of the OpenReport on the macro. It ran, however, I can write ANYTHING in the "password" box in my form and it still runs. It's not comparing the input to the table. I feel like I'm close, but where/how do I write some sort of IF statement. A link in one of your responses took me to http://www.mvps.org/access/general/gen0018.htm is this a form control that I am looking for?

pbaldy · Feb 11, 2009

Yes, and I wouldn't have it in a wherecondition. To accomplish your stated goal: "How can I get the macro to run IF and only IF" I would do something like:

Code:

If DLookup(...) = PasswordOnForm Then
  'run the macro
Else
  Msgbox "Wrong password"
End If

RachelKenyon · Feb 11, 2009

This is what I have and it doesn't run the report when the password is correct. When it is wrong, the error does pop up so I'm halfway there!

If DLookup("password", "tblEntry", "password = 'Ruby' or password = 'Wish'") = Forms!frmDateSelector!Text7 Then
'DoCmd.OpenReport rptTotal1047221, acViewPreview,,,acWindowNormal
Else
MsgBox "Wrong password"
End If

RachelKenyon · Feb 11, 2009

This doesn't work either....still trying.

Private Sub Command4_Click()
If DLookup("password", "tblEntry", "password = 'Ruby' or password = 'Disney' or password = 'Metal'") = Forms!frmDateSelector!Text7 Then
RunMacro mcrpassword
Else
MsgBox "Wrong password"
End If
End Sub

pbaldy · Feb 11, 2009

I would expect a user ID in the criteria, not the password field. In other words, you want to say "Find me the password for the user = the user on the form". Thus I would expect:

If DLookup("password", "tblEntry", "EmployeeID = " & Forms!frmDateSelector.ControlWithTheID) = Forms!frmDateSelector!Text7 Then

RachelKenyon · Feb 11, 2009

Got a big fat Data mismatch error.

Also it doesn't like the line.
DoCmd.OpenReport rptTotal1047221, acViewPreview,,,acWindowNormal
wants to debug, but when I follow the instructions for the line it STILL doesn't like it.

pbaldy · Feb 11, 2009

If the ID is text it would have to be:

DLookup("password", "tblEntry", "EmployeeID = '" & Forms!frmDateSelector.ControlWithTheID & "'")

The instructions should have said the report name had to be a string:

DoCmd.OpenReport "rptTotal1047221", acViewPreview,,,acWindowNormal

Need macro to run if 2 form fields are correct (1 Viewer)

RachelKenyon

ACCESS newbie

pbaldy

Wino Moderator

RachelKenyon

ACCESS newbie

RachelKenyon

ACCESS newbie

pbaldy

Wino Moderator

RachelKenyon

ACCESS newbie

pbaldy

Wino Moderator

RachelKenyon

ACCESS newbie

RachelKenyon

ACCESS newbie

pbaldy

Wino Moderator

RachelKenyon

ACCESS newbie

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread