Error 3828 - cannot reference a table with a multi-valued field using an IN clause...

[etorasso]

I am running Access 2007. I have created a query that I use in my code for the creation of a spreadsheet. The query runs without any problems and gives the proper results.

When I output the query to an Excel spreadsheet it works fine (see option 1 in attached code). When I attempt to output the results to a "CSV" file, I get the 3828 error code (shown in attachment) (see option 2 in attached code).

My goal is to output the information to a CSV file then import the CSV file into an Excel template with an Excel macro. This way the file I present to the customer will be formatted to their liking.

This is running the exact same query in both instances. One works and the other does not. I tried removing the specification file and I still get the error.

Can anyone help in resolving this issue?

Thanks.

Ed Torasso

Private Sub cmdExport_Click()
'* * * * * * * * * _NEW_FORMAT_USING_CSV_FILE  * * * * * *
   On Error GoTo Err_cmdExport_Click
   
   Dim XL As Object
   Dim wbk As Object
   Dim strFolderPath As String
   Dim strAppPath As String
   Dim intOption As Integer
   
'**********************
'SET OPTION HERE TO RUN ONE OF THE TRANSFER TYPES FOR TESTING
   intOption = 2
'***********************
   strFolderPath = GetPath("Export")
   
   If strFolderPath = "ERROR" Then
      Exit Sub
   End If
   
   strAppPath = CurrentProject.path & "\"
   
   If intOption = 1 Then
      'CODE TO TRANSFER SPREADSHEET   *** THIS WORKS FINE
      strFolderPath = strFolderPath & Me.BusinessName & " " & Format(Now, "mm-dd-yyyy") & ".xlsx"
      On Error Resume Next
      Kill strFolderPath           'DELETE EXISTING FILE IF IT EXISTS
      On Error GoTo Err_cmdExport_Click
      DoCmd.OutputTo acOutputQuery, "qryBusinessExport_Consolidated", acFormatXLSX, strFolderPath, False, "", 0, acExportQualityPrint
      Set XL = CreateObject("Excel.Application")
      Set wbk = XL.Workbooks.Open(strFolderPath)
      XL.Visible = True
      Set wbk = Nothing
      Set XL = Nothing
   Else
      'CODE TO CREATE CSV FILE TO LOAD TO TEMPLATE  -  *** THIS FAILS WITH ERROR 3828
      strFolderPath = strFolderPath & Me.BusinessName & " " & Format(Now, "mm-dd-yyyy") & ".xlsm"
      On Error Resume Next
      Kill strFolderPath           'DELETE EXISTING FILE IF IT EXISTS
      On Error GoTo Err_cmdExport_Click
      DoCmd.TransferText acExportDelim, "BusinessExportConsolidated", "qryBusinessExport_Consolidated", strAppPath & "custExport.csv", False, ""
       ' Create Formatted Spreadsheet
       Set XL = CreateObject("Excel.Application")
       XL.Workbooks.Open strAppPath & "DatabaseExport_Consolidated_Template.xlsm"
       XL.Run "Module1.ImportData"
       XL.ActiveWorkbook.SaveAs strFolderPath
      XL.Visible = True
      Set XL = Nothing
   End If
   
   MsgBox "Extract file has been created at the following directory path:" & vbCrLf & vbCrLf & strFolderPath, vbInformation, "Export Completed"

Exit_cmdExport_Click:
    Exit Sub
Err_cmdExport_Click:
    If Err.Number = 2302 Then
      MsgBox "The " & Me.BusinessName & " " & Format(Now, "mm-dd-yyyy") & ".xls file is currently opened.  Please close the file " & _
             "in order to process this export request.", vbCritical, "File Open Error"
    Else
      MsgBox ErrorMessage(Me.Name, "cmdExport_Click") & Err.Number & " - " & Err.description, vbInformation, "System Code Error"
    End If
    Resume Exit_cmdExport_Click
End Sub

[imgnothumb]http://bytes.com/attachment.php? attachmentid=75 73[/imgnothumb]

[zmbd]

Multivalue fields are Microsoft's attempt to shield the enduser from real database normalization.. . along with the lookup fields at table level.
IMHO: Neither should be used unless you are going to publish to a sharepoint site. No other database supports these fields, and in fact, Microsoft barely handles these fields outside of Access with any grace...

Now to attempt to solve your issue, we'll need to see the SQL for your query and please indicate which field is your multivalue field. Please, click on the [CODE/] button in the post toolbar and then cut and paste your SQL script between the [CODE] [/CODE] tags.

[etorasso]

Thanks for your response. Here is the SQL that was generated for my query. I do not have any of the fields defined as multi-value. That is my main issue.

