Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report (Access 2003)

    I am running a query with the criteria for one field set to a combo box on a form ( [FrmCreatePdfFilter].[ComboTCG] ). A box pops up that says "Too Few Parameters. Expected 1". I have done this before with no problem. Has anyone seen this message before and know why it would pop up?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report (Access 2003)

    First of all, you need to reference the Forms collection, so you need this: Forms![FrmCreatePdfFilter].[ComboTCG]

    If used in a update query, you often have to wrap the reference in the Eval function, as follows:

    Eval("Forms![FrmCreatePdfFilter].[ComboTCG] ")

    Note that you must also embed the reference in quotes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report (Access 2003)

    I tried "Forms![FrmCreatePdfFilter].[ComboTCG] "and still got the same message. I am baffled because I have done this before?? Any other ideas?

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

    Re: Report (Access 2003)

    Can you provide some details? Are you using code, and if so, what code?

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report (Access 2003)

    Is this an update query or an action query (update, append, etc.)? And double-check all your spellings. And the form you are referring to is a mainform and not a subform, I hope?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Report (Access 2003)

    What happens if you change the . before [ComboTCG] to a !

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report (Access 2003)

    Unless your Combobox is bound to a field named ComboTCG, you'll probably need to use a bang (!) as Pat suggested. The dot (.) indicates a member of a collection, while the bang specifically applies to the control ComboTCG on the form FrmCreatePdfFilter.
    Charlotte

  8. #8
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report (Access 2003)

    Is the query a cross-tab query based on another query with that criteria in it?
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  9. #9
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report (Access 2003)

    Here is the code I am using:


    Private Sub TestCaseReport_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim oldTestCaseNumber As Integer
    Dim i As Integer
    Dim title As String

    oldTestCaseNumber = 0

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SA_CaseReportFilter", dbOpenForwardOnly)

    Do While rst.EOF = False

    TestCaseModule.TestCaseNumber = rst!TestCaseNumber
    title = rst!TestCaseTitle
    If oldTestCaseNumber = TestCaseNumber Then
    rst.MoveNext
    Else
    i = i + 1
    oldTestCaseNumber = TestCaseModule.TestCaseNumber
    TestCaseModule.strFilename = "TestCaseNumber" & TestCaseModule.TestCaseNumber
    DoCmd.OpenReport "SA_CaseReportFilter", acViewDesign
    DoCmd.OpenReport "SA_CaseReportFilter", acViewNormal
    DoCmd.Close acReport, "SA_CaseReportFilter"
    DoCmd.SetWarnings False
    rst.MoveNext
    End If
    Loop

    ExitHandler:
    On Error Resume Next
    TestCaseModule.TestCaseNumber = 0
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.description
    Resume ExitHandler
    End Sub

    I used this code to create a report and it worked out fine. The problem occured when I duplicated the form and used the same query with that one line of criteria.

    I am just using a standard query. Yes its a main form not a subform.

    Changing the . to an ! still had me get the same message.

    Any other ideas?

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

    Re: Report (Access 2003)

    DAO operates at a much lower level than Access. It doesn't "know" about Access forms, so you have a problem when you open a recordset on a query that has a parameter that refers to a form.

    Try this:

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim oldTestCaseNumber As Integer
    Dim i As Integer
    Dim title As String

    oldTestCaseNumber = 0

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("SA_CaseReportFilter")
    qdf.Parameters("Forms![FrmCreatePdfFilter].[ComboTCG]") = Forms![FrmCreatePdfFilter].[ComboTCG]
    Set rst = qdf.OpenRecordset(dbOpenForwardOnly)
    ...

    Note: why do you open the report in design view first, then immediately afterwards in normal view (i.e. print it)?

  11. #11
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report (Access 2003)

    I opened it in design view first because when the name came up to save a file as a .pdf, it was pulling the caption of the report so every pdf would have the same name. When I opened in design view it didnt do this. It's a very amatuer work around, but its been working

  12. #12
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report (Access 2003)

    I added this code so now my code is :


    Private Sub TestCaseReport_Click()

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim oldTestCaseNumber As Integer
    Dim i As Integer
    Dim title As String

    oldTestCaseNumber = 0

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("SA_CaseReportFilter")
    qdf.Parameters("Forms![FrmCreatePdfFilter].[ComboTCG]") = Forms![FrmCreatePdfFilter].[ComboTCG]
    Set rst = qdf.OpenRecordset(dbOpenForwardOnly)


    Do While rst.EOF = False

    TestCaseModule.TestCaseNumber = rst!TestCaseNumber
    title = rst!TestCaseTitle
    If oldTestCaseNumber = TestCaseNumber Then
    rst.MoveNext
    Else
    i = i + 1
    oldTestCaseNumber = TestCaseModule.TestCaseNumber
    TestCaseModule.strFilename = "TestCaseNumber" & TestCaseModule.TestCaseNumber
    DoCmd.OpenReport "SA_CaseReportFilter", acViewDesign
    DoCmd.OpenReport "SA_CaseReportFilter", acViewNormal
    DoCmd.Close acReport, "SA_CaseReportFilter"
    DoCmd.SetWarnings False
    rst.MoveNext
    End If
    Loop

    ExitHandler:
    On Error Resume Next
    TestCaseModule.TestCaseNumber = 0
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.description
    Resume ExitHandler
    End Sub

    When I did this, I got the message "Item not found in this collection"

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

    Re: Report (Access 2003)

    Open the query SA_CaseReportFilter in design view.
    Select Query | Parameters...
    Enter the parameter exactly as it occurs in the criteria line - probably Forms![FrmCreatePdfFilter].[ComboTCG] but check carefully.
    Select the appropriate data type, then click OK.
    If you still get the error message, check and double check the spelling of the parameter in the query and in the code. They must match exactly.

  14. #14
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report (Access 2003)

    Thanks Han, that worked.

    Thanks everyone for the input

Posting Permissions

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