AND operator help

[maxxximaniac]

Good day,

I am comming across a small problem that I am leading to think is the incorrect way to use the AND operator, although not sure.

I have a login form with Username/Password/Unit fields.
When the user Chooses their user name, Their Unit is populated by a hidden field.

I have a code on a login button, where if the passwords match against a password table, then open a form based on the Unit.

[COLOR="Navy"] If [/COLOR]Me.PasswordInput <> Me.Password [COLOR="navy"]Then [/COLOR]MsgBox "User/Password Incorrect!", vbCritical, "Error!"

 [COLOR="navy"]If [/COLOR]Me.PasswordInput = Me.Password [COLOR="navy"]And [/COLOR]Me.Unit = "Batching" [COLOR="navy"]Then [/COLOR]DoCmd.OpenForm "frm_EnterProductionBatching", acNormal
 
 [COLOR="navy"]If [/COLOR]Me.PasswordInput = Me.Password [COLOR="navy"]And [/COLOR]Me.Unit = "Exceptions" [COLOR="navy"]Then [/COLOR]DoCmd.OpenForm stDocName, , , stLinkCriteria

I had this line before which excluded the unit field and it works fine.

 [COLOR="navy"]If [/COLOR]Me.PasswordInput = Me.Password [COLOR="navy"]Then [/COLOR]DoCmd.OpenForm stDocName, , , stLinkCriteria

Thank you!

 

[ghudson]

If you are really using that code as is, you are not stopping the code if the answer is false. Also, Password is a reserved word. You need to use a naming convention and prefeix your objects with a txt for text boxes, like txtPassword. That will also let Access know that you are referring to the value of the text box and not the field name from your table.

If txtPasswordInput <> txtPassword Then
    MsgBox "User/Password Incorrect!", vbCritical, "Error!"
    Exit Sub
End If

If txtPasswordInput = txtPassword And txtUnit = "Batching" Then
    DoCmd.OpenForm "frm_EnterProductionBatching", acNormal
ElseIf txtPasswordInput = txtPassword And txtUnit = "Exceptions" Then
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

 

[Pat Hartman]

Are you sure that unit is a text field? Could the stored value actually be a numeric ID? If you are using a lookup field defined in the table definition, this can easily cause this type of confusion. I recommend not using lookups defined at the table level. Use combos on your forms and allow the REAL numeric value to show in the table and queries.

 

[maxxximaniac]

Thank you both,

I went ahead and changed the naming of the text fields, and yes, the field's date type in the table is text.

Now I get the messagebox error even when the password is correct.

I attached the database below... The form for the login is frm_Login. The subform is frm_Loginsub which loads the password field onto the form...

Password is 1234 and it is housed in tbl_Employees.

Thank you!

If Me.txt_PasswordInput <> txt_Password Then MsgBox "User/Password Incorrect!", vbCritical, "Error!"
 If Me.txt_PasswordInput = txt_Password Then DoCmd.OpenForm stDocName, , , stLinkCriteria
 If Me.txt_PasswordInput = txt_Password Then DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[nateobot]

Try this:

 If Me.txt_PasswordInput <> Me!frm_LoginSubform.Form!txt_Password Then MsgBox "User/Password Incorrect!", vbCritical, "Error!"
 If Me.txt_PasswordInput = Me!frm_LoginSubform.Form!txt_Password Then DoCmd.OpenForm stDocName, , , stLinkCriteria
 If Me.txt_PasswordInput = Me!frm_LoginSubform.Form!txt_Password Then DoCmd.OpenForm stDocName, , , stLinkCriteria

You weren't referencing the control on the subform.

 

[maxxximaniac]

Nice! Thank you!

I tried using me.txt_Password or Forms!frm_LoginSubform!txt_Password but didn't have the code down to the dime..