Create report based on data returned in a subform??

[SilFaz]

Hello there,
I have created a DB to track staff that have been trained to use applications at my organization.

I use the DB to collect the person's name, professional group and the date that they attended training of a specific application/module. So long as the date is blank, it is assumed that they have not been trained in that specific application. When a date is entered, it implies that the person is trained.

I created a search form where I can recall information about all of the people trained (leave the search criteria blank) or I can search by specific criteria - full name or portion of it for specific individuals, professional group, the module (using radio buttons), and/or date range.

Date Range works like so: I select a module and choose a Start date from which to start searching (e.g. all people trained since Jan 1/06) or an End date (all ppl trained before June 31/05), or a range using both Start and End Dates (ppl trained between Feb 01/06 and June 01/06).

I use VB coding to conduct the search and return the required info in a subform. I can click on an Edit button and add/adjust info for ppl already trained.

**What I would like to do is create a report based on the info returned in the subform. Currently, I am basing the info in the report on a Query, but that query recalls all of the information in the table. Can I use the same VB code that I have for the main form? How can I have that pass to a report?**

Much appreciated,
Silvia

 

[SilFaz]

Any ideas? Opinions?
Silvia

 

[pauljkeenan]

Hi Silfaz

Im currently trying to do the exact samt thing, I want to print the data returned in a subform as a report.

I've just started looking for info on it so will get back if I find anything promising. Any luck youself with it since you posted?

anybody else know how this can be done?

 

[pauljkeenan]

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

' 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.

anybody know anything about this? Your help would be very appreciated as I need to finish this project by tomorrow, which is only possible if God comes down and sits beside me, and of course, if God knows access.

thanks lads

 

[SilFaz]

Paul,
I'm sorry, I have been away from my computer for several days. Your second post was helpful, but I also don't know how to refer to the data or 'recordset' in the subform.

Have you managed to make anymore progress on it?

Can anyone else help with this problem? It seems like it would be a fairly common occurance.

Cheers,
Silvia

 

[pauljkeenan]

Hi Silvia

I did find a more straight forward way to do it, but for the moment its not working Im getting these annoying error messages.

Anyway maybe it'll work for you. The report command button is supposed to create a report based on the two dates selected by the user.

We need to use the OpenArgs function to pass the SQL string through to the report. My SQL string is called SQL_Select. I think its pretty straight forward at the moment.

Private Sub cmdOpenRpt_Click()
On Error GoTo Err_Handler

Dim stDocName As String
Dim SQL_Select As String

stDocName = "rptYes"
DoCmd.OpenForm stDocName, acPreview, , , , , 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") & "# "


Exit_Handler:
Exit Sub

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

End Sub


And then you also need to create a report, and build an event for the report 'on open'. In here you need to put the following code

Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs & vbNullString) > 0 Then
SQL_Select = Me.OpenArgs
End If
End Sub

But so far I havent got it to work. I get an error message saying the report 'rptYes' doesnt exist or is misspelled, which is impossible.

Does anyone know if Im going about this the wrong way, or what the problem could possibly be? Your help will be greatly appreciated.

Thanks guys

 

[pauljkeenan]

I just noticed a mistake there actually. It should be...

DoCmd.OpenReport stDocName, acPreview, , , , , OpenArgs:=SQL_Select

and not DoCmd.OpenForm,

Anyway at the moment it creates a report but includes all the records, not the ones between the dates chosen by the user.

well its a step in the right direction