trying to use query in recordset

[klnlsu]

I'm trying to use a query in a recordset and I'm getting errors. The query was created in Access using a SQL table. The query and table are in my current database. I get runtime error 3061 (expected 3 parameters) at line in red.

Public Sub UpdateLotsizes()
Dim curDatabase As Object
Dim rstQryLot As Recordset
Dim rstInvLoc As Recordset
Set curDatabase = CurrentDb
Set rstQryLot = curDatabase.OpenRecordset("QryLotsize3")

I tried putting using "DAO" as follows:

Public Sub UpdateLotsizes()
Dim curDatabase As DAO.Object
Dim rstQryLot As DAO.Recordset
Dim rstInvLoc As Recordset
Set curDatabase = CurrentDb
Set rstQryLot = curDatabase.OpenRecordset("QryLotsize3")

but then I get a compiler error "user defined type not defined". Do I need another add-in? Am I going about this all wrong?

I'm sure I have other errors in this code that some of you may notice. I'm trying to correct one error at a time.

Thanks in advance for your help.

 

[pr2-eugin]

klnlsy, Welcome to AWF..

Well you have one small problem, you need to do it like..

Public Sub UpdateLotsizes()
    Dim curDatabase As DAO[COLOR=Red][B].Database[/B][/COLOR]
    Dim rstQryLot As DAO.Recordset
    Dim rstInvLoc As [COLOR=Red][B]DAO.[/B][/COLOR]Recordset
    Set curDatabase = CurrentDb()
    Set rstQryLot = curDatabase.OpenRecordset("QryLotsize3")

 

[klnlsu]

Thanks pr2-eugin for your quick response!

I tried using "DAO." but the compiler says I have "used defined type not defined". Do I need an add-in?

 

[pr2-eugin]

OOPS.. My bad..

You need to add the DAO reference.. Go to the VBA window, Tools --> References and search in the list for Microsoft DAO X.X Object Library..

The X.X represents the version number which might vary.. I got 3.6

 

[klnlsu]

Update: trying to use query in recordset

I added the Microsoft DAO 3.6 Object Library to my reference list and that took care of my "user defined" error, but now I am getting the error 3061 (expected 3 parameters) again at this point:

Set rstQryLot = curDatabase.OpenRecordset("QryLotsize3")

 

[pr2-eugin]

Is the QryLotsize3, by any chance a parameterized Query? - As in: does it require any input by the user to be made either manually or through forms for it to Run? If the answer is yes.. You will not be able to do that, you need to create the Query dynamically and open Recordset..

If you want to Run the Query you can always use the DoCmd.OpenQuery which will prompt for the User Input when needed..

 

[klnlsu]

Yes, the query has parameters, so I'm not sure what to do here because I have to allow the user to select the data. By running the query from within my VBA code using the docmd.openquery, will that solve my problem? I don't know if I understand the strSQL statement enough to use it.

 

[pr2-eugin]

Okay can you post your SQL query here?

 

[klnlsu]

There are a series of three queries that are run in this order:

SELECT dbo_IMINVTRX_SQL.item_no, dbo_IMINVTRX_SQL.Loc, dbo_IMINVTRX_SQL.trx_dt, dbo_IMINVTRX_SQL.doc_type, dbo_IMITMIDX_SQL.activity_cd, dbo_IMINVTRX_SQL.quantity, dbo_IMITMIDX_SQL.prod_cat, dbo_IMITMIDX_SQL.mat_cost_type, dbo_IMITMIDX_SQL.drawing_release_no, dbo_IMITMIDX_SQL.stocked_fg
FROM dbo_IMINVTRX_SQL INNER JOIN dbo_IMITMIDX_SQL ON dbo_IMINVTRX_SQL.item_no = dbo_IMITMIDX_SQL.item_no
WHERE (((dbo_IMINVTRX_SQL.Loc)=[location]) AND ((dbo_IMINVTRX_SQL.trx_dt)>=[Start_YYYYMMDD]) AND ((dbo_IMINVTRX_SQL.doc_type)="I") AND ((dbo_IMITMIDX_SQL.activity_cd)="A") AND ((dbo_IMITMIDX_SQL.prod_cat)=[Product_Catagory_CODE]) AND ((dbo_IMITMIDX_SQL.mat_cost_type)="FIN") AND ((dbo_IMITMIDX_SQL.stocked_fg)="Y"));

then:

SELECT DISTINCTROW QryLotsize1.item_no, Sum(QryLotsize1.quantity) AS [Sum Of quantity], Max(QryLotsize1.quantity) AS [Max Of quantity], Count(*) AS [Count Of dbo_IMINVTRX_SQL Query]
FROM QryLotsize1
GROUP BY QryLotsize1.item_no;

and finally:

SELECT QryLotsize2.item_no, QryLotsize2.[Sum Of quantity] AS itemSum, QryLotsize2.[Max Of quantity] AS itemMax, QryLotsize2.[Count Of dbo_IMINVTRX_SQL Query] AS itemCount, [Sum Of quantity]/13 AS LS
FROM QryLotsize2
ORDER BY [Sum Of quantity]/13 DESC;

The first query collects the parameters that are input by the user.

 

[klnlsu]

I have not received a response to my SQL code, so I am guessing this is more complicated than I at first thought. Should I use a "QueryDef" approach? Or "strSQL"? I haven't been able to get the syntax right for either because I am trying to pass 3 parameters input by the user. Am I going about this all wrong? Can someone point me in the right direction? I have been struggling with this problem for a week! :banghead:

 

[klnlsu]

I'm taking a new path. I changed the query to a create table query and I am using the table as my recordset. Still debugging, but I didn't receive any compiler errors.

 

[klnlsu]

Changing the query to a make table query and using the table for my recordset worked fine, so this issue is resolved.

Thanks to pr2-eugin for your help!