Subreport Recordsource problems

[Stephen English]

Hi
I am really struggling with this - mental block type stuff!
See line with asterisks for first (and most pressing
problem)
Part 1 - Setting subreport record source
strBox = Forms!ProdInfo3.itnbr ' Sets it equal to product
no on calling form
strSQL = "SELECT itnbr, Left(itmloc.printable,Instr
(1,itmloc.printable,' ')) as BinLoc, " & _
"Mid(itmloc.printable,Instr(1,itmloc.printable,' ')
+1) as type " & _
" FROM itmloc " & _
"WHERE itmloc.itnbr = '" & strBox & "'" & _
" ORDER by itmloc.printable"
MsgBox strSQL ' this checks out OK
' Next 3 lines are checking out that it is valid
SQL
Set rstBins = db.OpenRecordset(strSQL)
rstBins.MoveLast
Debug.Print rstBins.RecordCount
' ********* Next line is line with problem
*********************************
Reports!Bin_Labels2i.BinsSubreport.Report.RecordSource
= strSQL
'
***********************************************************
*************
Me.BinsSubreport.Requery

Part 2
I would also like to set the recordsource of the main
report (Bin_labels2i) in code rather
than in permanent queries.

Report Bin_Labels2i has a recordsource of Query3. THis
links Query 1 with Query 2 to provide
one record as the source for the report (a bin label)
Query 2 selects the minimum value of one field of Query 1
Query 1 links several tables togetherand generates 4 to 10
records and again it links to strBox (see above)

From what I understand the recordsource has to be set to a
query or table. A recordset will not work it seems.

Is there any way I can set the recordsource to this
combination of queries please?

Any help will be greatly appreciated especially for Part 1
which is giving me a lot of pain
Thanks
Stephen

 

[Steve Schapel]

Stephen,

What is the event your procedure below is assigned to? You can set
the RecordSource of a Report in the report's Open event.

- Steve Schapel, Microsoft Access MVP

 

[Stephen English]

Hi all
THanks for your comments - they cured the mental block.
Problem now resolved in a much simpler way!
Thanks
Stephen