asked on

I am getting an error "Too Few parameters - Expected 16" in my Access function to copy multiple records.

Error - "Too Few parameters - Expected 16"

This is kind of confusing of how to explain how this works. When I have a record that is pulled into qSkpiInvestigationTMMKsubform I may choose to enter additional information and then copy it to multiple records with in the same table. This allows me to manually copy information that should also apply to other records that need the same information. Once I enter my data into the fields of the selected record, I then check a box for all other records that I'd like to copy the selected data into. This can be done on as many records as I select. However, I have added a couple new fields and the function is getting errors.

The function set to copy 14 parameters that are selected in a form/query. It is called by running "qryUpdateSKPI_TagNumber". (This query is below the function code).

I am getting a run-time error that states "Too Few parameters - Expected 16"

This function is

Sub CopyMultipleTMMK_SelectByTagNumber()
Dim rsTMMK As DAO.Recordset
Dim qdTMMK As QueryDef
Dim reccountTMMK As Long
Dim copycountTMMK As Long
Dim msgStringTMMK As String
 
Set rsTMMK = Me.qSkpiInvestigationTMMKsubform.Form.RecordsetClone
If Not ((rsTMMK.BOF) And (rsTMMK.EOF)) Then
    rsTMMK.MoveLast
    reccountTMMK = rsTMMK.RecordCount
    Debug.Print reccountTMMK & " records in subform"
    rsTMMK.MoveFirst
     
    For ctr = 1 To rsTMMK.RecordCount
        If rsTMMK.Fields("Copy") = True And rsTMMK("tagNumber") <> Me.TagNumber Then
            copycountTMMK = copycountTMMK + 1
            Debug.Print "*** Copying details to SKPI with Tagnumber " & rsTMMK.Fields("tagNumber")
            Set qdTMMK = CurrentDb.QueryDefs("qryUpdateSKPI_TagNumber")
            qdTMMK.Parameters("strTagNumber") = rsTMMK.Fields("tagNumber")
            'Fill in the form-based params
            qdTMMK.Parameters(1) = Me.DateInvestigationIssued
            qdTMMK.Parameters(2) = Me.RCOccurence
            qdTMMK.Parameters(3) = Me.RCOccurrenceCategory
            qdTMMK.Parameters(4) = Me.RCDetection
            qdTMMK.Parameters(5) = Me.RCDetectionCategory
            qdTMMK.Parameters(6) = Me.SupplierName
            qdTMMK.Parameters(7) = Me.Responsible
            qdTMMK.Parameters(8) = Me.CMOccurence
            qdTMMK.Parameters(9) = Me.CMDetection
            qdTMMK.Parameters(10) = Me.CMCategory
            qdTMMK.Parameters(11) = Me.DateCMImplemented
            qdTMMK.Parameters(12) = Me.dispute
            qdTMMK.Parameters(13) = Me.Status
            qdTMMK.Parameters(14) = Me.FieldImpact
'            For x = 0 To qdTMMK.Parameters.Count - 1
'                Debug.Print x; qdTMMK.Parameters(x).Name & vbTab & vbTab & qdTMMK.Parameters(x).Value
'            Next
            qdTMMK.Execute
            Set qdTMMK = Nothing
        ElseIf rsTMMK("tagNumber") = Me.TagNumber Then
            Debug.Print "Skipping TagNumber" & rsTMMK("tagNumber") & " because it is the record being edited in the main form."
        End If
        rsTMMK.MoveNext
    Next
    msgStringTMMK = "Details were copied to " & copycountTMMK & " records."
Else
    msgStringTMMK = "No records in subform!"
End If
    MsgBox msgStringTMMK, vbOKOnly + vbInformation, "Copy to multiple SKPIs"
Set qdTMMK = Nothing
Set rsTMMK = Nothing
End Sub

Open in new window

PARAMETERS [strTagNumber] Text ( 255 );
UPDATE SkpiINVESTIGATION SET
 SkpiINVESTIGATION.DateInvestigationIssued = Forms!fSkpiInvestigation!DateInvestigationIssued,
 SkpiINVESTIGATION.RCOccurence = Forms!fSkpiInvestigation!RCOccurence,
 SkpiINVESTIGATION.RCOccurrenceCategory = Forms!fSkpiInvestigation!RCOccurrenceCategory,
 SkpiINVESTIGATION.RCDetection = Forms!fSkpiInvestigation!RCDetection,
 SkpiINVESTIGATION.RCDetectionCategory = Forms!fSkpiInvestigation!RCDetectionCategory,
 SkpiINVESTIGATION.SupplierName = Forms!fSkpiInvestigation!SupplierName,
 SkpiINVESTIGATION.Responsible = Forms!fSkpiInvestigation!Responsible,
 SkpiINVESTIGATION.CMOccurence = Forms!fSkpiInvestigation!CMOccurence,
 SkpiINVESTIGATION.CMDetection = Forms!fSkpiInvestigation!CMDetection, 
 SkpiINVESTIGATION.CMCategory = Forms!fSkpiInvestigation!CMCategory,
 SkpiINVESTIGATION.DateCMImplemented = Forms!fSkpiInvestigation!DateCMImplemented,
 SkpiINVESTIGATION.dispute = Forms!fSkpiInvestigation!dispute,
 SkpiINVESTIGATION.Status = Forms!fSkpiInvestigation!Status,
 SkpiINVESTIGATION.FieldImpact = Forms!fSkpiInvestigation!FieldImpact
