report filter

[awake2424]

Is it possible in Access 2003 to filter a report so it shows a only unique record?

For example:

Table
Case #: 1 Case #: 2
Last Name: Jones Last Name: Smith
First Name: Jim First Name: Sam

Report:
"Please enter Case #" 1

Return:
Last Name: Jones
First Name: Jim

Though my table may have several records in it I only want the report to be generated on a case by case basis. Thank you.

 

[Alc]

How are you currently opening the report? If you're using a button with code behind it, presumably it's something along the lines of

DoCmd.OpenReport [I]reportname[/I]

If so, you can add filter criteria to it e.g.

Dim str_Filter as string
 
str_Filter = "[Last Name] = 'Smith'"
 
DoCmd.OpenReport [I]reportname, [/I]acViewNormal, str_Filter

You could then construct the filter criteria by having a drop-down list on the form from which you access the report. This list displays all last names. The user would pick a last name and click on the button, then only that person's data would be displayed on the report e.g.

Dim str_Filter as string
 
str_Filter = "[Last Name] = '" & [I]DropDownListName & [/I]''"
 
DoCmd.OpenReport [I]reportname, [/I]acViewNormal, str_Filter

Hope that makes sense.

 

[awake2424]

Currently I open the report in a form using this code:

Private Sub cmdPrint_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[Case number] = " & Me.[Case number]
DoCmd.OpenReport "unrelated SCT1", acViewPreview, , strWhere
End If
End Sub

I am getting this error code: 2455 (screenshot attached)

 

[Alc]

I assume Me.Dirty = False is meant to undo any changes made to the displayed data? If so, try Me.Undo, instead. That should get you past the error message.

 

[awake2424]

I am using the code that was provided by you:

Private Sub cmdPrint_Click()

Dim str_Filter As String

str_Filter = "[Case number] = '1'"

DoCmd.OpenReport unrelatedSCT1, acViewNormal, str_Filter

End Sub

I am getting error code 2497 (screenshot attahed)

Thank you.

 

[Alc]

If unrelatedSCT1 is the report name, it needs to be in quotation marks.
Apologies, I used itallics to indicate it was a value to change, but forgot to mention that.

 

[awake2424]

Great! That worked.

How can I get the button to print a unique record.

When I click it all records print.

I have attached what my form looks like. I basically have a button that runs the code that you kindly provided and a combo box that has a blank drop down box.


Thanks.

 

[Alc]

So far, so good.

When the usuer clicks the button, you need to
1) check that they've selected a case number
2) read the code and use it as a filter
Something like this should work, assuming that
a) the field in the table is called Case Number
b) that it is a number (no need for quotation marks), not text
c) the report is called "unrelatedSCT1",

Private Sub cmdPrint_Click()

    Dim str_Filter As String

    str_Filter = "[Case Number] = " & [I]name of drop down list, no quotation marks needed[/I]

    DoCmd.OpenReport "unrelatedSCT1", acViewNormal, str_Filter

End Sub

If this doesn't work, look at the properties for the report. If the Filter On value is set to No, then it will ignore any filter criteria you give it and show all records. Set that to Yes, save the report, and try again.

 

[awake2424]

The combo box case comes up with a blank drop down box.

How can I set this?

 

[Alc]

Open the form in design mode and set the combo box's Row Source property. You'd probably want to set it to a query pulling just the Case Numbers from your table.

 

[awake2424]

That worked!

I can now select the case from a drop down list and click the button to print. It is still however printing all the records in the report and not the unique record selected. I can not find the Filter On property, but I assume thats what it is.

 

[Alc]

In the Properties window, it's the third item down on the Data tab.

 

[kate10123]

I am finding that code useful, how could it be changed for listbox? I have a list of queries in a list box which I want to use to filter a report.

 

[Alc]

What do you want to do, exactly?

 

[kate10123]

I am creating a front end for the user to select different criteria and then when they click a button it displays those relevant criterias in a report.

I have a listbox called 'Category' which contains things like nationality, ethnicity, tutorials - these are all queries which are shown on a report

and three other listboxes with other categories - Year - Students - Period

I want the user to be able to select any amount of items in the Category listbox and one in each of the other listboxes, click a button and a report showing those selected criterias previews.

 

[awake2424]

I am not seeing this.

I tried the form filter and the report and I do not see it.

I hane Access 2003 is it somewhere else?

 

[Alc]

I use that sort of thing a lot.
There may well be a neater way of doing it, but I use the following.

Assume Field1 is Gender and Field2 is City.

Dim str_Filter as String
 
str_Filter = ""
 
If not isnull(Field1) Then
     str_Filter = "Gender = '" & Field1 & "'"
end if
 
if not isnull(Field2) Then
     if str_Filter = "" then
          str_Filter = "City = '" & Field2 & "'"
     else
          str_Filter = str_Filter & " AND City = '" & Field2 & "'"
     end if
end if

and so on, until

DoCmd.OpenReport [I]"Report", ,[/I] str_Filter

If noone selects any of the criteria fields, str_Filter is blank and all records are shown in the report. If one or more is selected, str_Filter is constructed as you go and the report is filtered accordingly.

Hope that makes sense.

As I say, it may not be the neatest method, but it works (and my experience on this forum has taught me that if there's one surefire way to guarantee someone else pointing out a 'right' method, it's me explaining my own 'incorrect' one ).

 

[Alc]

I am not seeing this.

I tried the form filter and the report and I do not see it.

I hane Access 2003 is it somewhere else?

I'm also running 2003.
Could you post a screenshot of the report's properties window?

 

[kate10123]

Thanks Alc I will give that a go

 

[Alc]

Thanks Alc I will give that a go

Good luck.

If you get on okay with it, could you just post back to say it worked (you never know who else it may help).