Syntax/End Statement Error

[Robby19]

Good day everyone.

Used a code for a login form and it doesn't seem to be computing whatsoever.

Private Sub cmdContinue_Click()
 On Error GoTo ErrHandler
      Dim strSQL As String
      If Nz(Me.txtFirstName, "") = "" Then
       MsgBox ("First Name cannot be empty.")
       DoCmd.GoToControl "txtFirstName"
      Exit Sub
      End If
      If Nz(Me.txtLastName, "") = "" Then
       MsgBox ("Last Name cannot be empty.")
       DoCmd.GoToControl "txtLastName"
      Exit Sub
      End If
      If Nz(Me.txteMail, "") = "" Then
       MsgBox ("Email cannot be empty.")
       DoCmd.GoToControl "txteMail"
      Exit Sub
      End If
      DoCmd.SetWarnings False
      strSQL = "INSERT INTO tblUsers ( uNetworkID, uFirstName, uLastName, ueMail, uLastLogon, uLogonCount, uSecurityID, uActive )" & _
      " SELECT Environ("UserName"), '" & Me.txtFirstName & "', '" & Me.txtLastName & "', '" & Me.txteMail & "', Now(), 1, 9, True"
      DoCmd.RunSQL strSQL
       Forms(" frmMainMenu ").Tag = "Continue"    'frmMainMenu change to the name of your Main Menu/Switchboard/Navigation Form
      DoCmd.Close acForm, Me.Name
Complete:
  Exit Sub
ErrHandler:
   MsgBox ("Error creating user profile: " & Err.Description)
 End Sub

The following is highlighted in yellow

Private Sub cmdContinue_Click()

This is in red

strSQL = "INSERT INTO tblUsers ( uNetworkID, uFirstName, uLastName, ueMail, uLastLogon, uLogonCount, uSecurityID, uActive )" & _
" SELECT Environ("UserName"), '" & Me.txtFirstName & "', '" & Me.txtLastName & "', '" & Me.txteMail & "', Now(), 1, 9, True"

Any ideas?

 

[Robby19]

Fixed it. Replaced with

strSQL = "INSERT INTO tblUsers ( uNetworkID, uFirstName, uLastName, ueMail, uLastLogon, uLogonCount, uSecurityID, uActive )" & _ " Values ('" &  Environ("UserName") & "' , '" & Me.txtFirstName & "', '" & Me.txtLastName & "', '" & Me.txteMail & "', Now(), 1, 9, True"

Now I am getting the following error; "Error creating user profile: Syntax error in INSERT INTO statement."

 

[Norie]

There should be a closing parenthesis after True.

 

[Robby19]

Thank you!

Something else that is happening is that I am receiving the error

Compile error:
Sub or Function not defined

If IsDeveloper Then
ChangeProperty "AllowBypassKey", dbBoolean, True
Else
ChangeProperty "AllowBypassKey", dbBoolean, False
End If

The ChangeProperty code is highlighted.

 

[Robby19]

Here is the full code;

Private Sub Form_Load()
 On Error Resume Next
 Dim rst As DAO.Recordset
 Dim db As DAO.Database
 Dim myQuery As String
 myQuery = "SELECT * FROM tblUsers WHERE uNetworkID = '" & Environ("UserName") & "'"
 Set db = CurrentDb()
 Set rst = db.OpenRecordset(myQuery, dbOpenDynaset, dbSeeChanges)
      If Not rst.BOF And Not rst.EOF Then
        rst.Edit
        rst.Fields("uLogonCount") = rst.Fields("uLogonCount") + 1
        rst.Fields("uLastLogon") = Now()
        rst.Update
         Me.txtSecurityID = rst.Fields("uSecurityID")
         Me.txtOverride = rst.Fields("uSpecialPermissions")
         Me.txtUserID = rst.Fields("uUserID")
         Me.txtDelete = rst.Fields("uDelete")
         Me.txtPassword = rst.Fields("uPassword")
        DoEvents
      Else
        DoCmd.OpenForm "frmNewUser", acNormal, , , , acWindowNormal
        Me.Dirty = False
        Me.Visible = False
        Do Until Me.Tag = "Continue"
          DoEvents
        Loop
      End If
 Set rst = Nothing
 db.Close
 Set db = Nothing
           If IsDeveloper Then
              ChangeProperty "AllowBypassKey", DB_BOOLEAN, True
           Else
              ChangeProperty "AllowBypassKey", DB_BOOLEAN, False
           End If
Form_Load_Exit:
 Exit Sub
 End Sub

 

[Micron]

If you are still waiting for a response, I'd advise the following:
Access is looking for a procedure or function named ChangeProperty but can't find it. Either you're missing the code, or it doesn't have the proper scope, or it's in a code library db for which your project is missing a Reference to.

Be careful using Resume Next on an error unless you're absolutely sure you want whatever comes next to happen. In this case, probably don't use it at all but without seeing the code for the missing procedure, I can't say for sure. Reason being is that the AllowByPassKey property does not exist by default. Typically you attempt to set it, and if it doesn't exist, you append it the property to the properties collection of the db, then Resume (go back to the point that caused the error, i.e. where the attempt was made to set the property). Once appended, you can set it.

Not sure I would employ several Do Events; it can be a resources hog. Plus, when you fix the error (assuming you have not yet) I see the user form opening and any open or load code in it being run, then execution returning to your loop. Then you're in a continuous loop while Access looks at the Tag property, waiting for it to change, presumably via the other form. Seems a bit of a waste of resources. Maybe the 2nd form ought to be modal instead.

I'd set all objects to Nothing before closing the db (you are doing the reverse). Odd that you would close the db yet have calls to another function, all being outside the IF block above. Why would that code ever need to be run when you closed the db before it?

 

[Robby19]

Created the following module;

Function IsDeveloper() As Boolean

                   Dim UserName As String
                   IsDeveloper = False
                   UserName = VBA.Environ("Username")

                   Select Case UserName
                    Case "Your NetworkID goes here"
                      IsDeveloper = True
                    Case Else
                      IsDeveloper = False
                   End Select

              End Function

[FONT=Courier New]Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer

                     Dim dbs As Object, prp As Variant
                     Const conPropNotFoundError = 3270
                   
                     Set dbs = CurrentDb
                     On Error GoTo Change_Err
                     dbs.Properties(strPropName) = varPropValue
                     ChangeProperty = True 
                  Change_Bye:
                     Exit Function 
                  Change_Err:
                     If Err = conPropNotFoundError Then ' Property not found.
                         Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
                         dbs.Properties.Append prp
                         Resume Next
                     Else
                         ' Unknown error.
                         ChangeProperty = False
                         Resume Change_Bye
                     End If

                  End Function [/FONT]

This got rid of the error and allows the user to not be required to log-in assuming they have previously created an account.