Too few Parameter

[aadebayo]

Hello
I am having the following problem with my code. When it gets to the bit highlighted in red, it gives the following message.
Too few parameters, Expected 20

Private Sub create_journal1C() ' journal1 - free pupil meals
Dim Count As Integer, client_school_cc As String, meal_price As Double
Dim post_year As String
line_no = 1
Count = 0
batch_no = 0        'journal header details
journal_no = "J1C"
j_date = Now
post_year = IIf(Month(Get_Date) < 4, Right(year(Get_Date) - 1, 2), Format(Get_Date, "yy"))
Call journal_header(batch_no, journal_no, j_date, post_year)

Dim dbs As DAO.Database, rst As DAO.Recordset, intI As Integer
Dim strNumber As String, strBookmark As String
Dim TableSize As Integer, I As Integer, CountList As Integer, match As Boolean
Dim CodeList() As String
Dim rstOrders As DAO.Recordset
Dim lngTotal As Long, no_free_meals As Long, mealtype As String
Dim rstQuery As DAO.Recordset
Dim Select_Query As String
Dim cnn As New ADODB.Connection
Set cnn = CurrentProject.Connection
Set dbs = CurrentDb
'rst.Open "tbdiningcentre", cnn, adOpenDynamic, adLockPessimistic
Set rst = dbs.OpenRecordset("tbdiningcentre") ' Return Database variable pointing to current database.
rst.MoveLast
rst.MoveFirst
TableSize = rst.RecordCount
'''''loop through for each dining centre in table
' problem is, don't want to write two lines where the cost code are the same, so need to keep
' a track of what cost codes have been processed, and check each new one against list
ReDim CodeList(TableSize)

Do While Not rst.EOF
    For I = 1 To CountList
        If CodeList(I) = rst("COST-CENTRE") And I <> 1 Then 'match found
            match = True
            Exit For
        Else: match = False
        End If
    Next I
    CountList = CountList + 1
    CodeList(CountList) = rst("COST-CENTRE")
    Call glrstatus("Processing Cost Centre " & rst("COST-CENTRE"), "Please Wait")
    If IsNull(rst("CLIENT_SCHOOL_MEALS-CC")) Or match = True Then
        rst.MoveNext
    Else
        client_school_cc = rst("CLIENT_SCHOOL_MEALS-CC")
        '''''''get number of price bandings for journal lines'''''''''''''
        Set rstOrders = dbs.OpenRecordset("tbmealprice")
        rstOrders.MoveLast
        rstOrders.MoveFirst
        Do While Not rstOrders.EOF
            mealtype = rstOrders("MEAL-TYPE-CODE")
            meal_price = rstOrders("MEAL-SERVICE-PRICE")
            Select_Query = "SELECT DINING-CENTRE-CODE, MEAL-TYPE-CODE, MEAL-TYPE-NAME, WEEK-ENDING-DATE, WEEK-ACTUAL-NO, SCHOOL-COST-CENTRE, FUEL-RECHARGE-RATE, MEAL-TYPE-PRICE, MEAL-SERVICE-PRICE, CLIENT_SCHOOL_MEALS-CC, COST-CENTRE, JournalDate FROM qrymealsales WHERE MEAL-TYPE-CODE = '" & mealtype & "' AND MEAL-SERVICE-PRICE = " & meal_price & " AND CLIENT_SCHOOL_MEALS-CC = '" & client_school_cc & "' AND MEAL-TYPE-NAME Like '*PUPIL FREE*' AND week-ending-date= #" & Format(Get_Date, "mm/dd/yyyy") & "# GROUP BY DINING-CENTRE-CODE, MEAL-TYPE-CODE, MEAL-TYPE-NAME, WEEK-ENDING-DATE, WEEK-ACTUAL-NO, SCHOOL-COST-CENTRE, FUEL-RECHARGE-RATE, MEAL-TYPE-PRICE, MEAL-SERVICE-PRICE, CLIENT_SCHOOL_MEALS-CC, COST-CENTRE, JournalDate"
[COLOR=Red][B]Set rstQuery = dbs.OpenRecordset(Select_Query)[/B][/COLOR]
            If rstQuery.EOF = True And rstQuery.BOF = True Then
                'no records have been selected
            Else
                no_free_meals = 0
                rstQuery.MoveLast
                rstQuery.MoveFirst
                Do While Not rstQuery.EOF
                    no_free_meals = no_free_meals + rstQuery("WEEK-ACTUAL-NO")
                    rstQuery.MoveNext
                Loop
                If no_free_meals = 0 Then   'if no sales, skip to next
                    'do nowt
                Else    'journal CREDIT line details
                    rstQuery.MoveFirst
                    batch_no = 0
                    journal_no = "J1C"
                    line_number = line_no
                    ref_number = "w/e " & Format(Journal_Date, "ddmmyy") 'week ending date to which data refers
                    ledger_code = client_school_cc & "/6965"
                    debit_amount = 0        'leave blank- this is a credit journal
                    credit_amount = Format(no_free_meals * meal_price, "####0.00")
                    analysis = no_free_meals & " meals"  'actual no. of free pupil school meals per price banding
                    user_data = 0
                    narrative = "£" & Format(meal_price, "####0.000") & " price " & LCase(Me![DINING-CENTRE-NAME]) 'price of meal & school name
                    original_account = mealtype
                    debit_units = " "
                    unit_of_measure = " "
                    journal_line
                    line_no = line_no + 1
                End If
            End If
            rstOrders.MoveNext
        Loop
        rst.MoveNext
    End If
Loop
End Sub

 

[Mile-O]

Firstly, when posting long pieces of code can you ensure you use the CODE tags?

If you have parameters in the query then you can't just open the recrodset as you need to supply the parameters.
To get round this you use the QueryDef and its Parameters property.

There are examples throughout the site if you search on your error.