SELECT tblCustomer.BusinessNo 
         AS [Customer #]
   , tblCustomer.BusinessName 
         AS [Customer Name]
   , tblStatusList.Status
   , IIf([Flag]=True,"True","False") 
         AS [Update Flag]
   , tblCustomer.DID
   , tblIncident.IncidentType 
         AS [Incident Type]
   , tblEmails_Consolidated.TO 
         AS [Email Address (TO)]
   , tblEmails_Consolidated.CC 
         AS [Email Address (CC)]
   , tblCustomer.ClientComments 
         AS [Customer Comments]
   , tblSites.SiteName 
         AS [Site Name]
   , tblSites.Comments 
         AS [Site Comments] 
FROM (tblStatusList 
   INNER JOIN tblCustomer 
      ON tblStatusList.StatusID 
         = tblCustomer.StatusID) 
   INNER JOIN (tblIncident 
      INNER JOIN (tblSites 
         INNER JOIN tblEmails_Consolidated 
            ON tblSites.SiteID 
               = tblEmails_Consolidated.SiteID) 
         ON tblIncident.IncidentID 
            = tblEmails_Consolidated.IncidentID) 
      ON tblCustomer.BusinessID 
         = tblSites.BusinessID 
GROUP BY tblCustomer.BusinessNo
   , tblCustomer.BusinessName
   , tblStatusList.Status
   , IIf([Flag]=True,"True","False")
   , tblCustomer.DID
   , tblIncident.IncidentType
   , tblEmails_Consolidated.TO
   , tblEmails_Consolidated.CC
   , tblCustomer.ClientComments
   , tblSites.SiteName
   , tblSites.Comments 
HAVING (((tblCustomer.BusinessNo)
      =[Forms]![frmBusiness]![BusinessNo]));

[zmbd]

Line 2 and Line 12 in the reformatting.
You have "#" and "()" in the "AS" field names.

for giggles remove ALL of these symbols and let me know what happens.
( [Customer #] ==> [Customer_No] )
( [Email Address (TO)] ==> [Email Address_TO] )
etc...

[etorasso]

I eliminated the special characters and spaces in field names (as shown below). I still get the same error message.

SELECT tblCustomer.BusinessNo AS Customer_No, tblCustomer.BusinessName AS Customer_Name, tblStatusList.Status, IIf([Flag]=True,"True","False") AS Update_Flag, tblCustomer.DID, tblIncident.IncidentType AS Incident_Type, tblEmails_Consolidated.TO AS Email_Address_TO, tblEmails_Consolidated.CC AS Email_Address_CC, tblCustomer.ClientComments AS Customer_Comments, tblSites.SiteName AS Site_Name, tblSites.Comments AS Site_Comments
FROM (tblStatusList INNER JOIN tblCustomer ON tblStatusList.StatusID = tblCustomer.StatusID) INNER JOIN (tblIncident INNER JOIN (tblSites INNER JOIN tblEmails_Consolidated ON tblSites.SiteID = tblEmails_Consolidated.SiteID) ON tblIncident.IncidentID = tblEmails_Consolidated.IncidentID) ON tblCustomer.BusinessID = tblSites.BusinessID
GROUP BY tblCustomer.BusinessNo, tblCustomer.BusinessName, tblStatusList.Status, IIf([Flag]=True,"True","False"), tblCustomer.DID, tblIncident.IncidentType, tblEmails_Consolidated.TO, tblEmails_Consolidated.CC, tblCustomer.ClientComments, tblSites.SiteName, tblSites.Comments
HAVING (((tblCustomer.BusinessNo)=[Forms]![frmBusiness]![BusinessNo]));

[zmbd]

I wonder if it's not parsing your form correctly then and thinks it's a linked database try the DoCmd.TransferT ext Method and see if this helps. Otherwise we'll have to pull each record set up on by one and write the file.

[etorasso]

zmbd,

It is the DoCmd.TransferT ext method that is failing. The DoCmd.OutputTo method is working fine putting out an Excel file. The TransferText method, to create the CSV file, is failing.

Thanks for your help. I'll see what I can do to rework this perhaps with multiple queries instead of all the tables in 1 query with all the joins. That may work.

Thanks again.

Ed

[zmbd]

sorry, for somereason I was reading "transferspread sheet"

Take your query, and hard code:

HAVING (((tblCustomer.BusinessNo) 
      =1));

or some other number that is valid for a record and see what happens.
Either you do have a multivalue field and don't know it or there is something about the form.

You do have the latest service pack installed, yes? It fixed a lot of weirdness like this...

[Rabbit]

On my way out so this is just a guess but don't multi-valued fields get converted to comma delimited strings? And doesn't that conflict with the comma separated values spec?

[NeoPa]

Just to throw in a suggestion - You can also format the Excel workbook using Application Automation. This doesn't help directly with your problem, but might lead you to a better approach anyway ;-)

[zmbd]

@Rabbit: I think you are correct in this and that is part of the issue; however, in this case OP states that there are no multi-value fields involved.

@NeoPa: That was my next suggestion... I guess great minds think alike ( (^_^) ).

@etorasso: Waiting on your reply to the hard-coded "HAVING" clause. If that works then there is an issue with how the JET/ACE is intrepting the form. I'm seeing this in some chatter across the net where there are no multi-valued fields and some of the more complex queries, or queries wherein the form is using a combo-box, are tossing this error on deleminated text export - it appears to be a bug in Office2010

1) The tempvars collection may be an option

2) Parse the SQL string so that the returned form values are actually in the SQL string instead of the form references, add this parsed string, or alter a qrydef in, to the QRYDEFS collection. Use this parsed query.

3) Using the first parameter query that refers to your form, then create a SECOND query that refers to the parameter query. Use this second query in the transfertext method

4)As NeoPa mentions: Application Automation may be the way to go as a workaround. As this is not directly related to your question you will need to start a new thread.