Export the same randomized query to PDF and Excel

[gojets1721]

I have a query which selects 30 random records from my table. I want to be able to export this random listing to both a PDF file and an excel file.

The issue is I'm not sure how to use VBA to export this listing to PDF & excel, and have the random records on both be the same.

My query is:

SELECT TOP 30 *
FROM tblEmployees
ORDER BY EmployeeName;

Suggestions on what to do to export this query to both PDF and excel and have the random records be the same?

 

[MajP]

Without trying it, my first guess the easiest would be to save to a temp table and use that for both. Not sure how the query you show is supposed to give random records because that does not.

 

[arnelgp]

you can also use:

for pdf:

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="theNameOfyourQuery", OutputFile:="path to where to output.pdf", OutputFormat:=acFormatPDF

for excel:

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="theNameOfyourQuery", OutputFile:="path to where to output.xlsx", OutputFormat:=acFormatXLSX

https://access-excel.tips/access-vba-docmd-outputto-method/

 

[MajP]

I think the question was if you would get two different random draws by exporting twice. You will get two different rosters unless you ensure you open the query before exporting. If not you requery on each export, and a requery always gives you a new order.

Public Sub OutputBoth()
  Dim path As String
  path = CurrentProject.path & "\"
  'This will faill with two different orders unless you open the query first
  DoCmd.OpenQuery "qryRandom30"
  DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="qryRandom30", OutputFile:=path & "output.pdf", OutputFormat:=acFormatPDF
  DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="qryRandom30", OutputFile:=path & "output.xlsx", OutputFormat:=acFormatXLSX
End Sub

@gojets1721, I would expect a query to look like this

SELECT TOP 30 EmployeeName, Rnd([EmployeeID]) AS Sort
FROM tblEmployees
ORDER BY Rnd([EmployeeID]);

 

[theDBguy]

Pardon me for jumping in but if it was me, I will probably open a recordset based on the random query and export that into Excel; afterwards, export the Excel to PDF.