Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run-time error (Access 2003)

    Hoping that someone can help me with this. I have some code behind a command button, and am getting Run-time error '3061' Too few parameters. Expected 1. It seems to be hanging up at:

    CurrentDb.Execute sSQL

    Private Sub cmdAction_Click()
    If IsNumeric(Me.txtUserID.Value) = False Then
    MsgBox "The Assoc ID field requires only integer values. You cannot add/update a record until this is changed."
    Me.txtUserID.SetFocus
    Exit Sub
    End If

    If validated Then
    Select Case Me.cmdAction.Caption
    Case "&Add"
    sSQL = "INSERT INTO TBL_USERLOG (UserID, UserName, NmFirst, NmLast, ActiveDt, AccessLevel, Password) VALUES ("
    sSQL = sSQL & Me.txtUserID & ", '"
    sSQL = sSQL & Me.txtUsername & "', '"
    sSQL = sSQL & Me.txtFirstName & "', '"
    sSQL = sSQL & Me.txtLastName & "', '"
    sSQL = sSQL & Me.txtActiveDate & "', "
    sSQL = sSQL & Me.cboAccessLevel & ", 'howitzer1')"
    Case "&Update"
    sSQL = "UPDATE TBL_USERLOG SET username = '" & Me.txtUsername & "', "
    sSQL = sSQL & "NmFirst = '" & Me.txtFirstName & "', "
    sSQL = sSQL & "NmLast = '" & Me.txtLastName & "', "
    sSQL = sSQL & "InActiveDt = '" & Me.txtInactiveDate & "', "
    sSQL = sSQL & "AccessLevel = " & Me.cboAccessLevel
    sSQL = sSQL & " WHERE UserID = " & Me.lstUsers
    End Select
    Debug.Print sSQL
    CurrentDb.Execute sSQL
    Me.lstUsers.Requery
    Call cmdClear_Click
    Else
    MsgBox "You have not completed all the required fields", vbExclamation
    End If
    End Sub


    Thoughts

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run-time error (Access 2003)

    You have a debug.print line in there. Have you tried copying the SQL from the immediate window and pasting it into the SQL view of a new query to see what happens? Usually when you get that error it means that you have misspelled a field name, making the query engine think a parameter is expected. Since you're dealing with two different queries, you need to narrow it down to whether one or both are throwing this error. Trying to run the result of the SQL should give you some indication of the actual problem.
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run-time error (Access 2003)

    This error usually means that a field name is not recognized. Check all field names you are using VERY carefully. For example, in the "&Add" case, you set a field named ActiveDt, and in the "&Update" case, a field named InActiveDt. Is that correct?

    By the way, you treat ActiveDt and InActiveDt as if they are text fields. If they are date fields, you should assign date values, surrounded in # characters. (I also forced US date format; this is useful if your database can be used by someone using another system date setting)

    Private Sub cmdAction_Click()
    If IsNumeric(Me.txtUserID.Value) = False Then
    MsgBox "The Assoc ID field requires only integer values. You cannot add/update a record until this is changed."
    Me.txtUserID.SetFocus
    Exit Sub
    End If

    If validated Then
    Select Case Me.cmdAction.Caption
    Case "&Add"
    sSQL = "INSERT INTO TBL_USERLOG (UserID, UserName, NmFirst, NmLast, ActiveDt, AccessLevel, Password) VALUES ("
    sSQL = sSQL & Me.txtUserID & ", '"
    sSQL = sSQL & Me.txtUserName & "', '"
    sSQL = sSQL & Me.txtFirstName & "', '"
    sSQL = sSQL & Me.txtLastName & "', #"
    sSQL = sSQL & Format(Me.txtActiveDate, "mm/dd/yyyy") & "#, "
    sSQL = sSQL & Me.cboAccessLevel & ", 'howitzer1')"
    Case "&Update"
    sSQL = "UPDATE TBL_USERLOG SET username = '" & Me.txtUserName & "', "
    sSQL = sSQL & "NmFirst = '" & Me.txtFirstName & "', "
    sSQL = sSQL & "NmLast = '" & Me.txtLastName & "', "
    sSQL = sSQL & "InActiveDt = #" & Format(Me.txtInActiveDate, "mm/dd/yyyy") & "#, "
    sSQL = sSQL & "AccessLevel = " & Me.cboAccessLevel
    sSQL = sSQL & " WHERE UserID = " & Me.lstUsers
    End Select
    Debug.Print sSQL
    CurrentDb.Execute sSQL
    Me.lstUsers.Requery
    Call cmdClear_Click
    Else
    MsgBox "You have not completed all the required fields", vbExclamation
    End If
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •