Getting a too few paramaters Error on OpenRecord Set (1 Viewer)

[gmann]

I have this code, that uses a query to make a new table.

It works fine if u enter the criteria in the query itself.

But i want the user to be able to select what they want using combo boxes on a form, so i made a new form with the same code as the other one.

I put [Forms]![Lab2]![Combo10] into the criteria field in the query.
Ok So that works fine, it generates the query right.

But when it goes to make the new table, i get a Run Time Error 3061, too few paramaters Expected 3 on this line.

Set rst = dbs.OpenRecordset("LAB2", dbOpenDynaset)

Also Should there be any code for the combo boxes

Does anyone have any ideas how to fix this problem.

Thanks Greg

 

[The_Doc_Man]

Basically, I would suggest use of parameter queries.

What you do is build your MakeTable query using whatever values you need. Where you would insert data from your form, you make that field a parameter in the SQL design window.

Look up Parameter Object and Parameter Collection, plus Parameter queries.

Basically, in your VBA code, you will simply access the query as

Dim qdfMyPQ as Querydef
Dim dbMyDB as Database

Set dbMyDB = CurrentDB
Set qdfMyPQ = dbMyDB.QueryDefs("MyPQName")

qdfMyPQ.Parameters!Param1 = newvalue from combo box
qdfMyPQ.Parameters!Param2 = anothernewvalue from combo box

... run the action query

This is incomplete but it should point you to some useful help topics you will need to see.

 

[gmann]

I'm sorry, i wasn't clear, I'm not using a Make Table Query. Its just a regular query; the form i use, has a command button that makes the table, because the table i need couldn't be made with the make table query.
Here is the code, if u want to take a look.

Private Sub Combo10_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Combo18_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Combo20_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Command36_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstnew As DAO.Recordset
Dim StationsArray() As String
Dim ChemicalArray() As String
Dim DataIDArray() As Integer
Dim DateArray() As String
Dim TimeArray() As String
Dim CurrentArrayRec As Integer
Dim TempDataID As String
Dim Last As Boolean
Dim NoTime As Boolean
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim fldLoop As Field
Dim prpLoop As Property
Dim tdfNew As TableDef
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("LAB2", dbOpenDynaset)
CurrentArrayRec = 0
i = 0
Last = False
NoTime = False
' Create a new TableDef object.
Set tdfNew = dbs.CreateTableDef("New")

With rst

 ReDim Preserve DataIDArray(i)
 ReDim Preserve DateArray(i)
 ReDim Preserve TimeArray(i)
 ReDim Preserve StationsArray(i)
 rst.MoveFirst
 'Generates The DataID, Date, Time, Station Arrays
 Do While Not rst.EOF
        'Checks if the DataId is already in the array
    For i = 0 To UBound(DataIDArray())
                'If the DataID is already in the array
                'it exits out of the For Loop
    If DataIDArray(i) = !Chemical_DataID Then
        Exit For
    End If
              'if it is at the last record, and it hasn't found the DataID
              'it will add the DataID, Date, Time, Station to their Array
    If i = UBound(DataIDArray()) Then
        DataIDArray(i) = !Chemical_DataID
        CurIDRec = 1 + CurIDRec
        ReDim Preserve DataIDArray(i + 1)
        DateArray(i) = !Date_Collected
        ReDim Preserve DateArray(i + 1)
        StationsArray(i) = !Station
        ReDim Preserve StationsArray(i + 1)
        
        'If Lab is Chemtech it doesn't use the Time Array
        'Because chemtech doesn't use the time field
        'Sets NoTime to True so later in the code it doesn't
        'Insert Time as a Field
        If ![Lab] = "Chemtech" Then
        NoTime = True
        Else
        TimeArray(i) = ![Time Collected]
        ReDim Preserve TimeArray(i + 1)
        End If
    End If
    Next i
rst.MoveNext
Loop
 i = 0
 'Generates the Chemical Array
 ReDim Preserve ChemicalArray(i)
 rst.MoveFirst
 Do While Not rst.EOF
                'Checks if the parameter is already in the array
    For i = 0 To CurrentArrayRec
                'If the parameter is already in the array
                'it exits out of the For Loop
    If ChemicalArray(i) = !parameter Then
        Exit For
    End If
              'if it is at the last record, and it hasn't found the parameter
              'it will add the parameter to the Array
    If i = CurrentArrayRec Then
        ChemicalArray(i) = !parameter
        CurrentArrayRec = 1 + CurrentArrayRec
        ReDim Preserve ChemicalArray(i + 1)
        Exit For
    End If
    Next i
 rst.MoveNext
 Loop
