Forum Discussion
Please help!!! Run-time error 3828 Cannot reference a table with a multi-valued field ...
I have a form that passes a string to a query. From the form, I want to convert the query results to a csv file. I have checked several posts on this subject and they don't seem to have any problem...
Problems like this are part of the reason most experienced Access developers strongly encourage NOT using multi-value fields in tables. They are like sugar cubes, really sweet at first, but sooner or later you realize eating cubed sugar is not a good plan if you value your health.
The problem is not the DoCmd statement. The problem is the method you have to use to extract the values from the items in the MVF.
It's possible, but it remains a second-tier approach.
You might find some useful information in this documentation.
So I guess I should delete the lookup fields? These fields are single value fields per record with no user entries. Would dropdown list eliminate the multi-value issue?
Any insights would be appreciated...
thanks
Lookup fields fall into the same "sugar cube" category. We are talking about standard table design versus (what I think of as ) eye candy design, to continue with the "too sweet" metaphor.
Create related tables where appropriate with an appropriate one-to-many relationship and store only the Foreign Keys in the related table.
Another way to look at it is that things like MVFs and Lookups are closely tied to tables as interface objects rather than data storage objects. In reality, users shouldn't be looking at, or directly touching tables anyway.
I try not to be too dogmatic about it because I understand that everyone has to make such decisions for themselves. However, my recommendation is always going to be to stick as close as possible to solid table design principles.
- thank you
