Report Filter

[DragonTech]

I'm trying to use a command button in a form to filter and open a report. I am able to get it to open the report, but I cannot get it to filter the report based on a combobox in the form. I've tried every combination of code I could think of and find. Here is what I currently have:

Private Sub FilterReport_Click()
DoCmd.OpenReport "Report", acViewReport, "First Name='" & Me.FName & "'"
End Sub

Option Compare Database

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

"FilterReport" = Form Button
"Report" = Report
"First Name" = Report Field
"FName" = Form Combo Box
:banghead:

 

[BlueIshDan]

DoCmd.OpenReport "Report", acViewReport, "[First Name]='" & Me.FName & "'"

 

[DragonTech]

DoCmd.OpenReport "Report", acViewReport, "[First Name]='" & Me.FName & "'"

BlueIshDan, thank you for your reply. I tried putting the brackets in the code, but there was no change in behavior.

 

[Sketchin]

Try this:

DoCmd.OpenReport "Report", acViewReport, "[First Name]='" & Me.FName.column(0) & "'"

Change the column number if the data you want to use as criteria is not the primary key.

 

[DragonTech]

Sketchin,

It's still not filtering the records. When I press the command button on the form, all of the records show up in the report. I'm sure I'm just making a dumb mistake, but I can't figure what it is.

 

[Sketchin]

Lets try something else:

DoCmd.OpenReport "Report", acViewReport, "[First Name]= " & Me.FName

 

[DragonTech]

It still is not working. I'm thinking I'm doing something wrong beyond the code. I've checked the names of everything and they all check out. Do you think it might be because I'm using a combobox as a filter source?

 

[Sketchin]

After this code:

DoCmd.OpenReport "Report", acViewReport, "[First Name]= '" & Me.FName & "'"

Add this:

debug.print me.fname

the first name of the person selected should appear in the immediate window. If it doesn't, there is a different problem.

 

[DragonTech]

I added it after the code. There is nothing different. It still just opens the report with all of the records.

 

[Sketchin]

Did you see a name come up in the immediate window?

FYI:

http://office.microsoft.com/en-ca/access-help/HV080205290.aspx

 

[DragonTech]

Ah! Yes! I didn't know what the immediate window was. The code is POAH05.

 

[DragonTech]

I figured out that POAH05 is a unique identifier under the primary key in my main table. The immediate window returns the unique identifier related to the First Name I choose every time I run the report filter. What do I do with this information?

 

[DragonTech]

I've done quite a bit of editing. At the moment, I have it so that when I open the query and enter a criteria into the prompt, I get the proper results. The same happens when I open the report. However, when I select the criteria from the form combobox that is linked to the specified field in the query, I get zero results. Any help is appreciated.

 

[GinaWhipp]

Please post the RowSource of your Combo Box.

 

[DragonTech]

The RowSource of the Combo Box in the form is

SELECT Students.ID, Students.[First Name] FROM Students ORDER BY Students.[First Name];

 

[GinaWhipp]

yep, that would be the problem, first Column in your Combo Box is the ID not the First Name, so, let's try this...

DoCmd.OpenReport "Report", acViewReport, "[First Name]='" & Me.FName.Column(1) & "'"

 

[DragonTech]

I've tried that method. It makes no difference. The results still come up as zero.

 

[GinaWhipp]

Hmm, something strange here... Please post the Record Source for the report.

 

[DragonTech]

The Record Source is ReportGenQ, which is a query that contains the First Name field from the Students table. The criteria for the First Name field in ReportGenQ is [Forms]![Report Generator]![FName]. Report Generator is the form.

 

[GinaWhipp]

Hmm, no I need to see the query itself not just the name, so...

1. Open query in Design Mode
2. In the upper left hand corner select SQL View from the drop down
3. Copy/paste everything there here