Too Few Parameters Error

G

Guest

I am trying to test the validity of the data on a form before closing the
form. I get the error 'Run-Time Error 3061. Too few parameters. Expected
6.' The data in tblAuditResultQuestions comes from a subform. I got the
same error when I substituted ME! for [Form]![frmAudit ResultsChange]. Here
is the code:

Dim strSQL As String
Dim rst As DAO.Recordset
Dim cntNo As Integer
strSQL = "SELECT Count(tblAuditResultQuestions.QuestionNumber) AS CountBad "
& _
'"FROM tblAuditResultQuestions " & _
'"WHERE (((tblAuditResultQuestions.FacilityCode)=[Form]![frmAudit
ResultsChange]![txtFacilityCode]) AND " & _
'"((tblAuditResultQuestions.FacilityPlant)=[Form]![frmAudit
ResultsChange]![txtFacilityPlant]) AND " & _
'"((tblAuditResultQuestions.ProcessName)=[Form]![frmAudit
ResultsChange]![txtProcessName]) AND " & _
'"((tblAuditResultQuestions.AuditDate)=[Form]![frmAudit
ResultsChange]![txtAuditDate]) AND " & _
'"((tblAuditResultQuestions.Level)=[Form]![frmAudit
ResultsChange]![txtLevel]) AND " & _
'"((tblAuditResultQuestions.Shift)=[Form]![frmAudit
ResultsChange]![txtShift]) AND " & _
'"((tblAuditResultQuestions.Pass)='No') AND ((IIf([CorrectiveAction] Is
Null,'Y','N'))='Y'));"
Set rst = CurrentDb.OpenRecordset(strSQL)
cntNo = rst.Fields("CountBad").Value
rst.Close

Any help would be appreciated
Thanks
Marni
 
G

Guest

Its enough that one of the fields from the form wont return a value fpr you
to recieve this message.

Check all the
[Form]![frmAudit ResultsChange]![txtFacilityPlant]

And the most important thing is, the syntax should be with s after the form
(forms)
[Forms]![frmAudit ResultsChange]![txtFacilityPlant]
 
K

Keith

Marnie said:
I am trying to test the validity of the data on a form before closing the
form. I get the error 'Run-Time Error 3061. Too few parameters. Expected
6.' The data in tblAuditResultQuestions comes from a subform. I got the
same error when I substituted ME! for [Form]![frmAudit ResultsChange].
Here
is the code:

Set rst = CurrentDb.OpenRecordset(strSQL)
cntNo = rst.Fields("CountBad").Value
rst.Close
Click to expand...

I'm guessing it's because you've opened a recordset but not moved to a
record. You've opened the door but not gone inside.

Regards,
Keith.
www.keithwilby.com
 
G

Guest

The real problem here is syntax. You have the reference to the controls on
your form inside the quotes, so rather than searching for the value in the
controls, it is trying to match to the literal value of the name of your
controls. YOu also have too many single quotes in the wrong places. Here is
an example of what you need for your entire SQL:

& "WHERE (((tblAuditResultQuestions.FacilityCode)= '" & [Form]![frmAudit
ResultsChange]![txtFacilityCode]) & "' AND " & _
"((tblAuditResultQuestions.FacilityPlant)= '" & [Form]![frmAudit
ResultsChange]![txtFacilityPlant]) '" & "' AND "

Use the single quotes for text fields.
Use # instead of single quotes for date fields.
Numeric fields need nothing.
 
G

Guest

Duh! I guess I was looking at it so long I didn't notice the syntax. That
fixed the problem. Thanks! But I have another question.. this form has
several subforms. I want to put this code where it will execute and then not
allow the user to move off the main form until the data has been corrected (I
am adding an IF statement based on the value of cntNo). For example, if I
put this on the On Close event is there a way to return control back to the
form if cntNo<>0. Keep in mind the user might use the navigation buttons to
move between forms.

Thanks
Marni

Klatuu said:
The real problem here is syntax. You have the reference to the controls on
your form inside the quotes, so rather than searching for the value in the
controls, it is trying to match to the literal value of the name of your
controls. YOu also have too many single quotes in the wrong places. Here is
an example of what you need for your entire SQL:

& "WHERE (((tblAuditResultQuestions.FacilityCode)= '" & [Form]![frmAudit
ResultsChange]![txtFacilityCode]) & "' AND " & _
"((tblAuditResultQuestions.FacilityPlant)= '" & [Form]![frmAudit
ResultsChange]![txtFacilityPlant]) '" & "' AND "

