Access 2007 VBA Compile Error.

[LeslyeE]

Hi All,

I am creating a user form in Access 2007. I am receiving a Compile Error within my VBA coding. Please take a look at the code below and see if you can help.

Compile Error: Expected: End of statment

Recived on line:

DoCmd.OpenForm “frmPasswordChange”, , , “[UserID]" = ” & Me.cboUser

Within the VBA code:

Private Sub txtPassword_AfterUpdate()If IsNull(Me.cboUser) Then
    MsgBox "Please Enter your SSO to login to the database.", vbInformation, "SSO Required"
Me.cboUser.SetFocus
Else
If Me.txtPassword = Me.cboUser.Column(2) Then
If Me.cboUser.Column(4) Then
 DoCmd.OpenForm “frmPasswordChange”, , , “[UserID]" = ” & Me.cboUser
End If
DoCmd.OpenForm “frmMainMenu”
Me.Visible = False
Else
    MsgBox "Password does not match, please re-enter.", vboOkOnly + vbExclamation, "Password Required"
Me.txtPassword = Null
Me.txtPassword.SetFocus
End If
End If
End Sub
End Sub

 

[mrmmickle1]

It's easier to read code when it's indented....

It looks like you may have had an extra End Sub Statement as well as two different types of Double Quotes..... Try this code instead:

[COLOR=#0000ff]Private Sub[/COLOR] txtPassword_AfterUpdate()
  [COLOR=#0000ff]  If[/COLOR] IsNull(Me.cboUser) [COLOR=#0000ff]Then[/COLOR]
        MsgBox "Please Enter your SSO to login to the database.", vbInformation, "SSO Required"
    Me.cboUser.SetFocus
[COLOR=#0000ff]    Else[/COLOR]
   [COLOR=#0000ff]     If [/COLOR]Me.txtPassword = Me.cboUser.Column(2) [COLOR=#0000ff]Then[/COLOR]
           [COLOR=#0000ff] If[/COLOR] Me.cboUser.Column(4)[COLOR=#0000ff] Then[/COLOR]
                DoCmd.OpenForm “frmPasswordChange”, , , "[UserID]" = ” & Me.cboUser
[COLOR=#0000ff]            End If[/COLOR]
            DoCmd.OpenForm “frmMainMenu”
            Me.Visible =[COLOR=#0000ff] False[/COLOR]
[COLOR=#0000ff]        Else[/COLOR]
            MsgBox "Password does not match, please re-enter.", vboOkOnly + vbExclamation, "Password Required"
            Me.txtPassword =[COLOR=#0000ff] Null[/COLOR]
            Me.txtPassword.SetFocus
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]
[COLOR=#008000]'End Sub <----Remove this line[/COLOR]

 

[xenou]

THe quotes do look suspicious, BTW. Make sure you are not writing code or copying it in MS Word. You must use a text editor with plain Ascii characters for those quotes.

 

[LeslyeE]

Thank you for your help. I am having one more issue now. The user has to hit the "Enter" key to submit the password. How can I assign a Command button to run this instead?

 

[LeslyeE]

I am also receiving this error (I have created a form with that form name. I do not know why I am reciving this error when running the correct password.)

Run Time Error '2492':
The action or method requires a form name argument.

on this line of code:

DoCmd.OpenForm “frmMainMenu”

within this updated script:

Private Sub txtPassword_AfterUpdate()    If IsNull(Me.cboUser) Then
        MsgBox "Please Enter your SSO to login to the database.", vbInformation, "SSO Required"
    Me.cboUser.SetFocus
    Else
        If Me.txtPassword = Me.cboUser.Column(2) Then
            If Me.cboUser.Column(3) Then
                DoCmd.OpenForm “frmPasswordChange”, , , "[UserID]" = ” & Me.cboUser
            End If
            DoCmd.OpenForm “frmMainMenu”
            Me.Visible = False
        Else
            MsgBox "Password does not match, please re-enter.", vboOkOnly + vbExclamation, "Password Required"
            Me.txtPassword = Null
            Me.txtPassword.SetFocus
        End If
    End If
End Sub

 

[Norie]

You have strange looking quotes - “, they should be standard quotes - ".

As for the command button, just add one to the form and put the code you have in the textbox's AfterUpdate event into the command button's Click event.

 

[LeslyeE]

Thank you Norie,
I wrote the code in Access VBA. I used the standard " key when writing. I am not sure how they were converted. I did not copy paste or write in any other application or form.

As far as the command button, after I submitted that question I realized what the answer was. I am working on multiple projects so it just slipped my mind for a moment.. One of those Duh moments.. lol.. Thank you for the advice though. I posted one additional question that may be the same obvious answer but I am still stuck on this one.

 

[LeslyeE]

To Sum the two issues I am trying to find a resolution to now
1. Receiving an error "Run Time Error '2492'(displayed above)
2. Userform (code listed above) does not clear after OK is clicked or close.

I am trying to google the answer but no code I find works with the VBA listed above.

 

[Norie]

1 Stupid question perhaps, but have you checked the form name?

2 Not sure what you mean by 'clear', do you want the form to close?

 DoCmd.Close acForm, Me.Name