Feb 16 2021 09:19 AM
Feb 16 2021 09:19 AM
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!"
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.....
Feb 16 2021 10:16 AM
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.
Feb 16 2021 10:39 AM
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...
Feb 16 2021 11:02 AM
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.
Feb 17 2021 06:19 AM
I went through the table and removed all of the lookup fields. I am still getting the same error message. What is confusing me is even before the lookup fields were removed and recreated, the query would retrieve the data and I could convert query results to an excel file manually and then manually save as a CSV with no problem, just not programmatically...any ideas?
Feb 17 2021 06:45 AM
The same error message even though there are no longer any MVF fields in the tables?
Feb 17 2021 06:53 AM
@bbalzer That suggests there is another problem in that query. In the screenshot, we see only the query name. Can you open the query in SQL view and copy the SQL out and paste here?
Feb 17 2021 06:56 AM
SELECT tbl_AssetUpdate.ID, tbl_AssetUpdate.ProjectNo, tbl_AssetUpdate.RPUID, tbl_AssetUpdate.UnitID, tbl_AssetUpdate.UDID, tbl_AssetUpdate.Site, tbl_AssetUpdate.GISLayer, tbl_AssetUpdate.GISObjectID, tbl_AssetUpdate.Personnel, tbl_AssetUpdate.DateSub, tbl_AssetUpdate.SME, tbl_AssetUpdate.SMEPhone, tbl_AssetUpdate.InstallationID, tbl_AssetUpdate.ServiceStatus, tbl_AssetUpdate.FeatureID, tbl_AssetUpdate.UpdateType, tbl_AssetUpdate.LongDescription, tbl_AssetUpdate.YearInstalled, tbl_AssetUpdate.SizeLength, tbl_AssetUpdate.PhysicalLocation, tbl_AssetUpdate.InspectedBy, tbl_AssetUpdate.InspectedDate, tbl_AssetUpdate.Manufacturer, tbl_AssetUpdate.ModelNo, tbl_AssetUpdate.SerialNo, tbl_AssetUpdate.[Voltage Level], tbl_AssetUpdate.[Conduit Size], tbl_AssetUpdate.NoConduits, tbl_AssetUpdate.NoWires, tbl_AssetUpdate.ConduitMaterial, tbl_AssetUpdate.PipeMaterial, tbl_AssetUpdate.PipeSize, tbl_AssetUpdate.DownstreamInvert, tbl_AssetUpdate.TankType, tbl_AssetUpdate.RemainingSL, tbl_AssetUpdate.[WO No], tbl_AssetUpdate.Comments, tbl_AssetUpdate.WorkOrderType, tbl_AssetUpdate.NetworkType, tbl_AssetUpdate.NetworkSubType, tbl_AssetUpdate.Disposition, tbl_AssetUpdate.[Equipment Type], tbl_AssetUpdate.WaterType, tbl_AssetUpdate.[Gas Type], tbl_AssetUpdate.Attachments, tbl_AssetUpdate.Completed, tbl_AssetUpdate.DateCompleted, tbl_AssetUpdate.UpdatedBy, tbl_AssetUpdate.UpdateStatus, tbl_AssetUpdate.DShtNo, tbl_AssetUpdate.DWGNo, tbl_AssetUpdate.PID
Feb 17 2021 07:15 AM
Thank you. And I think we were sent down the wrong rabbit hole by that error message, at least in part.
While my recommendations regarding MVF and Lookup fields in tables are the same, there is also another
issue problem at work in this query in VBA. I should have considered that earlier.
I don't think the problem is the WHERE clause, but as a test, can you create a copy of the query with a hard-coded ProjectNo instead of the form control reference?
WHERE (((tbl_AssetUpdate.ProjectNo)=12345)); -- Use a valid ProjectNo in your project table, of course.
Feb 17 2021 07:27 AM
Shouldn't the object be the qryconvert? It seems like its looking for the csv file?
Feb 17 2021 08:10 AM
Yes, that is exactly what is happening, I think. And it does reveal the problem more clearly, thank goodness. Do you have this csv linked to your accdb? That's a remarkably long file name, BTW, It occurs to me to wonder if perhaps the number of characters exceeds some limit. It also occurs to me to wonder if the path to that csv is specified properly.
Feb 17 2021 08:28 AM
Sorry, I overlooked your last question. I assumed that the query is based, at least in part on that missing .csv file. Is the table in the query linked to it? Somehow, something is referencing it.
Feb 17 2021 08:42 AM
Since the query is not converted to a CSV file until the DoCmd code is completed, why would it be looking for a CSV file? I am still confused on how it works fine when manually executed, but with the program code it bombs...
Feb 17 2021 08:48 AM
@bbalzer I can't see the internal workings in that accdb, so all I can do is guess from experience. Are you saying that the csv in the error message is intended to be the RESULT of the export, and not one of the sources, i.e. not a linked table?
If so, then I'm going to lean even stronger to a length problem in the file name. Try, as a test, something very short, like "Test.csv" for the output file instead.
Feb 17 2021 08:56 AM
yes, the desired output is the query converted to a CSV file. I tried to shorten the file name, but it didn't have any effect. Should the conversion be a two-step process? I had originally went that direction and was able to have the query programmatically saved as a .xlsx file. I was in the process of trying to rename the file as a csv file, until I saw a comment about using the doCmd transfertext post, seemed so easy. The original code used a saved export to excel and that portion worked just fine, it was the renaming that buried me...
Feb 17 2021 09:44 AM
Wow, We've really strayed away from that initial error message.
So, if it works as an export to .xlsx, then it does seem like the problem is the file format piece.
I'm of two minds.
If you can get to the result you want by exporting the .xlsx and then converting that to a .csv, then that would be the path of expediency and "making it work".
If you can get the desired result in a single steps, then that is certainly more elegant.
Let me do a bit of investigation on that aspect. Unfortunately, I think we really got mislead by the error messages, and that's at least partly my fault for not thinking that way.
Feb 17 2021 09:45 AM
no worries, appreciate the help..
Feb 17 2021 10:01 AM
How about removing that colon from the file name. I can break my export by inserting a : into the file name to be created. It gives me a different error message, but the behavior seems to be the same.
In fact, now that I think about it, that might be resolved by Windows along the lines of the colon in a drive reference, c:\path and that would break it. Does that make a difference in your environment?