Error 3061: Too Few Parameters

[Steve887]

I'm running a loop on a listbox to get each selected value, I want to then insert these into separate rows in a table. The way I thought to do this was to put an append query into the loop to insert each time a selected value is found.

Here is the code:

On Error GoTo ErrHandler
    'Set variables
    Dim ctlSource As Control
    Dim intCurrentRow, intStrLength As Integer
    Dim strHolder As String
    Dim vVal As Variant
    
    Set ctlSource = Me.prodsearch 'set control source to look at the list box
    
    For intCurrentRow = 0 To ctlSource.ListCount - 1 'Loop until the end of all the items in the list box
        If ctlSource.Selected(intCurrentRow) Then 'If item selected in list box, step into the if statment
            vVal = ctlSource.Column(0, intCurrentRow) 'set vVal to the bound value in the list box for this selected item
        End If
        If vVal <> Empty Then 'If vVal is not "Empty" (it has a value) step into this if statment.
            strHolder = vVal            
CurrentDb.Execute "INSERT INTO test (product) VALUES (strHolder)"
            'Add vVal and it's needed extra string to it's self.
            vVal = Empty 'reset vVal to Empty
        End If
    Next intCurrentRow 'Loop to next itme in the list

I've run a debug and the code fails on the query here:

CurrentDb.Execute "INSERT INTO test (product) VALUES (strHolder)"

Any ideas?

 

[pbaldy]

You have to concatenate the value. If it's numeric:

CurrentDb.Execute "INSERT INTO test (product) VALUES (" & strHolder & ")"

if it's text:

CurrentDb.Execute "INSERT INTO test (product) VALUES ('" & strHolder & "')"

 

[Steve887]

That works great.

Thanks very much for the quick reply.

 

[pbaldy]

No problem. By the way, it's much more efficient to loop through selected items only, rather than all items and testing for selected. Here's an example:

  Dim ctl         As Control
  Dim varItem     As Variant
  Dim strSQL      As String

  Set ctl = Me.lstDriveConcern
  For Each varItem In ctl.ItemsSelected
    strSQL = "INSERT INTO tblDiagDriveConcern " _
          & "VALUES( " & Me.DiagID & ", " & ctl.ItemData(varItem) & ");"
    CurrentDb.Execute strSQL
  Next varItem

That one appends 2 fields; the second is the one from the listbox.