Creating a table with multiple rows extracted from the query?

[Yessen]

I need to create a report that will have a filter that has to be selected before it is generated.

For example I choose Honda Civic as my filter criteria

I need my report to print a table that will print all the Honda Civics that I have in the query along with all the related information.

How can I do that? Sorry I am new to this report thing

 

[JANR]

If you want a generic report to print out diffrent records using filters then you could use what I use.

Create a PUBLIC variable in a module like this:

Public gstrReportFilter As String

In the open event of your report use this code to grab a filterstring.

Private Sub Report_Open(Cancel As Integer)
    If gstrReportFilter <> vbNullString Then
        Me.filter = gstrReportFilter
        Me.FilterOn = True
        gstrReportFilter = vbNullString
    End If
End Sub

You now have to build a filter string in a form and pass it to gstrReportFilter and print the report.

JR

 

[Yessen]

In the open event of your report use this code to grab a filterstring.

How do I open event? i.e connection to my query

Also, for some reason field list doesnt show up on my report design view.

I want to grab those list of fields from my query and put them in the right places so they show up there.

Pretty much as I said if I use Honda Civic as my filter how can I have other details printed on the report?

Also, how to create a table once the connection with query is established with your method?

Thanks a lot of your help

 

[JANR]

To use my methode you have to make a report which show ALL the records and then you apply a filter the same way you filter a form.

How do I open event? i.e connection to my query

When you open your report in DESIGN view open the property for your report and you see a tab marked Events the you find the one marked OnOpen. Click on the little button with 3 dots on and a dialogbox opens and you choose Eventbuilder.

This opens the "codewindow" of your report and you paste/type in between Private Sub Report_Open and End Sub the code for this event.

hope this helps

JR

 

[Yessen]

To use my methode you have to make a report which show ALL the records and then you apply a filter the same way you filter a form.



When you open your report in DESIGN view open the property for your report and you see a tab marked Events the you find the one marked OnOpen. Click on the little button with 3 dots on and a dialogbox opens and you choose Eventbuilder.

This opens the "codewindow" of your report and you paste/type in between Private Sub Report_Open and End Sub the code for this event.

hope this helps

JR

That helped a lot.

What if I want to pass the values along when I click report
something like

GenerateReport(Me.release, Me.round...etc) how can I pass these values and have them used in the code view of the generated report.

I want to get the values from the previous screen's buttons(in this case release and round) and then use them in finding search criteria string as you helped me out in the first response.

 

[Yessen]

You now have to build a filter string in a form and pass it to gstrReportFilter and print the report.

JR

what is the format of the report filter string?

the criteria for filters are given in the previous screen where the user hits the button generate report.

it will have the combo box where he/she could select the filter
there will be two filters used so two comboboxes will be populated and only then the user will click the generate report button.

which has to automatically generate the report filter string and do the filtering for me.

 

[JANR]

There are many ways to build a filterstring. But a typical filter string using text fields looks like this:
(CarMake = "Nissan" AND CarModel = "200SX")
If you enter this in the filterproperty of a report it will show only record that matches.
To build a string like this from a form if you have some comboboxes to make filter selection etc cboCarMake and cboCarModel and a clickbutton to print the report. In the ClickEvent of the button you put something like this:

Private CarPrint_Click()
Dim sFilter as String
sFilter = "(CarMake = '" & Me.cboCarMake & "' AND CarModel = '" & Me.cboCarModel & "')"
gstrReportFilter = sFilter
DoCmd.OpenReport "CarReport"
End Sub

You can play with diffrent filters and fields, as long as know how the filterstring looks like you can build it
using the above syntax.

JR

 

[Yessen]

There are many ways to build a filterstring. But a typical filter string using text fields looks like this:
(CarMake = "Nissan" AND CarModel = "200SX")
If you enter this in the filterproperty of a report it will show only record that matches.
To build a string like this from a form if you have some comboboxes to make filter selection etc cboCarMake and cboCarModel and a clickbutton to print the report. In the ClickEvent of the button you put something like this:

Private CarPrint_Click()
Dim sFilter as String
sFilter = "(CarMake = '" & Me.cboCarMake & "' AND CarModel = '" & Me.cboCarModel & "')"
gstrReportFilter = sFilter
DoCmd.OpenReport "CarReport"
End Sub

You can play with diffrent filters and fields, as long as know how the filterstring looks like you can build it
using the above syntax.

JR

From the code you provided, all i can see it is doing is creating a filter string and opening a report.

but how does it know where to get the information i.e which query to connect to apply the filter string and extract information those rows that matched the search criteria.

how can I get these rows and iterate through them and put them into a table that will appear on the report I have opened in our case "CarReport"

 

[JANR]

This line:

gstrReportFilter = sFilter

passes the filter string to the global variabel gstrReportFilter and when the report opens it has its own open event like i posted earlier and this event graps the filterstring and puts it in its filterproperty and filter the report.

JR

 

[Yessen]

This line:

gstrReportFilter = sFilter

passes the filter string to the global variabel gstrReportFilter and when the report opens it has its own open event like i posted earlier and this event graps the filterstring and puts it in its filterproperty and filter the report.

JR

I see. then report takes it after that with passed filter string.

Then how does report know where to get information using obtained filter string?

Provided code just assumes that it has all the possible report and will sort out what we wanted. But initially how does it get those reports up there? how can I say please get all of the report from this query.

Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub

 

[JANR]

Then how does report know where to get information using obtained filter string?

A report can either get it's recordsource from a saved Query or you build a query in the reports own control Source just like a query. What you want is a query that pulls all relevant date in one big lump and filter out unwanted data and print your report.

So when the report opens (get printed) the event of OnOpen fires and executes there by looks at the globalvariable and grap whatever is in it and filters the report BEFORE the actual printing happens.

To get the consept of this you must have a basic understanding of properties and events.

Hope this clarifies it for you. There are alot of reading avalible on the web and it is a steep learning curve when you create something like this, but when you get the basic's it's like a drug you will soo bee looking for a excuse to dibble with VBA.

JR