Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RTE 3061 too few parameters (Access97)

    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. <img src=/S/puke.gif border=0 alt=puke width=60 height=15>

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: RTE 3061 too few parameters (Access97)

    DAO doesn't know about forms and reports, so you reference to me!memID is not working in this context.
    The post <!post=blank forms,167151 >blank forms<!/post> had a solution to a similar problem.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RTE 3061 too few parameters (Access97)

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

    <font color=blue>"(tblAffiliation.AffiliationID = jcttblAfftoMem.AffiliationID) AND " & _
    "(tblAffiliation.AffiliationID = jcttblAfftoMem.AffiliationID) " & _</font color=blue>

    This seems to be the same join twice.

    Tip: right above the <font color=blue>Set rst = db.OpenRecordset(strSQL)</font color=blue>, insert a statement

    <font color=blue>MsgBox strSQL</font color=blue>

    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

    <font color=blue>Debug.Print strSQL</font color=blue>

    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

    <font color=blue>If Forms!frmoperations!Combo0.Value = 31 Or 32 Then</font color=blue>

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

    <font color=blue>If Forms!frmoperations!Combo0.Value = 31 Or Forms!frmoperations!Combo0.Value = 32 Then</font color=blue>

    (3) You can simplify

    <font color=blue>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</font color=blue>

    To

    <font color=blue>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</font color=blue>

    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:

    <font color=blue>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</font color=blue>

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

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RTE 3061 too few parameters (Access97)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RTE 3061 too few parameters (Access97)

    <P ID="edit" class=small>(Edited by HansV on 21-Aug-02 17:38. added "and use db.OpenRecordset ...")</P>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

    <font color=red>Set qdf = db.QueryDefs(strSQL)
    qdf.Parameters(Me.MEMID) = Reports!rptCommitteeLists!MEMID</font color=red>

    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.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RTE 3061 too few parameters (Access97)

    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! <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RTE 3061 too few parameters (Access97)

    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:
    <UL><LI>Make a copy of the database.
    <LI>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.
    <LI>Remove most of the records from the tables and modify sensitive data - or enter a few dummy records.
    <LI>Compact the database.
    <LI>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)
    <LI>Attach the zip file to a new reply in this thread.[/list]That way, Loungers can examine the problem hands-on.
    Regards,
    Hans

  8. #8
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RTE 3061 too few parameters (Access97)

    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.
    Attached Files Attached Files

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: RTE 3061 too few parameters (Access97)

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RTE 3061 too few parameters (Access97)

    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) for this.

    Regards,
    Hans

  11. #11
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RTE 3061 too few parameters (Access97)

    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. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •