Forum Discussion
bbalzer
Feb 16, 2021Copper Contributor
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 problems using the DoCmd statement...I am posting my code below.
Private Sub Toggle15_Click()
Dim QueryName As String
Dim ReportName As String
Dim SaveLocation As String
Dim DateFormater As String
Dim FileNameFormat As String
Dim Project As String
Dim Location As String
QueryName = "qryConvert"
DateFormater = Format(Date, "MMDDYY")
ReportName = "ICAM Sustainment"
FileNameFormat = ReportName & " " & DateFormater & ".csv"
Project = DLookup("ProjectDescription", "tbl_Project", "PID =" & ProjectDescription)
Location = DLookup("Location", "tbl_Location", "LID =" & SiteLocation)
DoCmd.OpenQuery QueryName, acViewNormal, acEdit
DoCmd.TransferText acExportDelim, , QueryName, Text24 & "\" & Location & "_" & Project & FileNameFormat, True
MsgBox "Export is Complete!"
End Sub
I can't seem to find out what in the query is the problem. I have a simple query with a lookup function for one value that is passed from a form...to select the records...The pic below shows where the code stops and the error message I get....Run-time error 3828 Cannot reference a table with a multi-valued field using an IN clause that refers to another database.
thanks in advance.....
- Stacy ClarkBrass ContributorMy suggestion is to get rid of the multi-valued fields. Define your tables (and fields) the classical way, and create queries that refer to all the tables directly. That should eliminate the error.
- George_HepworthSilver ContributorStacy, I suggest you read the ENTIRE thread. We ran way past that stage long ago. The problem, and the solution, were unrelated to MVFs, although that was my initial assumption as well.
- If I'm interpreting the error message properly, you can't perform such an action on a table which has a multi value field in it.
In general, multi valued field are to be avoided, just a headache! Build your own related table to get the same result.- George_HepworthSilver Contributor
It was my understanding, earlier in this thread, that the MVF and Lookup fields were removed from the table on which the query is based.
When the query is run with a hard-coded criteria in place of the form control reference, it created the .csv file properly as long as there was no : in the file name.
However, when the query was restored to the original form control reference criteria, it failed with the same error message. That leads me to believe there is something else going on.
- George_HepworthSilver Contributor
Hm. It just now occurred to me that this control on the form may be bound to a field in a DIFFERENT table and that table may be the one with the MVF field here. Is that possible?
- George_HepworthSilver Contributor
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.
- bbalzerCopper Contributor
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
- George_HepworthSilver Contributor
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.