RT Error 3061 : Too Few Parameters

[bballhermit]

I am trying to write a function to populate text boxes with hours based on filter combo boxes on the form. My function code is below. The line

Set rst = db.OpenRecordset("SELECT * FROM [tblAssignments] WHERE (([tblAssignments].[YearID]=[Forms]![frmEmployeeAssignment]![cmboYear]) AND ([tblAssignments].[ProjectID]=[Forms]![frmEmployeeAssignment]![cmboProject]) AND ([tblAssignments].[EmployeeID]=[Forms]![frmEmployeeAssignment]![cmboEmployee]))")

returns the runtime error stating that it needs 3 parameters. How can I fix this? Thanks.

'Requery Employee Assignment Hours
Private Sub hrsRequery()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Dim October As String
    Dim November As String
    Dim December As String
    Dim January As String
    Dim February As String
    Dim March As String
    Dim April As String
    Dim May As String
    Dim June As String
    Dim July As String
    Dim August As String
    Dim September As String
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM [tblAssignments] WHERE (([tblAssignments].[YearID]=[Forms]![frmEmployeeAssignment]![cmboYear]) AND ([tblAssignments].[ProjectID]=[Forms]![frmEmployeeAssignment]![cmboProject]) AND ([tblAssignments].[EmployeeID]=[Forms]![frmEmployeeAssignment]![cmboEmployee]))")
    October = rst.Fields("OctHrs")
    November = rst.Fields("NovHrs")
    December = rst.Fields("DecHrs")
    January = rst.Fields("JanHrs")
    February = rst.Fields("FebHrs")
    March = rst.Fields("MarHrs")
    April = rst.Fields("AprHrs")
    May = rst.Fields("MayHrs")
    June = rst.Fields("JunHrs")
    July = rst.Fields("JulHrs")
    August = rst.Fields("AugHrs")
    September = rst.Fields("SepHrs")
    rst.Close
    Set rst = Nothing
    Set db = Nothing

    Me.txtOct = October
    Me.txtNov = November
    Me.txtDec = December
    Me.txtJan = January
    Me.txtFeb = February
    Me.txtMar = March
    Me.txtApr = April
    Me.txtMay = May
    Me.txtJun = June
    Me.txtJul = July
    Me.txtAug = August
    Me.txtSep = September

End Sub

 

[pbaldy]

You have to concatenate the form references:

"...WHERE [tblAssignments].[YearID]=" & [Forms]![frmEmployeeAssignment]![cmboYear] & " AND..."

A tutorial here, including the different syntax for text and date values:

http://www.baldyweb.com/BuildSQL.htm

 

[bballhermit]

Thanks for the tutorial! Hmm. Now, I get a datatype mismatch. Here is my code:

Set rst = db.OpenRecordset("SELECT * FROM [tblAssignments] WHERE ([tblAssignments].[YearID]=" & [Forms]![frmEmployeeAssignment]![cmboYear] & ") AND ([tblAssignments].[ProjectID]=" & [Forms]![frmEmployeeAssignment]![cmboProject] & ") AND ([tblAssignments].[EmployeeID]=" & [Forms]![frmEmployeeAssignment]![cmboEmployee] & ")")

Any ideas? Now, my combo boxes are all unbound as they are a filter system that are have a requery action anytime any of the combo boxes are updated. Would that affect it? If so, how could I fix it.

Thanks.

 

[pbaldy]

What are the data types of the 3 fields in the criteria? If any are text, they require quotes around the values, as detailed in the tutorial.

 

[vbaInet]

Just to help shorten your code:

Private Sub hrsRequery()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim dMonths(1 To 12) As String
    Dim i As Integer
    
    dMonths(1) = "Jan"
    dMonths(2) = "Feb"
    dMonths(3) = "Mar"
    dMonths(4) = "Apr"
    dMonths(5) = "May"
    dMonths(6) = "Jun"
    dMonths(7) = "Jul"
    dMonths(8) = "Aug"
    dMonths(9) = "Sep"
    dMonths(10) = "Oct"
    dMonths(11) = "Nov"
    dMonths(12) = "Dec"
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM [tblAssignments] " & _
                               "WHERE ([YearID] = " & Me.cmboYear & " AND [ProjectID] = " & Me.cmboProject & " AND [EmployeeID] = " & Me.cmboEmployee & ";")

    For i = LBound(dMonths) To UBound(dMonths)
        Me.Controls("txt" & dMonths(i)) = rst.Fields(dMonths(i) & "Hrs")
    Next

    rst.Close
    Set rst = Nothing
    Set db = Nothing
End Sub

You will still need to sort out the data type issue as pbaldy advised.

 

[bballhermit]

Thanks for the responses.

None of the three criteria are text, however, I do not believe. They are all autonumbers.

 

[boblarson]

You don't have lookups defined at table level do you? If so, that can cause problems with data type mismatches when it doesn't seem that it should. If you do, read this and remove them.

 

[bballhermit]

No, I do not have any lookups in my actual tables. I am querying from the form to pull in the table data. Any other ideas?

Thanks.

 

[pbaldy]

Double check the spelling of everything in the SQL: table and field names, form and control names.

 

[boblarson]

And also check to see if the combos are returning the values you think they should be returning. You can use

Debug.Print "YearID : " & cmboYear, "ProjectID: " & cmboProject, "EmployeeID: " & cmboEmployee

to see in the Immediate Window, when you run the code, what the values are.