Use the single quotes for text fields.
Use # instead of single quotes for date fields.
Numeric fields need nothing.

Marnie said:
I am trying to test the validity of the data on a form before closing the
form. I get the error 'Run-Time Error 3061. Too few parameters. Expected
6.' The data in tblAuditResultQuestions comes from a subform. I got the
same error when I substituted ME! for [Form]![frmAudit ResultsChange]. Here
is the code:

Dim strSQL As String
Dim rst As DAO.Recordset
Dim cntNo As Integer
strSQL = "SELECT Count(tblAuditResultQuestions.QuestionNumber) AS CountBad "
& _
'"FROM tblAuditResultQuestions " & _
'"WHERE (((tblAuditResultQuestions.FacilityCode)=[Form]![frmAudit
ResultsChange]![txtFacilityCode]) AND " & _
'"((tblAuditResultQuestions.FacilityPlant)=[Form]![frmAudit
ResultsChange]![txtFacilityPlant]) AND " & _
'"((tblAuditResultQuestions.ProcessName)=[Form]![frmAudit
ResultsChange]![txtProcessName]) AND " & _
'"((tblAuditResultQuestions.AuditDate)=[Form]![frmAudit
ResultsChange]![txtAuditDate]) AND " & _
'"((tblAuditResultQuestions.Level)=[Form]![frmAudit
ResultsChange]![txtLevel]) AND " & _
'"((tblAuditResultQuestions.Shift)=[Form]![frmAudit
ResultsChange]![txtShift]) AND " & _
'"((tblAuditResultQuestions.Pass)='No') AND ((IIf([CorrectiveAction] Is
Null,'Y','N'))='Y'));"
Set rst = CurrentDb.OpenRecordset(strSQL)
cntNo = rst.Fields("CountBad").Value
rst.Close

Any help would be appreciated
Thanks
Marni
Click to expand...
Click to expand...
 
G

Guest

That is a weak spot for me. I tried several different events, but none
really do what you are asking. If you were just closing the form, the UnLoad
event is the best place because it has a cancel argument and will not allow
the form to close. I tried LostFocus, DeActivate (which, by the description
in Help should do it), etc but I could not get it to work. I would suggest
you repost that as a new question so you can get some more knowledgable
responses.

Marnie said:
Duh! I guess I was looking at it so long I didn't notice the syntax. That
fixed the problem. Thanks! But I have another question.. this form has
several subforms. I want to put this code where it will execute and then not
allow the user to move off the main form until the data has been corrected (I
am adding an IF statement based on the value of cntNo). For example, if I
put this on the On Close event is there a way to return control back to the
form if cntNo<>0. Keep in mind the user might use the navigation buttons to
move between forms.

Thanks
Marni

Klatuu said:
The real problem here is syntax. You have the reference to the controls on
your form inside the quotes, so rather than searching for the value in the
controls, it is trying to match to the literal value of the name of your
controls. YOu also have too many single quotes in the wrong places. Here is
an example of what you need for your entire SQL:

& "WHERE (((tblAuditResultQuestions.FacilityCode)= '" & [Form]![frmAudit
ResultsChange]![txtFacilityCode]) & "' AND " & _
"((tblAuditResultQuestions.FacilityPlant)= '" & [Form]![frmAudit
ResultsChange]![txtFacilityPlant]) '" & "' AND "

Use the single quotes for text fields.
Use # instead of single quotes for date fields.
Numeric fields need nothing.

Marnie said:
I am trying to test the validity of the data on a form before closing the
form. I get the error 'Run-Time Error 3061. Too few parameters. Expected
6.' The data in tblAuditResultQuestions comes from a subform. I got the
same error when I substituted ME! for [Form]![frmAudit ResultsChange]. Here
is the code:

