Run-time error 3075 when using login code 1

[valgore]

im trying to make a log in form, and i keep getting "Run-time error '3075': Syntax error (missing operator) in query expression 'UCase(trim(User Name))='". i have made a table called UNP. it has two fields: User Name and Password. Im using Access 2007. here is my code

Private Sub Command12_Click()
'check for null User
    If IsNull(Trim(txtUser)) Then
        MsgBox "User Name required.", vbExclamation
        txtUser.SetFocus
        Exit Sub
    End If

    
    'check for null password
    If IsNull(Trim(txtPassword)) Then
        MsgBox "Password required.", vbExclamation
        txtPassword.SetFocus
        Exit Sub
    End If
    
    Dim tempRecordSet As Recordset, Password As String
    
    Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(User Name)) = '" & UCase(Trim(txtUser)) & "'")
    
        If tempRecordSet.RecordCount <> 0 Then
        Password = UCase(Trim(tempRecordSet("UNP.Password")))
    End If
    
      tempRecordSet.Close
    Set tempRecordSet = Nothing
    
    If Password <> UCase(Trim(txtPassword)) Then
       MsgBox "Access Denied", vbExclamation
    Else
         MsgBox "Access Granted", vbExclamation
    End If
    txtPassword = Empty
End Sub

The code works up until
Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(User Name)) = '" & UCase(Trim(txtUser)) & "'")

Can anyone help?

The second question i have is when i view my logon form in form view, and i select my user name from a drop down list, it inserts my password in the password box by itself. how do i stop this?

Thanks,
Valgore

 

[HarleyQuinn]

For your first question try:

Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim([red][[/red]User Name[red]][/red])) = '" & UCase(Trim(txtUser)) & "'")

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[valgore]

wow that solved it! thank you so much!

anyone else help on question 2?

 

[HarleyQuinn]

I imagine that you've got the password box bound the the password field in your DB, you would just have to unbind it (assuming there's nothing in the code behind the dropdown list when a selection is made that would affect it).

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[valgore]

it seems like i spoke too soon. it looks like as long as the username and password box has something in it, you gain access. i think it has to do with the first part of the code. i realized i didnt have a code put in that said if there is a password inputed then check to see if its right and same for username. can you help me with that part?



thanks, Valgore

 

[HarleyQuinn]

Hmm, worked fine for me and the only change I made was to replace

If tempRecordSet.RecordCount <> 0 Then
with
If Not tempRecordSet.BOF And Not tempRecordSet.EOF Then

Not sure why it's not working for you though...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[valgore]

so you changed

  If tempRecordSet.RecordCount <> 0 Then
        Password = UCase(Trim(tempRecordSet("UNP.Password")))
    End If

to

If tempRecordSet.RecordCount <> 0 Then
with
If Not tempRecordSet.BOF And Not tempRecordSet.EOF Then
?

 

[PHV]

Why not simply something like this ?

Private Sub Command12_Click()
'check for null or ZLS or blank User
If Trim(Me!txtUser & "") = "" Then
  MsgBox "User Name required.", vbExclamation
  Me!txtUser.SetFocus
  Exit Sub
End If
'check for null or ZLS or blank password
If Trim(Me!txtPassword & "") = "" Then
  MsgBox "Password required.", vbExclamation
  Me!txtPassword.SetFocus
  Exit Sub
End If
If DCount("*", "UNP", "[User Name]='" & Me!txtUser & "' AND [Password]='" & Me!txtPassword & "'") = 0 Then
  MsgBox "Access Denied", vbExclamation
Else
  'your protected code here
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

so what does the Me! do for the txtUser and txtPassword? when i tried it, i got a popup saying Run-Time error '2465':
Microsoft Office Access can't find the field 'txtUser' referred to in your expression. i like the simpler look to it PHV. it's really only for a few user. the only reason im doing a login is to be able to give specific users specific forms. so i dont need it to be really complicated.

Valgore

 

[PHV]

Sorry, I guessed that txtUser and txtPassword was controls in the same form than the Command12 button.
Yoy may surely get rid of all the Me!

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

ok. well i took out the Me! and the code is

Private Sub Command12_Click()
'check for null or ZLS or blank User
If Trim("Form_Logon Screen.txtUser" & "") = "" Then
  MsgBox "User Name required.", vbExclamation
  txtUser.SetFocus
  Exit Sub
End If
'check for null or ZLS or blank password
If Trim("Form_Logon Screen.txtPassword" & "") = "" Then
  MsgBox "Password required.", vbExclamation
  txtPassword.SetFocus
  Exit Sub
End If
If DCount("*", "UNP", "[User Name]='" & "Form_Logon Screen.txtUser" & "' AND [Password]='" & "Form_Logon Screen.txtPassword" & "'") = 0 Then
  MsgBox "Access Denied", vbExclamation
Else
  'your protected code here
  MsgBox "Access Granted!", vbExclamation
 
End If
End Sub

no matter what i put in for username and password, it says Access Denied. is that because i didnt put the next line of code in? also, this may sound stupid, but what do i put in for the "your protected code here" spot? sorry. this is my first time using VBA and ive been trying to get into a college class for coding but haven't had the time.

Thanks,
Valgore

 

[PHV]

What is Form_Logon Screen ???

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

well thats the form that i use for the logon screen. i put that there instead of the Me! because without it, it says user name required even if i havea username selected. same with password

 

[PHV]

it says user name required
And the focus goes to the right control ?

BTW, where is located the Command12 button ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

ok so i have a drop down box to select my username. i select it, type in a password, and press the go button. the Command12 button is the go button on the form. when i press go, i get "user Name required". i press ok, and another dialogue box comes up saying "run-time error 424 object required". so does that mean that it doesnt know where to look for the username?

 

[PHV]

And your original code didn't raise this error ?

 

[valgore]

you mean the first code i posted? the only thing wrong with that was that the variable didnt work. so as long as there was something in the username and password box, you gained full access. so i think the part of the code that didnt work was the verification part to check if the username and password matched to the UNP table. so no, it didnt raise the error.

 

[PHV]

What are the real names of your controls in the form ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

sorry, what do you mean by controls?

 

[valgore]

im pretty sure i know where the code is failing. i think it's failing at

 Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(UserName)) = '" & UCase(Trim(txtUser)) & "'")

because the next line recieves the password IF the username and txtuser match.