RTE 3061 too few parameters (Access97)

Topic

New Reply

in the details format section of a report module, i am generating a runtime error 3061 – too few parameters: expected 2

the line of code where it breaks is as follows:

Set rst = db.OpenRecordset (strSQL)

the entire code for this section is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As DAO.Database
Dim rst As Recordset
Dim strSQL As String
Dim strSpeciality As String
Set db = CurrentDb
If IsNull(Me.MEMID) Then
Me.txtSpeciality = “”
Exit Sub
End If

If Forms!frmoperations!Combo0.Value = 31 Or 32 Then

lblCounty.Visible = True
lblDistrict.Visible = True
Text46.Visible = True
Text47.Visible = True

Else:
lblCounty.Visible = False
lblDistrict.Visible = False
Text46.Visible = False
Text47.Visible = False

End If

strSQL = “SELECT jcttblAfftoMem.MEMID, tblAffiliation.Affiliation ” & _
“FROM tblAffiliation INNER JOIN jcttblAfftoMem ON ” & _
“(tblAffiliation.AffiliationID = jcttblAfftoMem.AffiliationID) AND ” & _
“(tblAffiliation.AffiliationID = jcttblAfftoMem.AffiliationID) ” & _
“WHERE jcttblAfftoMem.MEMID = ” & Me.MEMID

Set rst = db.OpenRecordset(strSQL)

strSpeciality = “”
If rst.RecordCount 0 Then
rst.MoveFirst
Do While Not rst.EOF
strSpeciality = strSpeciality & rst!Affiliation & “, ”
rst.MoveNext
Loop
strSpeciality = Left(strSpeciality, Len(strSpeciality) – 2)
Me.txtSpeciality = strSpeciality
Else
Me.txtSpeciality = “”
End If

End Sub

Can anyone see what parameters it’s looking for? or perhaps there is an extra character that i am not noticing that is making Access think it needs another parameter.

Also, is there an easier way to refer to one property on many controls? like an upside down and backwards With Statement?

any suggestions for improvement will be appreciated.

Reply | Quote

Replies

DAO doesn’t know about forms and reports, so you reference to me!memID is not working in this context.
The post blank forms had a solution to a similar problem.

Reply | Quote

(1) I don’t understand the join in your SQL statement:

“(tblAffiliation.AffiliationID = jcttblAfftoMem.AffiliationID) AND ” & _
“(tblAffiliation.AffiliationID = jcttblAfftoMem.AffiliationID) ” & _

This seems to be the same join twice.

Tip: right above the Set rst = db.OpenRecordset(strSQL), insert a statement

MsgBox strSQL

Then you can inspect strSQL before it is used. Remove this statement in the final version, when everything is working as it should. Instead of this, you can also use

Debug.Print strSQL

This will “print” strSQL to the Immediate window (open it with Ctrl+G). In case of problems, you can copy the strSQL string from the Immediate window and paste it into the SQL view of a new query, to test it.

(2) The statement

If Forms!frmoperations!Combo0.Value = 31 Or 32 Then

doesn’t do what you expect. Putting the Or between two numbers forces a bitwise comparison. You need

If Forms!frmoperations!Combo0.Value = 31 Or Forms!frmoperations!Combo0.Value = 32 Then

(3) You can simplify

If Forms!frmoperations!Combo0.Value = 31 Or 32 Then
lblCounty.Visible = True
lblDistrict.Visible = True
Text46.Visible = True
Text47.Visible = True
Else:
lblCounty.Visible = False
lblDistrict.Visible = False
Text46.Visible = False
Text47.Visible = False
End If

To

Dim blnVisible As Boolean
blnVisible = (Forms!frmoperations!Combo0.Value = 31 Or Forms!frmoperations!Combo0.Value = 32)
lblCounty.Visible = blnVisible
lblDistrict.Visible = blnVisible
Text46.Visible = blnVisible
Text47.Visible = blnVisible

There are other possibilities to set a property for a series of controls, for instance:

Set the Tag property of a group of controls to the same value in design mode.
Then you can use code like the following:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = “MyTag” Then
ctl.Visible = True
End If
Next ctl
Set ctl = Nothing

This will affect only those controls that have “MyTag” as Tag value.

Reply | Quote

Thanks Hans and John for the suggestions. the new code is as follows and has new problems. the querydef generates a runtime error 3265 “Item not found in this collection” and breaks at the following line:

Set qdf = db.QueryDefs(strSQL)

I’m beginning to confuse myself , i can’t recall what the heck strSQL does in the first place LOL. Anyway the code is now as follows: I appreciate your help through this one guys.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As DAO.Database
Dim rst As Recordset
Dim strSQL As String
Dim strSpeciality As String
Dim qdf As QueryDef
Dim blnVisible As Boolean

Set db = CurrentDb

If IsNull(Me.MEMID) Then
Me.txtSpeciality = “”
Exit Sub
End If

