Access 2007 VBA Compile Error.

LeslyeE

New Member
Joined
Jun 17, 2014
Messages
20
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:
Code:
DoCmd.OpenForm “frmPasswordChange”, , , “[UserID]" = ” & Me.cboUser

Within the VBA code:
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
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:

Code:
[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]
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Jun 17, 2014
Messages
20
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

New Member
Joined
Jun 17, 2014
Messages
20

ADVERTISEMENT

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.)

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

on this line of code:
Code:
DoCmd.OpenForm “frmMainMenu”

within this updated script:
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(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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 17, 2014
Messages
20

ADVERTISEMENT

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

New Member
Joined
Jun 17, 2014
Messages
20
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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?
Code:
 DoCmd.Close acForm, Me.Name
 

Forum statistics

Threads
1,141,413
Messages
5,706,298
Members
421,440
Latest member
cmphares

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top