Results 1 to 13 of 13
  1. #1
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62

    Run-Time error 3061: Too few parameters. Expected 1

    Hello All,

    I am currently getting a run-time error stating "too few parameters. Expected 1" when I run my NewUserForm. It happens when I click Add New User button. It is happening at my INSERT and SELECT statement. Here is the code if you all could look at it. If you need more please let me know.

    Thank you



    Code:
    Private Sub cmdAdd_Click()
    
    
    strUserName = Me.txtUserName
    strLastName = Me.txtLast
    strFirstName = Me.txtFirst
    blnChangePWD = IIf(Me.cboChangePWD = "Yes", -1, 0)
    intUserType_ID = Nz(Me.cboUserRole, 1)
    dtePwdDate = Date
    
    
     'add new user
        If dtePwdDate <> 0 Then
        CurrentDb.Execute "INSERT INTO tblUserPass ( UserName, LastName, FirstName, Active, PWD, ChangePWD, UserType_ID, PWDDate)" & _
                " SELECT '" & strUserName & "' AS UserName, True AS Active, '" & SetDefaultPwd() & "' AS PWD," & _
                    " " & strFirstName & " AS FirstName," & strLastName & " AS LastName," & blnChangePWD & " AS ChangePWD, " & _
                    " " & intUserType_ID & " AS UserType_ID, " & dtePwdDate & " AS PWDDate;"
                    
                          
        Else 'omit PWDDate
        CurrentDb.Execute "INSERT INTO tblUserPass ( UserName, LastName, FirstName, Active, PWD, ChangePWD, UserType_ID)" & _
                " SELECT '" & strUserName & "' AS UserName, True AS Active, '" & SetDefaultPwd() & "' AS PWD," & strLastName & " AS LastName," & strFirstName & " AS FirstName," & SetDefaultPwd() & "' AS PWD," & _
                    " " & blnChangePWD & " AS ChangePWD, " & intUserType_ID & " AS UserTypeID;"
        End If
        
        Me.lblInfo.Caption = "New user " & Me.txtUserName & " has been successfully added" & vbCrLf & _
            "A default password 'Not set' has been added" & vbCrLf & _
            Me.txtUserName & " will be required to enter a new password at first login"
            
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Missing apostrophe delimiters for text parameters and # for date.
    Get fields and values in same order. Alias names not needed.
    Use VALUES() instead of SELECT.

    Could eliminate the If Then Else with an IIf so there is only 1 SQL.

    Code:
        CurrentDb.Execute "INSERT INTO tblUserPass(UserName, LastName, FirstName, Active, PWD, ChangePWD, UserType_ID, PWDDate)" & _
                " VALUES('" & strUserName & "','"  & strLastName & "','" & strFirstName & "',True,'" & SetDefaultPwd() & "'," & blnChangePWD & "," & _
                intUserType_ID & "," & IIf(dtePwdDate <> 0, "#" & dtePwdDate & "#", Null) & ")"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62
    thank you for the response and I copied it into my code. I am now getting another runtime error stating syntax error (missing operator) in query expression ",True,'...#&DE',True,1,#10/21/2019#)'. with "#&DE" being the PWD

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I may have edited my post after you read it. Look again. As far as I can see, syntax is now correct. Assuming Active and ChangePWD are both Yes/No type fields.

    Why do you show 3 dots '...#&DE'?

    Why do you need a function for a default password? Why not just hard code the string? Especially since message tells user default password is 'Not set' which is a very odd password.

    A debugging technique is to use a string variable for the SQL statement and Debug.Print so you can view the fully compiled SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62
    Quote Originally Posted by June7 View Post
    I may have edited my post after you read it. Look again. As far as I can see, syntax is now correct. Assuming Active and ChangePWD are both Yes/No type fields.

    Why do you show 3 dots '...#&DE'?

    Why do you need a function for a default password? Why not just hard code the string? Especially since message tells user default password is 'Not set' which is a very odd password.

    If you want to use current date, why bother with variable?

    A debugging technique is to use a string variable for the SQL statement and Debug.Print so you can view the fully compiled SQL statement.

    Awesome! I rechecked your post and fixed my code. It works now . Were would I hard code a password and ensure that it gets placed into the field? I can remove the function just need to know were to place the hard code. Thank you so much for your help.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Actually, if you set variable to current date then when will it ever be = 0? This If condition makes no sense.


    Just concatenate whatever text you want for password:

    & "True,'NewPassword'," &


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62
    Quote Originally Posted by June7 View Post
    Actually, if you set variable to current date then when will it ever be = 0? This If condition makes no sense.


    Just concatenate whatever text you want for password:

    & "True,'NewPassword'," &


    Okay I removed the IF statement to clean it up. I see what you mean. I will start working on the password section soon. I ran into another issue when trying to log in from the login screen. When I select the user name I have code in the AfterUpdate. It is presenting me with an error 94 in cboUserName_AfterUpdate procedure: Invalid Use of Null. When I run debug it doesn't show me anything. Here is the code:

    Code:
    Private Sub cboUserName_AfterUpdate()
    
    
    On Error GoTo Err_Handler
    
    
    Me.LblCurtPWD.Caption = "Password:"
    Me.TxtCurtPWD = ""
    
    
    '1. Check if user logged in elsewhere
     Dim strCriteria As String
    strCriteria = "UserName='" & Me.cboUserName & "' And Logoutevent Is Null"
    
    
    If DCount("*", "tblLoginSessions", strCriteria) > 0 Then
        If DLookup("ComputerName", "tblLoginSessions", strCriteria) <> GetComputerName Then
            'user logged in on another computer
            FormattedMsgBox "User " & Me.cboUserName & " is already logged in at workstation " & DLookup("ComputerName", "tblLoginSessions", strCriteria) & "      " & _
                "@User " & Me.cboUserName & " MUST logout from that computer before logging in again            @", vbCritical, "Already logged in"
            Me.btnLogin.Enabled = False
            cboUserName = ""
            Exit Sub
        Else
            'end previous session for this user on current computer so a new session can be started
            CurrentDb.Execute "UPDATE tblLoginSessions SET LogoutEvent = Now()" & _
                " WHERE UserName=GetUserName() AND LogoutEvent Is Null AND ComputerName=GetComputerName();"
        End If
    End If
    
    
    '2.Get user info
    If Trim(Me.cboUserName & "") <> "" Then
        strUserName = Me.cboUserName 'user name from combo
        strComputerName = GetComputerName()
        strPassword = RC4(Me.cboUserName.Column(2), "RC4_Key")
        blnChangeOwnPassword = Me.cboUserName.Column(3)
        intUserType_ID = Me.cboUserName.Column(5)
    End If
    '3. Does the user have a current password
    If RC4(Me.cboUserName.Column(2), "RC4_Key") = "Not Set" Then
        bFlag = False
        FormattedMsgBox "You have not set a login password yet.         " & _
            "@You must setup a password before you can access the application.          @", vbExclamation + vbOKOnly, "Setup Login Password"
        
        Me.TxtCurtPWD.Visible = False
        Me.TxtNewPWD.Visible = True
        Me.TxtConPWD.Visible = True
        Me.TxtNewPWD.SetFocus
        Me.lblNew.Caption = "New Password:"
        Me.lblCon.Caption = "Confirm Password:"
        Exit Sub
    Else
        bFlag = True
        Me.TxtCurtPWD.Visible = True
        Me.TxtNewPWD.Visible = False
        Me.TxtConPWD.Visible = False
    End If
    
    
    '4. Which control to go to
    If bFlag = False Then
        Me.TxtNewPWD.SetFocus
    Else
        Me.TxtCurtPWD.SetFocus
    End If
    
    
    Exit_Handler:
        Exit Sub
        
    Err_Handler:
        MsgBox "Error " & Err.Number & " in cboUserName_AfterUpdate procedure: " & Err.Description
        Resume Exit_Handler

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't know why cboUserName would be null after selection. But don't allow code to proceed if it is Null.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62
    Quote Originally Posted by June7 View Post
    I don't know why cboUserName would be null after selection. But don't allow code to proceed if it is Null.
    Thats what I cant figure out. When I select the username and click off of it, that is when it throws the error of "error 94 in cboUserName_AfterUpdate procedure: Invalid Use of Null"

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Which line actually triggers error? Disable the On Error GoTo and step debug.


    If cboUserName is null (user deleted entry), don't allow code to proceed if it is Null.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62
    Quote Originally Posted by June7 View Post
    Which line actually triggers error? Disable the On Error GoTo and step debug.

    If cboUserName is null (user deleted entry),
    don't allow code to proceed if it is Null.

    Okay it looks like it the error is coming from this:

    Code:
    strPassword = RC4(Me.cboUserName.Column(2), "RC4_Key")

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    RC4 is a custom function. What is its code? What is in Column(2)?

    Apparently function is returning Null - why? Did you step debug through the function? What would you expect it to return?

    String variables cannot hold Null. Only Variant type can. This would cause "Invalid Use of Null" error.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62
    Quote Originally Posted by June7 View Post
    RC4 is a custom function. What is its code? What is in Column(2)?

    Apparently function is returning Null - why? Did you step debug through the function? What would you expect it to return?

    String variables cannot hold Null. Only Variant type can. This would cause "Invalid Use of Null" error.
    I fixed it haha wow I am such a dummy!! It was how I had the combo box set up. When I placed the column numbers in the code, I didn't have them setup in the on the properties in the combo box

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Run-Time Error '3061: Too few parameters. Expected 2.
    By Rickochezz in forum Import/Export Data
    Replies: 1
    Last Post: 11-01-2016, 07:29 AM
  2. Replies: 4
    Last Post: 09-28-2016, 07:13 AM
  3. Replies: 5
    Last Post: 03-27-2015, 03:42 PM
  4. Replies: 3
    Last Post: 04-26-2013, 01:37 PM
  5. Replies: 1
    Last Post: 05-21-2011, 01:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums