Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

too few parameters error not understanding why??

Posted on 2017-11-14
4
Low Priority
?
28 Views
Last Modified: 2017-11-14
this is a access application, i have a method that gets all the selected values from a listbox and is delimited by a comma. i than take those values and pass it into a where clause and use the 'IN' operator on it but i am getting the too few parameters error.

my code.

DoCmd.SetWarnings False
SelectedList = GetSelectedItems(Me.WeekSelector)
Query = "INSERT INTO [WorkTable]([L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE], ID)" _
& " SELECT LoadTable.[L Batch ID], LoadTable.[Pay Group], LoadTable.[Pay Group Description], LoadTable.[General Ledger Account], LoadTable.[General Ledger Cost Center], LoadTable.[General Ledger Department], LoadTable.[Work Center], LoadTable.[Pay Period Ending Date], LoadTable.Hours, LoadTable.Amount, LoadTable.Week, LoadTable.[Pay Type Code], LoadTable.[Pay Type Description], LoadTable.[File Number], LoadTable.Name, LoadTable.[HOURLY SALARY], LoadTable.[FULL TIME_PART TIME], LoadTable.ACTIVE_INACTIVE, LoadTable.[HOURLY RATE], LoadTable.ID" _
& " FROM LoadTable " _
& " WHERE (((LoadTable.Week) IN (SelectedList)));"
CurrentDb.Execute (Query)
DoCmd.SetWarnings True

Open in new window




Public Function GetSelectedItems(lBox As Object) As String
'returns an array of selected items in a ListBox
Dim tmpArray() As Variant
Dim i As Integer
Dim selCount As Integer
    selCount = -1
    '## Iterate over each item in the ListBox control:
    For i = 0 To lBox.ListCount - 1
        '## Check to see if this item is selected:
        If lBox.Selected(i) = True Then
            '## If this item is selected, then add it to the array
            selCount = selCount + 1
            ReDim Preserve tmpArray(selCount)
            tmpArray(selCount) = lBox.ItemData(i)
            
        End If
    Next

    If selCount = -1 Then
        '## If no items were selected, return an empty string
        GetSelectedItems = "" ' or "No items selected", etc.
    Else:
        '## Otherwise, return the array of items as a string,
        '   delimited by commas
        GetSelectedItems = Join(tmpArray, ", ")
    End If
End Function

Open in new window

0
Comment
Question by:Aravind Ranganathan
4 Comments
 
LVL 59

Expert Comment

by:Bill Prew
ID: 42367630
What specific line are you getting the error on?


»bp
0
 

Author Comment

by:Aravind Ranganathan
ID: 42367633
@ Bill Prew line number 6 on the where condition.
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 42367641
You must concatenate the list:

& " WHERE (((LoadTable.Week) IN (" & SelectedList & ")));"

Open in new window

/gustav
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 500 total points
ID: 42367650
First off, get rid of the SetWarnings command.  My personal advice is to eliminate this command from your memory and never use it again.  You have already taken the next step, which is to use the Execute method when running action queries, and since Execute won't raise warnings, you really don't need those.

Now to your code:

SelectedList = GetSelectedItems(Me.WeekSelector)
Query = "INSERT INTO [WorkTable]([L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], " _
                  & "[General Ledger Cost Center], [General Ledger Department], [Work Center], " _
                  & "[Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], " _
                  & "[Pay Type Description], [File Number], Name, [HOURLY SALARY], " _
                  & "[FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE], ID)" _
       & " SELECT LoadTable.[L Batch ID], LoadTable.[Pay Group], LoadTable.[Pay Group Description], LoadTable.[General Ledger Account], " _
                    & "LoadTable.[General Ledger Cost Center], LoadTable.[General Ledger Department], LoadTable.[Work Center], " _
                    & "LoadTable.[Pay Period Ending Date], LoadTable.Hours, LoadTable.Amount, LoadTable.Week, LoadTable.[Pay Type Code], " _
                    & "LoadTable.[Pay Type Description], LoadTable.[File Number], LoadTable.Name, LoadTable.[HOURLY SALARY], " _
                    & "LoadTable.[FULL TIME_PART TIME], LoadTable.ACTIVE_INACTIVE, LoadTable.[HOURLY RATE], LoadTable.ID " _
       & "FROM LoadTable " _
       & "WHERE (((LoadTable.Week) IN (" & SelectedList & ")));"
CurrentDb.Execute (Query)

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question