Dim strSQL As String
Dim rst As DAO.Recordset
Dim cntNo As Integer
strSQL = "SELECT Count(tblAuditResultQuestions.QuestionNumber) AS CountBad "
& _
'"FROM tblAuditResultQuestions " & _
'"WHERE (((tblAuditResultQuestions.FacilityCode)=[Form]![frmAudit
ResultsChange]![txtFacilityCode]) AND " & _
'"((tblAuditResultQuestions.FacilityPlant)=[Form]![frmAudit
ResultsChange]![txtFacilityPlant]) AND " & _
'"((tblAuditResultQuestions.ProcessName)=[Form]![frmAudit
ResultsChange]![txtProcessName]) AND " & _
'"((tblAuditResultQuestions.AuditDate)=[Form]![frmAudit
ResultsChange]![txtAuditDate]) AND " & _
'"((tblAuditResultQuestions.Level)=[Form]![frmAudit
ResultsChange]![txtLevel]) AND " & _
'"((tblAuditResultQuestions.Shift)=[Form]![frmAudit
ResultsChange]![txtShift]) AND " & _
'"((tblAuditResultQuestions.Pass)='No') AND ((IIf([CorrectiveAction] Is
Null,'Y','N'))='Y'));"
Set rst = CurrentDb.OpenRecordset(strSQL)
cntNo = rst.Fields("CountBad").Value
rst.Close

Any help would be appreciated
Thanks
Marni
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I will repost. Thanks for all your help!

Klatuu said:
That is a weak spot for me. I tried several different events, but none
really do what you are asking. If you were just closing the form, the UnLoad
event is the best place because it has a cancel argument and will not allow
the form to close. I tried LostFocus, DeActivate (which, by the description
in Help should do it), etc but I could not get it to work. I would suggest
you repost that as a new question so you can get some more knowledgable
responses.

Marnie said:
Duh! I guess I was looking at it so long I didn't notice the syntax. That
fixed the problem. Thanks! But I have another question.. this form has
several subforms. I want to put this code where it will execute and then not
allow the user to move off the main form until the data has been corrected (I
am adding an IF statement based on the value of cntNo). For example, if I
put this on the On Close event is there a way to return control back to the
form if cntNo<>0. Keep in mind the user might use the navigation buttons to
move between forms.

Thanks
Marni

Klatuu said:
The real problem here is syntax. You have the reference to the controls on
your form inside the quotes, so rather than searching for the value in the
controls, it is trying to match to the literal value of the name of your
controls. YOu also have too many single quotes in the wrong places. Here is
an example of what you need for your entire SQL:

& "WHERE (((tblAuditResultQuestions.FacilityCode)= '" & [Form]![frmAudit
ResultsChange]![txtFacilityCode]) & "' AND " & _
"((tblAuditResultQuestions.FacilityPlant)= '" & [Form]![frmAudit
ResultsChange]![txtFacilityPlant]) '" & "' AND "

Use the single quotes for text fields.
Use # instead of single quotes for date fields.
Numeric fields need nothing.

:

I am trying to test the validity of the data on a form before closing the
form. I get the error 'Run-Time Error 3061. Too few parameters. Expected
6.' The data in tblAuditResultQuestions comes from a subform. I got the
same error when I substituted ME! for [Form]![frmAudit ResultsChange]. Here
is the code:

Dim strSQL As String
Dim rst As DAO.Recordset
Dim cntNo As Integer
strSQL = "SELECT Count(tblAuditResultQuestions.QuestionNumber) AS CountBad "
& _
'"FROM tblAuditResultQuestions " & _
'"WHERE (((tblAuditResultQuestions.FacilityCode)=[Form]![frmAudit
ResultsChange]![txtFacilityCode]) AND " & _
'"((tblAuditResultQuestions.FacilityPlant)=[Form]![frmAudit
ResultsChange]![txtFacilityPlant]) AND " & _
'"((tblAuditResultQuestions.ProcessName)=[Form]![frmAudit
ResultsChange]![txtProcessName]) AND " & _
'"((tblAuditResultQuestions.AuditDate)=[Form]![frmAudit
ResultsChange]![txtAuditDate]) AND " & _
'"((tblAuditResultQuestions.Level)=[Form]![frmAudit
ResultsChange]![txtLevel]) AND " & _
'"((tblAuditResultQuestions.Shift)=[Form]![frmAudit
ResultsChange]![txtShift]) AND " & _
'"((tblAuditResultQuestions.Pass)='No') AND ((IIf([CorrectiveAction] Is
Null,'Y','N'))='Y'));"
Set rst = CurrentDb.OpenRecordset(strSQL)
cntNo = rst.Fields("CountBad").Value
rst.Close

Any help would be appreciated
Thanks
Marni
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error Message: Too few parameters. 3

Top