WHERE (((SkpiINVESTIGATION.TagNumber)=[strTagNumber]));

Open in new window

John Tsioumpris

Since the error message clearly states that it expects 16 parameters..while providing only 14 have you checked if you missed something
Just take you query in the QDE and run it manually...providing the values by hand...i am pretty sure an error will pop up that will guide you to the solution.

ggodwin

ASKER

I walked through and received an error of

"CanNOT Update RCOccurenceCategory; Field in NOT updateable.

What exactly does this mean? The field is a short text field like many of the others.

This is referencing a field that was just added so it makes sense the error is here.

Dale Fye

my first question is what do these records which have something in common?

" should also apply to other records that need the same information"

Generally, if you have more that one column in common across multiple records, then those records have something in common, and should probably have a details table, so that you only have to enter that data in one place. There are obviously exceptions to that but I would start out by looking at that and consider a data structure redesign.

Rather than setting all of those parameters, why not simply open a recordset which identifies each of the records you need to update, and then set the actual value of the fields?

Dim rs as dao.recordset
set rs = currentdb.openrecordset("SELECT * from yourTable WHERE ...")
while not rs.eof
    rs.edit
    rs!DateInvestigationIssued = Me.DateInvestigationIssued
...
    rs!FieldImpact = Me.FieldImpact
    rs.Update
    rs.movenext
Wend

Open in new window

ggodwin

ASKER

Dale,
These records reference an event that occurs at a customer that has its own initial records. That event may have multiple events that follow but they do NOT have the same unique identifier. Because of there are many events of the related to one occurrences that are NOT connected. It is NOT an ideal situation. Therefore, engineers have to know which records are related to the occurrence. This function is setup so that they do NOT have to enter the same data into each of the 16 fields that are the same. The user selects the records that they want the fields to be copied into and runs the function.

Dale Fye

Is SkipInvestigation a table or a query? If it is a query, open it and attempt to manually make the edits you are attempting in the function. But if the field is not updateable, could it be a computed column?

John Tsioumpris

Is it just a field or the table...check if you have an aggregation

ggodwin

ASKER

SkpiInvestigation is a table

The query is shown in the original post.

Partha Mandayam

Look at the table design. That field may be calculated from some other fields so you can't update it
Remove that field from the update statement and see if the rest of the update works

ggodwin

ASKER

That field is populated from a “drop box” / combo box pull-down menu. My guess is that is where the error is from.

Dale Fye

OK, chances are that the field actually has a numeric data type, but uses the lookup feature built into the Access table designer. This allows you to store a numeric value while displaying the text associated with that value in a lookup table.

So, instead of trying to save a text value in that field, try using the associated numeric value. (this generally happens when you use a combo in your form design and set it to hide the bound column (the numeric value) and display the text. But sometimes, inexperienced developers will bind the combo box to the text field rather than the numeric value.

ggodwin

ASKER

Dale,
Would this be something like a 1 for yes and 0 for no?
This is my database but another designer added some fields to a table and now I'm getting these problems.

aikimark

Is the form open?

Mark Edwards

One troubleshooting tip is to put your update query in a querydef and see how/if it runs. You get a lot more info troubleshooting a querydef.
To create the querydef, use:

CurrentDB.CreateQuerydef(QuerydefNameHere,SQLStatementHere)

Open in new window

then to modify the SQL with the next query, use:

Currentdb.Querydefs(querydefnamehere).SQL = SQLStatementHere

Open in new window

to update the SQL in the existing querydef.

Open the querydef and see what you get when you try to run it. Look at the SQL view to see what the SQL looks like.

ASKER CERTIFIED SOLUTION

Mark Edwards

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ggodwin

ASKER

Thank you for all the help.

I basically rebuilt the entire form and query.

I removed things that I didn't need and confirmed all items were spelled correctly. I know one field was mis-spelled.

I am getting an error &quot;Too Few parameters - Expected 16&quot; in my Access function to copy multiple records.

I am getting an error "Too Few parameters - Expected 16" in my Access function to copy multiple records.