End With
    With tdfNew
        ' Create fields and append them to the new TableDef
        ' object. This must be done before appending the
        ' TableDef object to the TableDefs collection of the
        ' Current Db
        'Generate other fields here
        .Fields.Append .CreateField("Station", dbText)
        .Fields.Append .CreateField("Elevation", dbInteger)
        .Fields.Append .CreateField("Riser_Length", dbInteger)
        .Fields.Append .CreateField("Screen_Length", dbInteger)
        .Fields.Append .CreateField("Date_Collected", dbDate)
        If (NoTime = False) Then
        .Fields.Append .CreateField("Time_Collected", dbText)
        End If
        .Fields.Append .CreateField("Matrix", dbText)
        
      'Should generate the chemical fields from the array
    For i = 0 To CurrentArrayRec
        If ChemicalArray(i) = "" Then
        Exit For
        End If
    .Fields.Append .CreateField(ChemicalArray(i), dbText)
    Next i
          
    dbs.TableDefs.Append tdfNew
    End With
       
'//////////////////////////////////////////////////////////////////////
'Inputs the data from the lab table to the new table
 Set rstnew = dbs.OpenRecordset("New", dbOpenDynaset)
 rst.MoveFirst
        'Generates the # of records needed and adds Station Value to new table
 For i = 0 To UBound(StationsArray())
 rstnew.AddNew
    If StationsArray(i) = "" Then
    Exit For
    End If
 rstnew.Fields![Station] = StationsArray(i)
 rstnew.Update
 Next i



rstnew.MoveFirst
For i = 0 To UBound(DateArray())
If (DateArray(i) = "") Then
Exit For
End If
rstnew.Edit
rstnew.Fields![Date_Collected] = DateArray(i)
rstnew.Update
rstnew.MoveNext
Next i

rstnew.MoveFirst
If (NoTime = False) Then
For i = 0 To UBound(TimeArray())
If (TimeArray(i) = "") Then
Exit For
End If
rstnew.Edit
rstnew.Fields![Time_Collected] = TimeArray(i)
rstnew.Update
rstnew.MoveNext
Next i
End If

  
 rst.MoveFirst
 rstnew.MoveFirst
 j = 8
 TempDataID = rst.Fields!Chemical_DataID
'Inserts Chemical Results
   
  Temp = rst.Fields![Chemical_DataID]
  Do While Not rst.EOF
  If (rst.Fields![Chemical_DataID] <> Temp) Then
  rstnew.MoveNext
  End If
  rstnew.Edit
  For j = 6 To CurrentArrayRec + 7
        If (rst.Fields![parameter] = rstnew.Fields(j).name) Then
      
        rstnew.Fields(j) = rst.Fields![Report_Result]
        Exit For
        End If
  Next j
  Temp = rst.Fields![Chemical_DataID]
  rstnew.Update
  rst.MoveNext
  Loop
   
  rst.MoveFirst
  rstnew.MoveFirst
  i = 0
  Do While Not rst.EOF And Last = False
  If (rst.Fields![Station] = rstnew.Fields![Station]) Then
  rstnew.Edit
  rstnew.Fields![Elevation] = rst.Fields![Elevation]
  rstnew.Fields![Riser_Length] = rst.Fields![RiserLength]
  rstnew.Fields![Screen_Length] = rst.Fields![Screen_Length]
  rstnew.Fields![Matrix] = rst.Fields![Matrix]
  rstnew.Update
  If (rstnew.Fields![Station] = StationsArray(UBound(StationsArray()) - 1)) Then
  Last = True
  End If
  rstnew.MoveNext
  i = 1 + i
  rst.MoveNext
  
  Else
  rst.MoveNext
  
  End If
Loop
ErrorHandlerExit:
  Exit Sub
  
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
  
dbs.Close

End Sub

 

[cogent1]

Is "Lab2" the query to which you are sending the form's parameters? If so, it's expecting to have the parameters before the recordset is opened. If you convert the query to an SQL statement with the parameters included in the WHERE clause with the Forms!MyForm!Mycombo syntax and assign the rst variable to it, it should work.