Results 1 to 10 of 10
  1. #1
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58

    Query by form errors

    I am having trouble with something that I have never had problems with before.

    I have a form with multiple unbound combo boxes. These are straight-forward: the only new thing I have used is that the second combo box has a query by form referring to the first combo box in order to limit choices by type: So, for example:

    Cbobox1 = VesselName FROM Table/Query VESSEL DETAILS
    Cbobox2 = TripId FROM Table/Query VESSEL TRIPS TABLE with Criteria on [Vessel] of FORMS!CatchTrendsSelectionForm!VesselName

    This is all fine and works perfectly.

    The issue is that the query which refers to this form in the criteria will not work. I have the criteria set up in the relevant fields in the qbe grid to refer to the relevant combo boxes in the form, but the query will not accept it - and gives me a "The microsoft database does not recognize FORMS!CatchTrendsSelectionForm!VesselName as a valid field name or expression" - UNLESS I enter it into the Query parameters as well.

    I don't want to do that because hte resulting query is uploaded to Excel for charting and analysis and it the query won't export if there are query parameters attached to the query.

    Does anyone know why I am getting this? I have numerous queries where I have queried by form in the same way and I have not had these difficulties. I can't see what I am doing wrong. BTW, all names are entered by selection, so it is not a question of spelling mistakes or incorrect references. Also btw, not trained in Access at all, I just bumble along...

    Thankyou!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Is the query just a SELECT, not CROSSTAB?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Hi June,
    It is a SELECT query, although it's true, that it is based on a CROSSTAB. I did this on purpose as I couldn't upload the crosstab to Excel. BTW, the db is huge, I can't attach it I'm afraid....

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Then I believe the CROSSTAB is cause of issue. I seldom have need to use one but I think they require parameters if you want to filter their data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    That is what I was afraid you say ... which is a major headache. You can filter on them with no issues at all: that is you can enter criteria, use the filters etc, and it all works fine....it just doesn't like query by form references. Will have to think again! But thanks for you time June!

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Maybe base the CROSSTAB on a SELECT and the SELECT has the filter criteria and export the CROSSTAB?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Yes June, I will see if that works and I guess it should, although will end up with 3 queries for the sake of one report (but that is Access I guess!)...I won't be able to export the CROSSTAB as I have already found that that doesn't work - but I can base the CROSSTAB on one select qry and the query for export on the CROSSTAB. That might work, thanks! I will let you know....
    I've just tried it and unfortunately, it didn't work...so FRUSTRATING!! I will have to abandon for a while now as other things have piled up....

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Have you tried specifying column headings in the CROSSTAB? Review http://allenbrowne.com/ser-67.html#ColHead
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Quote Originally Posted by June7 View Post
    Have you tried specifying column headings in the CROSSTAB? Review http://allenbrowne.com/ser-67.html#ColHead
    June, that all worked perfectly, thank you. Good old Allan Browne: I should have thought to search from him as he has so many excellent answers.

    However, all those hours trying to work it out, getting the selection combos to work properly and the queries to relate to them properly and .... it won't work because Excel won't list the query to import because they have the query by form references (instead of straight criteria). Aaah! really, I despair sometimes! I might just have to do the filtering in Excel after importing the full data which is probably what I should have done in the first place - I just wanted it to be Access "originating" rather than Excel originating. Oh well! Thanks a lot for all your help anyway....I have learnt quite a lot in the process.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Instead of the parameters, maybe use VBA to modify the query definition.

    I thought you were exporting from Access side, not importing from Excel side?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. form control errors
    By Suzie2012 in forum Forms
    Replies: 4
    Last Post: 04-26-2012, 05:22 PM
  2. Navigation Form Errors
    By need_help12 in forum Forms
    Replies: 22
    Last Post: 04-23-2012, 02:49 PM
  3. Replies: 2
    Last Post: 02-29-2012, 01:09 PM
  4. Replies: 10
    Last Post: 07-25-2011, 12:07 PM
  5. Query to count errors when records duplicate
    By mkallover in forum Queries
    Replies: 1
    Last Post: 07-15-2011, 11:40 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums