Strange Error (1 Viewer)

[MattBaldry]

Hi all,

I have a strange error on a query and wondered if anyone else had ever seen anything like it. I am getting the below error.

The field it mentions is only in a single table on the query, so the message is incorrect.

This only happens when I edit the related form on a Windows 11 machine. And the fix is to simply edit the query and just move the table a little in any direction, preview in datasheet view and save it. It then works fine, as intended.

I will be changing the related table away from a multi-select field going forward, but just wondered had anyone seen this kind of error and strange fix.

~Matt

 

[Minty]

Remove the .Value
I've never seen that used in a query.

What is the full SQL for the query?

 

[The_Doc_Man]

When you said "multi-select" field did you mean "multi-valued field"?

IF SO: A multi-valued field causes issues like this because it involves a hidden table to hold the options it offers. Access was detecting the ambiguity of the fact that there was more than one virtual record "underneath" the field in question. That message wasn't concerned with the main table. That part was unambiguous. But the multi-value field causes there to be a choice with that field that is not governed either by a JOIN clause or a WHERE qualifier on that field. It was there that you had ambiguity.

IF it isn't a Multi-Valued Field, then just ignore my ramblings.

 

[June7]

I have seen the "more than one table" error when same field name is used in multiple tables and not qualified with table name in SQL. I have never seen this error just because MVF is involved. Can't answer for the Windows 11 oddity.

Really should provide entire SQL.

Minty, guessing you haven't worked with MVF in query. Need .Value if want to expand the MVF items to separate rows.

 

[Minty]

Minty, guessing you haven't worked with MVF in query. Need .Value if want to expand the MVF items to separate rows.

Nope, I have never used them, handy to know though.

 

[MattBaldry]

I have fixed this now by removing the MVF and using a table to allow me to select multiple values.

I know never to use MVF again, it was a pain.

What was strange though was the "fix". Just moving the table in query design view and saving it to make it work.

~Matt