Error 3326 to export (1 Viewer)

Krayna

Member
Local time
Today, 15:34
Joined
Sep 24, 2020
Messages
450
I'm trying to export a specific query as XLSX/RTF through onClick of button on form but getting Error 3326 - recordset not updateable. Can anyone help me how to resolve this?
I understand that this may be due to the query being crosstab but there must be a way of exporting it!
Here is the code - which worked fine when I did an export of the form itself...
Code:
Public Sub exportStaffTimetable(myTitle As String)

   Dim Fldr As String

   With Application.FileDialog(4)
      .AllowMultiSelect = False
      If .show <> -1 Then Exit Sub
      Fldr = .SelectedItems(1)
   End With

DoCmd.OutputTo acOutputQuery, "qrySessionsStaffDetailsCT", acFormatXLSX, Fldr & "\" & myTitle & ".xlsx", True
End Sub
 
Last edited:

Minty

AWF VIP
Local time
Today, 15:34
Joined
Jul 26, 2013
Messages
8,760
Why not use TransferSpreadsheet ? It's way more flexible, I've never used OutputTo with a query.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:34
Joined
Oct 29, 2018
Messages
16,078
Hi. Which one is the crosstab query? Is it qrySessionsStaffDetailsCT? Can you post its SQL statement? I just gave OutputTo a try on a crosstab query, and I didn't get any errors.
 

Krayna

Member
Local time
Today, 15:34
Joined
Sep 24, 2020
Messages
450
To clarify further - I have a form which is includes all the information required for the export and it will work in either of these formats. However the only problem is the headings will be the names of my textboxes which is not useful in this situation. Is there any way to change this - either temporarily renaming the textboxes or printing the labels as headings when exporting.
 

Krayna

Member
Local time
Today, 15:34
Joined
Sep 24, 2020
Messages
450
My SQL is:
SQL:
TRANSFORM First(qrySessionsAllExtended.cfDetails) AS FirstOfcfDetails
SELECT qrySessionsByStaff.Time
FROM qrySessionsAllExtended RIGHT JOIN qrySessionsByStaff ON qrySessionsAllExtended.fldSessionDayTimesID = qrySessionsByStaff.fldSessionDayTimesID
WHERE (((qrySessionsByStaff.fldStaffID)=117))
GROUP BY qrySessionsByStaff.Time, qrySessionsByStaff.fldStaffID, qrySessionsByStaff.jtblSessionDayTimes.fldStart
ORDER BY qrySessionsByStaff.jtblSessionDayTimes.fldStart
PIVOT qrySessionsByStaff.fldday IN ("Monday","Tuesday","Wednesday","Thursday");
 

Krayna

Member
Local time
Today, 15:34
Joined
Sep 24, 2020
Messages
450
@Minty Yes it does work with transferSpreadsheet method but formatting is non-existent...
 

Minty

AWF VIP
Local time
Today, 15:34
Joined
Jul 26, 2013
Messages
8,760
@Krayna Just format it - you know the filename, a little bit of VBA automation and you can format it as much as you like.
 

Krayna

Member
Local time
Today, 15:34
Joined
Sep 24, 2020
Messages
450
Thanks I just dug up an old UDF to do some basic formatting and will adjust as necessary.
Although I am curious if there is another solution to resolving the issue mentioned above regarding exporting a form. (post #4)
 

isladogs

CID VIP
Local time
Today, 15:34
Joined
Jan 14, 2017
Messages
15,240
Have you tried right clicking the query then selecting Export...Excel?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:34
Joined
May 7, 2009
Messages
13,691
you need to open the Querydef and change the SQL, the "headings".
 

Users who are viewing this thread

Top Bottom