blnVisible = (Forms!frmoperations!Combo0.Value = 31 Or Forms!frmoperations!Combo0.Value = 32)
lblCounty.Visible = blnVisible
lblDistrict.Visible = blnVisible
Text46.Visible = blnVisible
Text47.Visible = blnVisible

strSQL = “SELECT jcttblAfftoMem.MEMID, tblAffiliation.Affiliation ” & _
“FROM tblAffiliation INNER JOIN jcttblAfftoMem ON ” & _
“(tblAffiliation.AffiliationID = jcttblAfftoMem.AffiliationID)” & _
“WHERE jcttblAfftoMem.MEMID = ” & Me.MEMID

Set qdf = db.QueryDefs(strSQL)
qdf.Parameters(Me.MEMID) = Reports!rptCommitteeLists!MEMID

Set rst = qdf.OpenRecordset(strSQL)

strSpeciality = “”
If rst.RecordCount 0 Then
rst.MoveFirst
Do While Not rst.EOF
strSpeciality = strSpeciality & rst!Affiliation & “, ”
rst.MoveNext
Loop
strSpeciality = Left(strSpeciality, Len(strSpeciality) – 2)
Me.txtSpeciality = strSpeciality
Else
Me.txtSpeciality = “”
End If

End Sub

Reply | Quote

(Edited by HansV on 21-Aug-02 17:38. added “and use db.OpenRecordset …”)

The instruction

Set qdf = db.QueryDefs(strSQL)

won’t work because QueryDefs is the collection of defined queries. You can’t refer to a defined query by its SQL string. But don’t worry, you shouldn’t need

Set qdf = db.QueryDefs(strSQL)
qdf.Parameters(Me.MEMID) = Reports!rptCommitteeLists!MEMID

at all – since you placed Me.MemID outside the quotes in

strSQL = ” … ” & Me.MemID

it is concatenated to strSQL as a literal value, not as a variable (parameter). So there is no need to set a parameter.

Hopefully your code will work when you remove the two offending lines and use db.OpenRecordset instead of qdf.OpenRecordset in the next instruction.

Reply | Quote

I removed the lines. This places me right where I started, Run-time error 3061 – too few parameters expected 2. I cleared up the confusion but the syntax is going to kill me!

Reply | Quote

I don’t think we can determine the cause of the problem from a distance without knowing more about the underlying tables and/or queries.

Would it be possible to:

• Make a copy of the database.
• In the copy, strip out everything that is not relevant to the problem – just leave the offending report and the tables/queries needed for it.
• Remove most of the records from the tables and modify sensitive data – or enter a few dummy records.
• Compact the database.
• Zip the compacted database. The zip file should be less than 100KB. If not, decompile the database, then compact and zip it again. (If you do a search for decompile in this Forum, you’ll find what is meant by that, if you don’t know)
• Attach the zip file to a new reply in this thread.
  [/list]That way, Loungers can examine the problem hands-on.
  Regards,
  Hans

Reply | Quote

ok, here’s the file and the low down.

frmoperations is the place to start. there is an unbound combo box that has as its rowsource the commID and commname from tblcommittees. this combo box is the parameter for the CommID field in qryCommLists. qrycommlists is the basis for the report RptCommitteeLists. So frmoperations has to be open and a value selected from the combobox in order to open the report. On rptCommitteeLists, txtspecialty is unbound because the code we’ve been discussing modifies this textbox. the data in txtspecialty has to be displayed concatenated and separated by commas rather than in a list hence the code (happily supplied by a fellow lounger) All the data for that textbox comes from strSQL and that being the case i think i’m missing something major in the code… like the connection between strSQL and strSpecialty??? take a look, tell me what you think.

Reply | Quote

Sorry to jump in like this, but your strSql statement has a join that specifies that AffiliationID is the link on both sides of the join where in fact AffliationID is the name that has been defined in the tables. (Dyslexia, I do it all the time !!).
HTH
Pat

Reply | Quote

Hi Jenn,

Fortunately, Pat already found the cause while I was offline. It’s good that you posted the database – it would have been impossible to discover this without seeing the tables and queries. What makes it even more deceptive is that the “AffliationID” field in jcttblAffToMem has “AffiliationID” as Caption, so when you open that table, you see “AffiliationID” as column header…

The easiest fix is to replace “AffiliationID” by “AffliationID” in the code defining strSQL The report opens without problems then.

If you want to replace “AffliationID” by “AffiliationID”, you’ll have to be very careful to replace it everywhere in the database. It’s best to use an add-in like Rick Fisher’s Find-and-Replace (http://www.rickworld.com[/url%5D) for this.

Regards,
Hans

Reply | Quote

Thanks a million guys! i opted to change it everywhere in the DB and realized that i caused it when i was solving another runtime error in the code that occurred earlier in the procedure when i noticed the misspelling in the sql statement and decided to make the english right and proper.

Reply | Quote