List Box select error when query has multiple tables

[rwwilliamson]

Using Access 2000. I have a select Form with an unbound list box from table of contest participants. Each participant has a unique number. Three tables of data that use the participant number as the key. First table has name, category, etc. Second table has scores. Third table has an additional field of unique data. Form is used to select the participant and a "preview" button opens the Report with the participant number as the bound column. The Report source is a Query based on these 3 tables. If any one of the tables is individually used in the query, then the correct data is selected and presented on the form. When the tables are joined by the participant number, then an error dialog box appears with "The Microsoft Jet database engine does not recognize [formname].[listboxname] as a valid field name or expression."Merging all the tables into a single large table is not a choice.

 

[boblarson]

I think we need more information. What is the underlying query SQL for the report?

 

[rwwilliamson]

In my original design, one of the linked tables in the form-initiated query was actually a crosstab query of the scores table. Apparently a crosstab cannot accept an input from a form because when I replaced the crosstab with a simple "total of" query, then the form input works as expected and the report is generated correctly. Case closed.