Query to Excel, DAO

[MagicalHippo]

I'm extremely new to this, I was trying to create a parameter vba query that finds my access query and assigns values from cells to [user input] boxes
using Excel, and runs the Access query. What I am having trouble with is this is a "nested query." What I mean is this:

Q1 - grab [user input 1] [user input 2] etc..
Q2 - using inputs, filter data
Q3 - refilter data based on Work order dates
Q4 - shows difference by COUNT.

How would I tell access to Print me out Q4, but use Excel (A2,A3) cells to be a user input @ Q1?
The .Parameters in my Code are in Q1, but I point it at Q4 as I want to display the info from there..

**Apologies if this is confusing, as this is my first time creating something like this.

Ex of code:

Sub RunParameterQuery()
 
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
 
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("J:\NFI-07\Misc Reports\Data Dumps.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("Snapshot of xWW")
 
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter WW]") = Range("B1").Value
.Parameters("[Enter Scope Status]") = Range("B2").Value
.Parameters("[Enter T-NBR]") = Range("B3").Value
.Parameters("[Enter Reason Code]") = Range("B4").Value
End With
 
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
 
'Step 5: Clear previous contents
Sheets("Sheet1").Select
ActiveSheet.Range("A6:K10000").ClearContents
 
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A7").CopyFromRecordset MyRecordset
 
'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i
 
MsgBox "Your Query has been Run"
 
End Sub

 

[alansidman]

See if this is helpful:

Search Results for “parameter query from excel” – Bacon Bits

 

[MagicalHippo]

See if this is helpful:

Search Results for “parameter query from excel” – Bacon Bits

This somewhat helps! but the issue is I beleive these examples use a query with a input built into the field.
My example is a bit different.

Q1 <- User inputs
Q2 - using inputs, filter data
Q3 - refilter data based on Work order dates
Q4 - shows difference by COUNT.

The problem I have is I need to pass a SQL query into Q1, and have it permutate and display data from Q4! My brain hurts thinking about this and I been reseraching the best way to do this, and think it is not possible.

 

[xenou]

Probably you will need to script the steps so that they all happen. Unless in some fashion each query actually depends on the previous one and the results will cascade. But if that was the case you'd probably not be here asking questions!

 

[MagicalHippo]

Probably you will need to script the steps so that they all happen. Unless in some fashion each query actually depends on the previous one and the results will cascade. But if that was the case you'd probably not be here asking questions!

thanks for the response, sorry can you explain the "script the steps" comment?
Also, each query is depended upon each other unfortunately where is how the issue arised, and is driving me crazy.

 

[xenou]

Well, Q1 (query 1, I assume) needs to run. Then Query 2 (Q2) needs to run. Then Query 3 (Q3) needs to run. And so on. You can run these queries yourself. Or you can write a little bit of VBA code or create a macro that runs them. Something has to do it is the point. So either you do it yourself or you make it happen using code or macros. That's all.

 

[MagicalHippo]

Well, Q1 (query 1, I assume) needs to run. Then Query 2 (Q2) needs to run. Then Query 3 (Q3) needs to run. And so on. You can run these queries yourself. Or you can write a little bit of VBA code or create a macro that runs them. Something has to do it is the point. So either you do it yourself or you make it happen using code or macros. That's all.

I tried running the code through VBA on Q1, but ran into a runtime error 3061 - too few parameters. That doesnt make sense as The only parameters required are 2, and I have those 2 pointing at my Cells.
See below for VBA and Q1 SQL code. I tried to put inputs into Q1, and then spit out the information from Q4, I'm not sure how to run Q2,Q3 in the code below.

Sub RunParameterQuery()
 
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyQueryDef2 As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
 
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("J:\NFI-07\Misc Reports\Working Folder\Data Dumps.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("x Work Week TNBR Date")
Set MyQueryDef2 = MyDatabase.QueryDefs("FINAL - SCOPE FOR TNBR WW (RUN THIS)")
 
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Work Week]") = Range("B1").Value
.Parameters("[TNBR]") = Range("B3").Value
End With
 
'Step 4: Open the query
Set MyRecordset = MyQueryDef2.OpenRecordset
 
'Step 5: Clear previous contents
Sheets("Sheet1").Select
ActiveSheet.Range("A6:K10000").ClearContents
 
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A7").CopyFromRecordset MyRecordset
 
''Step 7: Add column heading names to the spreadsheet
'For i = 1 To MyRecordset.Fields.Count
'ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
'Next i
 
MsgBox "Your Query has been Run"

SQL Q1

SELECT dbo_v_WO_Hist.WO, dbo_v_WO_Hist.WW, dbo_v_WO_Hist.ScopeStatus, dbo_v_WO_Hist.ScopedDate, dbo_v_WO_Hist.Lvl, dbo_v_WO_Hist.TVal, SWMS_v_TBL_WWPARAMETERS.WWStartDate, SWMS_v_TBL_WWPARAMETERS.WWHoldsStartDate, SWMS_v_TBL_WWPARAMETERS.WWFreezeStartDate, SWMS_v_TBL_WWPARAMETERS.WWStabilityStartDate, Avg(Val([TNBR])) AS TNumber
FROM dbo_v_WO_Hist INNER JOIN SWMS_v_TBL_WWPARAMETERS ON dbo_v_WO_Hist.WW = SWMS_v_TBL_WWPARAMETERS.WorkWeekID
GROUP BY dbo_v_WO_Hist.WO, dbo_v_WO_Hist.WW, dbo_v_WO_Hist.ScopeStatus, dbo_v_WO_Hist.ScopedDate, dbo_v_WO_Hist.Lvl, dbo_v_WO_Hist.TVal, SWMS_v_TBL_WWPARAMETERS.WWStartDate, SWMS_v_TBL_WWPARAMETERS.WWHoldsStartDate, SWMS_v_TBL_WWPARAMETERS.WWFreezeStartDate, SWMS_v_TBL_WWPARAMETERS.WWStabilityStartDate
HAVING (((dbo_v_WO_Hist.WW)=[Work Week]) AND ((dbo_v_WO_Hist.ScopeStatus)="Approved") AND ((dbo_v_WO_Hist.Lvl)="WO") AND ((dbo_v_WO_Hist.TVal)<[TNBR]));

 

[xenou]

To run queries 2 and 3:

DoCmd.RunQuery "Query2"
DoCmd.RunQuery "Query3"

It's not clear how you actually "spit out information from Q4" (whatever precisely you mean by that) since Q4 or anything named something like Q4 is not referenced in your code. The links above show how to run a parameterized query. I usually recommend starting out with a simpler example so you can learn how it works, before you start with a complicated query. It's not possible to tell from your query what are parameters because they aren't named and so don't have anything to distinguish them from regular field names. I usually recommend explicitly defining parameters.

More on parameters and defining them as data types:
https://support.office.com/en-us/ar...-a-query-c2806d3d-d500-45a8-8507-ec6af351b6ed
PARAMETERS Declaration - MS-Access Tutorial