Opening 2 Recordsets

[nancier]

I have the code below which takes (lngSampleSize or sample sizes) from QryIntertab and plugs the sample sizes into the strSql query as lngSampleSize. This works fine but now I want to add (lngSeedNumber or seed numbers)from QrySeedNumbers and have it plug the seed number into the strSql query as lngSeedNumber. Is there a way to do this since there are 2 different recordsets? Thanks

rs.Open "QryIntertab", CurrentProject.Connection, adOpenStatic, adLockOptimistic

ctr = 1
Do While Not rs.EOF
lngSampleSize = rs.Fields("Sample Size")

strSQL = strSQL & "UNION ALL (SELECT DISTINCTROW TOP " & lngSampleSize & _
" tblStupload.[EXAM#], tblStupload.[SYS-DATE], tblStupload.[AUD-ID], " & _
"tblStupload.[EXAM-TYPE], Format([DATE],'yyyymmdd') AS [INV-DATE], " & _
"tblAmount.[INVOICE-#], tblAmount.NAME, tblAmount.DESC, tblStupload.[REASON-Q], " & _
"tblStupload.[TAX-CODE], tblStupload.[LOC-CODE], tblAmount.Amt AS [Amt-Q], " & _
"tblAmount.Amt AS [AMT-A], tblStupload.[REASON-A], Format([DATE],'mmddyy') AS " & _
"[I-DATE], tblStupload.[DET-AVG], tblStupload.[T-RATE], tblStupload.COMMENTS, " & _
"tblStupload.[CHAR-1], tblStupload.[CHAR-2], tblStupload.[CHAR-3], " & _
"tblStupload.[CHAR-4], tblStupload.[CHAR-5], tblStupload.[NUM-1], " & _
"tblStupload.[NUM-2], tblStupload.[NUM-3], tblStupload.[NUM-4], " & _
"tblStupload.[NUM-5], Format([DATE],'yy') AS [C-DATE], tblAmount.Amt AS CAAN11 " & _
"FROM tblAmount, tblStupload WHERE Amt between " & ARanges(ctr, 1) & _
" and " & ARanges(ctr, 2) & " ORDER BY GetRan(lngSeedNumber,[id]))"
rs.MoveNext
ctr = ctr + 1
Loop

strSQL = Mid(strSQL, 11)
CurrentDb.QueryDefs("TestingRandom").SQL = strSQL

 

[PHV]

How are the 2 recordsets related ?
Same number of rows returned ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nancier]

Hi,
One query holds the sample sizes and the other query holds the seed for the corresponding sample size.
They will each have the same number of rows.

Thanks

 

[PHV]

Use a second recordset object named, say, rs2:
rs.Open "QryIntertab", CurrentProject.Connection, adOpenStatic, adLockOptimistic
rs2.Open "QrySeedNumbers", CurrentProject.Connection, adOpenStatic, adLockOptimistic
ctr = 1
Do While Not rs.EOF And Not rs2.EOF
lngSampleSize = rs.Fields("Sample Size")
lngSeedNumber = rs2.Fields("seed numbers")
strSQL = ...

rs.MoveNext
rs2.MoveNext
ctr = ctr + 1
Loop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nancier]

Hi, the seed numbers aren't coming into the Str-SQL query like the sample sizes. It give an error message, Too few parameters, Expected 1. It highlights this line: CurrentDb.Execute "TestingRandom Query". I'm not sure if the punctuation is wrong on
& " ORDER BY GetRan (lngSeedNumber,[id]))"
or if something else is wrong. Any ideas? Thanks

Option Compare Database
Option Explicit

Public Sub DoItAll(lngNumberOfRanges As Long)
Dim sngCounter As Single, ARanges() As Variant, ctr As Long
Dim strSQL As String, rs As New ADODB.Recordset, rs2 As New ADODB.Recordset
Dim lngSampleSize As Long
Dim lngSeedNumber As Long

