Create Report from results in Subform

[pauljkeenan]

What I want to do is to create a report based on the results displayed in a subform following a search.

I found this link from Microsoft about how to print a report based on a recordset.

http://support.microsoft.com/default.aspx?scid=kb;en-us;132881

The problem is, is that you need to enter the "name" of the table/recordset so the report will have a recordsource. the following is the example they give on the site.

' To create a Recordset object based on the Customers table.
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("Customers")

But, the results/recordset displayed in a subform dont actually have a name, as a table would have (or do they?). The "customers" above probably refers to the customers table in Northwind thingy.

So my question is, how do we "refer" to the results displayed in a subform, for example the results after searching for maybe all the records beginning with "J". The results displayed dont actually have a name do they? I tried using the control name in the subform where the results are displayed but this didnt work.

does anybody know how to go about this?

thanks in advance lads
paul

 

[Crowley16]

there's 2 options, you can either clone the recordset of the subform or you could just base the report on the same search criteria you used to generate the subform...

I would go for the latter option, just set the recordsource of the report to use the same where condition you used to generate the subform...

--------------------
Procrastinate Now!

 

[pauljkeenan]

Thanks for the tip, but could you maybe explain a little more for me? I've only started using access recently so still trying to get my head around certain things.

the recordset in the subform changes depending on what the user selects, so if the user asks for records between certain dates, these will be displayed below in the subform. The Where statement used to create the recordset is this code

Private Sub cmdShowDateRecs_Click()

Dim strSQL1 As String, strOrder1 As String, strWhere1 As String

strSQL1 = "SELECT qrySearchVisaSub.Ref_No, qrySearchVisaSub.Name, qrySearchVisaSub.App_Date, qrySearchVisaSub.Issue_Date, qrySearchVisaSub.Visa_Type, qrySearchVisaSub.Fee, qrySearchVisaSub.Currency, qrySearchVisaSub.Del_Sanc, qrySearchVisaSub.Nationality, qrySearchVisaSub.DateOfBirth, qrySearchVisaSub.Passport_No, qrySearchVisaSub.Sticker_No " & _
"FROM qrySearchVisaSub"

strWhere1 = "WHERE"

strOrder1 = "ORDER BY qrySearchVisaSub.App_Date;"

If Not IsNull(Me.txtDateFrom) And Not IsNull(Me.txtDateTo) Then
strWhere1 = strWhere1 & " (qrySearchVisaSub.App_Date) between #" & Format(Me!txtDateFrom, "dd-mm-yyyy") & "# and #" & Format(Me!txtDateTo, "dd-mm-yyyy") & "# AND"
End If

'Remove the last AND from the SQL statment
strWhere1 = Mid(strWhere1, 1, Len(strWhere1) - 5)

'Pass the SQL to the RowSource of the listbox
Me!frmVisaSearchSub.Form.RecordSource = strSQL1 & " " & strWhere1 & " " & strOrder1
Me!frmVisaSearchSub.Form.Requery

End Sub

so is it possible to use some of this WHERE statement as the recordsource?

thanks again

 

[Crowley16]

yes, since you've already got the whole sql statement needed for the report generated, you can just set the recordsource of the report with this sql string and open the report...

the easiest way, in my opinion, is to pass this sql string as the OpenArgs of the report, and in the reportOpen event change the me.recordsource property to the openArgs...

however you can also achieve the same directly from the form by opening the report object, and setting it's recordsource property and then open the report...

there's advantages to either approach but like I said, I think the first one would be easier to implement...

--------------------
Procrastinate Now!

 

[pauljkeenan]

so this is the sytax for openargs

DoCmd.OpenReport (rptName,acPreview,,,acWindowNormal,openArgs)

what form does openArgs take, I mean how exactly do I pass the sql string as the openArgs?

please excuse my ignorance,

thanks for the assistance

 

[Crowley16]

it's just a sting... any valid string will do, however in your case, you'll need to use a valid sql string since you intend to use that as the recordsource of the report.

plus, you can't have brackets around the arguments since you're not returning any values...

F1 on docmd.openreport...

--------------------
Procrastinate Now!

 

[pauljkeenan]

thanks for that, Ill try it now

 

[pauljkeenan]

Sorry for bugging you again, but as you can tell its still not working

This is the code behind the command button to open the report.

Private Sub cmdOpenRpt_Click()
On Error GoTo Err_cmdOpenRpt_Click

Dim stDocName As String
Dim SQL_Select As String

stDocName = "rptYes"
DoCmd.OpenForm stDocName, , , , , , OpenArgs:=SQL_Select

SQL_Select = "SELECT qrySearchVisaSub.Ref_No, qrySearchVisaSub.Name, qrySearchVisaSub.App_Date, qrySearchVisaSub.Visa_Type, qrySearchVisaSub.Fee, qrySearchVisaSub.Currency, qrySearchVisaSub.Del_Sanc, "
SQL_Select = SQL_Select & "FROM qrySearchVisaSub "
SQL_Select = SQL_Select & "WHERE qrySearchVisaSub.App_Date between #" & Format(Me!txtDateFrom, "dd-mm-yyyy") & "# and #" & Format(Me!txtDateTo, "dd-mm-yyyy") & "# "


Err_cmdOpenRpt_Click:
MsgBox Err.Description
Resume Exit_cmdOpenRpt_Click

End Sub


this is the report code

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = SQL_Select
End Sub


Have I gone about this completly the wrong way? Not even sure where the SQL string is meant to be actually.

 

[Crowley16]

lol

sql_select = me.openargs

--------------------
Procrastinate Now!

 

[pauljkeenan]

Hi there,

Yes its still not worling, Ive been messing round with it again and this is what ive come up with

Private Sub cmdOpenRpt_Click()
On Error GoTo Err_Handler

Dim stDocName As String
Dim SQL_Select As String
Dim strOrder1 As String
Dim strWhere1 As String
Dim strSQL1 As String

strSQL1 = "SELECT qrySearchVisaSub.App_Date, qrySearchVisaSub.Issue_Date, qrySearchVisaSub.Visa_Type, qrySearchVisaSub.Fee, qrySearchVisaSub.Currency, qrySearchVisaSub.Del_Sanc, qrySearchVisaSub.Nationality, qrySearchVisaSub.DateOfBirth, qrySearchVisaSub.Passport_No, qrySearchVisaSub.Sticker_No " & _
"FROM qrySearchVisaSub"

strWhere1 = "WHERE"

strOrder1 = "ORDER BY qrySearchVisaSub.App_Date;"

If Not IsNull(Me.txtDateFrom) And Not IsNull(Me.txtDateTo) Then
strWhere1 = strWhere1 & " (qrySearchVisaSub.App_Date) between #" & Format(Me!txtDateFrom, "dd.mm.yyyy") & "# and #" & Format(Me!txtDateTo, "dd.mm.yyyy") & "# AND"
End If

'Remove the last AND from the SQL statment
strWhere1 = Mid(strWhere1, 1, Len(strWhere1) - 5)
SQL_Select = strSQL1 & " " & strWhere1 & " " & strOrder1
MsgBox strWhere1

DoCmd.OpenReport "rptDates", acViewPreview, , , , OpenArgs:=SQL_Select

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Now it creates a report but it doesnt show just the records from the dates chosen by the user, it shows all records from the database.

Any ideas whats going wrong here? I know that the date comparison is working fine but it just wont pass these records to the report.

thanks in advance for any help lads

paul