Query by form errors

[cfljanet]

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!

[June7]

Is the query just a SELECT, not CROSSTAB?

If you want to provide db for analysis, follow instructions at bottom of my post.

[cfljanet]

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

[June7]

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.

[cfljanet]

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!

[June7]

Maybe base the CROSSTAB on a SELECT and the SELECT has the filter criteria and export the CROSSTAB?

[cfljanet]

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

[June7]

Have you tried specifying column headings in the CROSSTAB? Review http://allenbrowne.com/ser-67.html#ColHead

[cfljanet]

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.

[June7]

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?