'Delete TblTest if it exists
On Error Resume Next
CurrentDb.TableDefs.Delete "TblTest"
On Error GoTo 0

'This line executes the query you need to run in step 1
CurrentDb.Execute "qryInterval2ForModule6"

'This calls your old fCountFromBottom which is slightly modified to return the single data type
'that it generates
sngCounter = fCountFromBottom(lngNumberOfRanges)
'Now, take that result and feed it to the Ranges function which returns, by reference, the ARanges array
Ranges sngCounter, lngNumberOfRanges, ARanges

'Delete everything in the table tblRangesForIntertab
CurrentDb.Execute "Delete From tblRangesForIntertab"
'Now put the values of the array into this table
For ctr = 1 To lngNumberOfRanges + 1
CurrentDb.Execute "INSERT INTO tblRangesForIntertab (Range, BeginRange, EndRange) VALUES ('" & _
ARanges(ctr, 1) & " TO " & ARanges(ctr, 2) & "'," & ARanges(ctr, 1) & "," & _
ARanges(ctr, 2) & ");"
Next ctr

'Finally, you can do step 4
rs.Open "QryIntertab", CurrentProject.Connection, adOpenStatic, adLockOptimistic
rs2.Open "QrySeedNumbers", CurrentProject.Connection, adOpenStatic, adLockOptimistic

ctr = 1
Do While Not rs.EOF And Not rs2.EOF
lngSampleSize = rs.Fields("Sample Size")
lngSeedNumber = rs2.Fields("Seed Numbers")
If lngSampleSize < 200 Then
lngSampleSize = 200
End If
If ARanges(ctr, 2) = 99999999.99 Then
lngSampleSize = rs.Fields("Records")
End If
strSQL = strSQL & "UNION ALL (SELECT DISTINCTROW TOP " & lngSampleSize & _
" tblStupload.[EXAM#], tblStupload.[SYS-DATE], tblStupload.[AUD-ID], " & _
"tblStupload.[EXAM-TYPE], Format([DATE],'yyyymmdd') AS [INV-DATE], " & _
"tblAmount.[INVOICE-#], tblAmount.NAME, tblAmount.DESC, tblStupload.[REASON-Q], " & _
"tblStupload.[TAX-CODE], tblStupload.[LOC-CODE], tblAmount.Amt AS [Amt-Q], " & _
"tblAmount.Amt AS [AMT-A], tblStupload.[REASON-A], Format([DATE],'mmddyy') AS " & _
"[I-DATE], tblStupload.[DET-AVG], tblStupload.[T-RATE], tblStupload.COMMENTS, " & _
"tblStupload.[CHAR-1], tblStupload.[CHAR-2], tblStupload.[CHAR-3], " & _
"tblStupload.[CHAR-4], tblStupload.[CHAR-5], tblStupload.[NUM-1], " & _
"tblStupload.[NUM-2], tblStupload.[NUM-3], tblStupload.[NUM-4], " & _
"tblStupload.[NUM-5], Format([DATE],'yy') AS [C-DATE], tblAmount.Amt AS CAAN11 " & _
"FROM tblAmount, tblStupload WHERE Amt between " & ARanges(ctr, 1) & _
" and " & ARanges(ctr, 2) rs.MoveNext
rs2.MoveNext
ctr = ctr + 1
Loop

strSQL = Mid(strSQL, 11)
CurrentDb.QueryDefs("TestingRandom").SQL = strSQL

'Delete the table [Result Set] if it exists
On Error Resume Next
CurrentDb.TableDefs.Delete "Result Set"
On Error GoTo 0

'Execute the final query
CurrentDb.Execute "TestingRandom Query"

'Open the table that has the results
'if you uncomment the below line you can have the results table open automatically when you're done
'DoCmd.OpenTable "Result Set"

End Sub

 

[nancier]

Hi, I reversed the rs and rs2 and it still find the sample sizes but not the seeds. It must be the punctuation. Anyone have any idea what the problem is?

Thanks