Referencing a value in a table

[sstasiak]

I have a table that has a field, [Pchange], with a "Yes/No" value. If this field is "Yes", then when a user tries to log into the DB, they are prompted to change their password. If it's "No", then the main form opens.

What is the VBA syntax to reference this field?

The first "If" statement will look something like this I think:

If DLookup("Pchange", "tblEmployee", "[Employee]= """ & Text0 & """") = "Yes"

Should the "Yes" above be a "Yes" or a different value?

 

[DAW]

yes should be -1, no is 0

 

[sstasiak]

Excellent, it works now.

One thing I forgot to ask about is how the syntax to change that field to a 0 would look.

 

[DAW]

This will give you the gist - you'll neet to disect it a bit to suit of course...

SQL = "UPDATE tbl_Misc SET [iValue] = '" & myValue & "' WHERE [Identifier] = 'FilesProcessed';"
CurrentDb.Execute SQL

 

[DAW]

Sorry - trying to be too quick in my last post...

SQL = "UPDATE tbl_Misc SET [iValue] = 0 WHERE [Identifier] = 'xx';"
CurrentDb.Execute SQL

 

[sstasiak]

Does this look like it would work:

SQL = "UPDATE tblEmployee SET [Pchange] = '" & 0 & "' WHERE [Employee] = 'Text0';"
CurrentDb.Execute SQL

tblEmployee contains fields:
[Pchange] - Yes or No value determining if user needs to change password
[Employee] - stores the username

Text0 is the text box where the username is input in the form.
0 is what I want the [Pchange] field changed to after the password is reset.

 

[DAW]

sstasiak,
You are setting the value as a string - you don't need the single quotes as the field (Yes/No) is numeric.

 

[sstasiak]

DAW

It doesn't seem to be working. I now have it like this:

SQL = "UPDATE tblEmployee SET [Pchange] = 0 WHERE [Employee] = 'Text2';"
CurrentDb.Execute SQL

Should the Text2 be Me.Text2.Value?

 

[Moniker]

In addition to not passing the True/False value as a string, the reference to Text0 needs to be a variable, not a literal, and there's no need for a separate SQL variable.

CurrentDb.Execute "UPDATE tblEmployee SET [Pchange] = 0 WHERE [Employee] = '" & Text0 & "';"

 

[sstasiak]

This is what finally works:

SQL = "UPDATE tblEmployee SET [PassChange] = 0  WHERE [Employee] = '" & Text0 & "';"
CurrentDb.Execute SQL

I needed the single and double quotes around Text0.

Can someone explain what the quotes signify, because I'm not exactly sure?

Thanks for all the help.

 

[sstasiak]

So I had everything working and then realized I had another issue.

The only thing that doesn't work now is when a user enters the wrong password and clicks "Submit", nothing happens. I'm almost positive it's an issue with how I have the If statements coded.

Here's the code for the button:

Private Sub Command4_Click()

If DLookup("PassChange", "tblEmployee", "[Employee]= """ & Text0 & """") = -1 Then
MsgBox "You are either a new user, or your password needs to be reset."
DoCmd.OpenForm "PassChange"
Else

If Me.Text2.Value = DLookup("Password", "tblEmployee", "[Employee]= """ & Text0 & """") Then
If DLookup("Admin", "tblEmployee", "[Employee]= """ & Text0 & """") = -1 Then
DoCmd.OpenForm "AdminMainMenu"
Else  [COLOR="Red"]***these two if conditions both need to be true...if the password matches, but there is no 
"Admin" option checked, this code should be ignored and the code below should take over.  
I don't think that is happening now.  This only happens if an admin enters the wrong password.
If it's a regular user, it works fine***[/COLOR]

If Me.Text2.Value = DLookup("Password", "tblEmployee", "[Employee]= """ & Text0 & """") Then
DoCmd.OpenForm "MainMenu"
Else
MsgBox "Your username and/or password are incorrect.  Please try again."

End If
End If
End If
End If

End Sub

 

[sstasiak]

I tried flipping the if statements. That fixed the problem when an admin put in the wrong password, but now regular users get nothing. Somehow those two statements need to be more like: If(xxxx is true) AND (yyyy is true) then....