Passing parameter to query in recordset based on previous recordset

[dbnichol]

The code i am having trouble with is below, but here is the gist of my problem. I run a label report that pulls the number of labels from a field in a table. This all works without a problem. The issue was when the table has more than one record, the code would only use the number of copies from the first record and apply it to all.

So i created a form that had the code below that runs a macro to clear the table, and then gets each record from my source, appends it to the table, then runs the print routine. The code is failing on the second OpenRecordset which is the append query taking a parameter.

When i run it, i get 'object required' message box. I put a breakpoint in just before "Set rs2 = qdf2.OpenRecordset" and there were no errors, as soon as it gets to this set is where the Object Required message comes up. The value of the parameter shows the proper information, but i cannot figure out what object the OpenRecordset is looking for.

Thanks for any assistance.

******************
Dim db As DAO.Database
Dim db2 As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As Recordset
Dim lotno
Dim qdf As QueryDef
Dim qdf2 As QueryDef
Set db2 = CurrentDb()

Set db = CurrentDb()

Set qdf = db.QueryDefs("Query1-selectQuery")

Set rs1 = qdf.OpenRecordset
Do While Not rs1.EOF
DoCmd.RunMacro "ClearTheTable"


Set lotno = rs1.Fields(3)

Set qdf2 = db2.QueryDefs("Query2-AppendQuery")
Set qdf2.Parameters("[queryPrompt]").Value = rs1.Fields(3)
Set rs2 = qdf2.OpenRecordset


DoCmd.OpenReport "labelReport"

rs1.MoveNext
Loop

rs1.Close
qdf.Close
rs2.Close
qdf2.Close
Set rs1 = Nothing
Set qdf = Nothing
Set rs2 = Nothing
Set qdf2 = Nothing

 

[dhookom]

Is your base requirement to simply print a quantity of labels/records based on a value in a field? If so, there is no reason to use any code.

Duane
Hook'D on Access
MS Access MVP

 

[dbnichol]

The problem is that if i don't use code, all labels get the same quantity. I have a table that has the data for label 1, i need 20, then label 2 has a quantity of 35 ... I have to loop through to get each quantity for that particular label data.

 

[dbnichol]

dhookom, perhaps i should explain that this started as code to prompt the user for the number of blank labels to skip, and then the number of copies. Supports a single label quite well. The idea came from them that based on the quantity of product, we know how many labels we will need so shouldn't have to prompt the user. So i built around the original code.

I will look this over again to see if maybe i should scrap the whole original code and go directly from the query to the report. The thing is, if i can get that last dataset to work, i know it will do what i need it to do.

Thanks for the thoughts.

 

[dbnichol]

Never mind everyone, i figured it out. Rather than calling the query as a dataset, i did a docmd.RunSql with the original query pasted into the sql statement (adjusting the double quotes etc as needed for SQL) passing the parameter in that statement. I did a docmd.setwarnings false before and true after so that the user isn't shown the message from the append query, and it worked like a charm.

Because it was a large query, the text of that command is rather long, but the fact is that it works.

Thanks for everyone that puzzled over it, i hope this helps somehow.

Set lotno = rs1.Fields(3)
DoCmd.SetWarnings False
DoCmd.RunSQL ("start the query here with where clause" & lotno & "the rest of the sql statement if necessary")
DoCmd.SetWarnings True

 

[dhookom]

I'm not sure if I understand your objective and table(s). Assuming I have a table like the [Order Details] in the Northwind sample database and I want to print a quantity of labels for all the products in each order based on the Quantity field. Create a table [tblNums] with a single numeric field and values from 1 to 1000 (1000 records).

Create a query with SQL like

SELECT [Order Details].OrderID, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity, tblNums.Num
FROM [Order Details], tblNums
WHERE (((tblNums.Num)<=[Quantity]))
ORDER BY [Order Details].OrderID, [Order Details].ProductID, tblNums.Num;

This will render each record a number of times based on the quantity field.

Duane
Hook'D on Access
MS Access MVP