Error 3326 to export (1 Viewer)

Kayleigh

Member
Local time
Today, 09:36
Joined
Sep 24, 2020
Messages
706
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, 09:36
Joined
Jul 26, 2013
Messages
10,353
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, 02:36
Joined
Oct 29, 2018
Messages
21,357
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.
 

Kayleigh

Member
Local time
Today, 09:36
Joined
Sep 24, 2020
Messages
706
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.
 

Kayleigh

Member
Local time
Today, 09:36
Joined
Sep 24, 2020
Messages
706
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");
 

Kayleigh

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

Minty

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

Kayleigh

Member
Local time
Today, 09:36
Joined
Sep 24, 2020
Messages
706
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

MVP / VIP
Local time
Today, 09:36
Joined
Jan 14, 2017
Messages
18,186
Have you tried right clicking the query then selecting Export...Excel?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:36
Joined
May 7, 2009
Messages
19,169
you need to open the Querydef and change the SQL, the "headings".
 

Users who are viewing this thread

Top Bottom