Exporting a Query with Parameters

[Trash_Bandicoot]

I've been fighting this code for some time now and its finally broken me... I'm trying to export the data from my database to specific cells in a specific workbook. I was able to do this just fine until I took it one step further and created a query with perimeters dictated by form to act as a search function. I've looked everywhere for a solution and I know that its because of these parameters that everything falls apart, but I'm just not good enough in VBA to find a workaround. Ive included the VBA code as well as the SQL from my Query. Any help is greatly appreciated!

DoCmd.OpenQuery "Shift Date Data Export", , acReadOnly
Dim rs As DAO.Recordset
    Set objapp = CreateObject("Excel.Application")
    objapp.Visible = True
    Set wb = objapp.Workbooks.Open("G:\Crew Tracking\CT Reference\Other Reference Docs\Access Test Docs\Test 3\Back-End\Master Monthly Late Report Test 3.xlsm", True, False)
    Set rs = CurrentDb.OpenRecordset("Shift Date Data Export")
    For Each ws In wb.Worksheets
        With ws
            .Activate
            .Cells(5, 2).CopyFromRecordset rs
        End With
    Next
    rs.Close
    Set rs = Nothing
    Set objapp = Nothing
    
    DoCmd.OpenForm "Late Report: Database Search"

SELECT [CREW AUTHORIZED LATE REPORT].[DEP DATE], [CREW AUTHORIZED LATE REPORT].STATION, [CREW AUTHORIZED LATE REPORT].[FLT NBR], [CREW AUTHORIZED LATE REPORT].[SKED DEP], [CREW AUTHORIZED LATE REPORT].[F/A REPORT], [CREW AUTHORIZED LATE REPORT].[PILOT REPORT], [CREW AUTHORIZED LATE REPORT].D0, [CREW AUTHORIZED LATE REPORT].A0, [CREW AUTHORIZED LATE REPORT].A14, [CREW AUTHORIZED LATE REPORT].NOTES, [CREW AUTHORIZED LATE REPORT].[Date Modified]
FROM [CREW AUTHORIZED LATE REPORT]
WHERE ((([CREW AUTHORIZED LATE REPORT].[Date Modified]) Between DateAdd("h",6,([Forms]![Shift Date Data Export]![txtBeginOrderDate])) And DateAdd("h",30,([Forms]![Shift Date Data Export]![txtBeginOrderDate]))));

 

[Ranman256]

Do you want to copy the datasheet to EVERY spreadsheet?
(For each ws)

Why wouldn't you just copy it once,then all cells look at that range?
Does your SQL produce the correct data?

 

[Trash_Bandicoot]

Do you want to copy the datasheet to EVERY spreadsheet?
(For each ws)

Why wouldn't you just copy it once,then all cells look at that range?
Does your SQL produce the correct data?

I just need it on the first/only page of the workbook. That (for each ws) section dropped the data into every sheet so instead of finding a fix i just deleted the other sheets... (again im not great and this is largely Frankensteined so I improvise ) The SQL works fine on its own (I actually just converted the Query to SQL and copy/pasted it here) the issue is that on export, I get a Run-time error 3421. Access doesn't seem to like that i have a parameter in the query that is drawing "date" information input by the user in a separate form....

 

[Uncle Gizmo]

Is the Form "Shift Date Data Export" Open at the time you do this?

 

[Trash_Bandicoot]

Is the Form "Shift Date Data Export" Open at the time you do this?

The form is actually "Late Report: Database Search" but yes it remains open, and my excel doc opens but then I get Run-time Error 3421 and when i debug it points towards the following line of code.

Set rs = CurrentDb.OpenRecordset("Shift Date Data Export")

 

[JHB]

The form is actually "Late Report: Database Search" but yes it remains open

But in your query you point to a form with the name "[Shift Date Data Export]"

... DateAdd("h",6,([Forms]![B][COLOR=Red][Shift Date Data Export][/COLOR][/B]![txtBeginOrderDate]) ....

 

[Trash_Bandicoot]

But in your query you point to a form with the name "[Shift Date Data Export]"

... DateAdd("h",6,([Forms]![B][COLOR=Red][Shift Date Data Export][/COLOR][/B]![txtBeginOrderDate]) ....

I'm sorry yes I've got my wires crossed! It remains open the entire time.

 

[pbaldy]

The open recordset can't resolve the form reference. Simplest solution is to wrap it in the Eval() function.

 

[Trash_Bandicoot]

The open recordset can't resolve the form reference. Simplest solution is to wrap it in the Eval() function.

I appreciate the response and suggestion! So would this be updating the query prior to defining it as the recordset? I'm trying to wrap my head around what's actually happening/not happening and how I need to incorporate it in the code.

 

[pbaldy]

No code. In the query wrap each form reference in the Eval() function:

Eval("[Forms]![Shift Date Data Export]![txtBeginOrderDate]")

 

[Trash_Bandicoot]

No code. In the query wrap each form reference in the Eval() function:

Eval("[Forms]![Shift Date Data Export]![txtBeginOrderDate]")

You. Are. Amazing. Thank you for the help!

 

[pbaldy]

Happy to help and welcome to AWF by the way!