Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Query Error when referring to a form (2002)

    Hi,

    I'm attaching a stripped down version of a database. It opens to a form with a list of employee names. When an employye name is clicked qryProductivityCrosstab should filter to the records for the employee listed. I keep gettign an error that the form isn't recognized yet if I use the same code in qrySalesItemVisitData, it filters fine. I'm not sure what is going on.

    I'm sure someone will wonder why I'm not using a unique employee id vs the name. This info is exported in from an excel spreadhseet and there is no unique employee id.

    Leesha

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

    Re: Query Error when referring to a form (2002)

    It's hard to investigate your question since your queries and form refer to tables in the backend, which you didn't include.

    Does it help if you declare the parameter explicitly?
    - Open the crosstab query (which isn't called qryProductivityCrosstab, by the way) in design view.
    - Select Query | Parameters...
    - Enter [forms]![frmEmployeeProductivity]![lstEmployeeName] in the Parameter box, and select Text in the Data Type box next to it.
    - Click OK.

    If that doesn't work, please attach a stripped down backend database.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Error when referring to a form (2002)

    Hi Hans,

    The backend tables aren't used in this query which is why I didn't include them. Sorry for the confusion.

    You solution worked. My question is why did it work? I've not run up against this in the past. Is this something I need to do whenever text is being used vs a number to filter with?

    Thanks,
    Leesha

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

    Re: Query Error when referring to a form (2002)

    Both the query and the row source of the list box on the form refer to the table tblOfficeName in the backend, so they don't work in the database you attached.

    Crosstab queries are fairly complicated for Access to process, since the number and names of the columns can change each time the query is opened. Having a parameter is an added complication, and by declaring it explicitly, you make things just that little bit easier.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Error when referring to a form (2002)

    Oh God, I totatlly forgot I had tblOfficename included! As I said I've been working at this for forever!!

    Thanks for the explanation!! It helps!
    Leesha

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

    Re: Query Error when referring to a form (2002)

    I didn't look at your queries, but I can tell you that often if you reference a form in a query, and that query is part of a crosstab or action query that you will be executing from code, you must use the Eval() function. So instead of a reference like this:
    [forms]![frmEmployeeProductivity]![lstEmployeeName]

    You must use (notice use of quotes also!):
    Eval("[forms]![frmEmployeeProductivity]![lstEmployeeName] ")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    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: Query Error when referring to a form (2002)

    KB Article 209778 also explains why this problem occurs.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Error when referring to a form (2002)

    Thanks All!!! Access certainly is never boring!

    Leesha

Posting Permissions

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