Attach Filter to openReport cmd.

[slyvsspy]

here is my code and I was wanting to know how to attach this filter that I have make called 'josh' to the report i'm opening because I want the report to only have the records that are on the form.

Private Sub Command50_Click()
josh = "VOTERID>' '"
If Me.PrimaryButton.Value = True Then josh = josh + " and PRIMARY > ' '"
If Me.RepublicanButton = True Then josh = josh + " and REP > ' '"
If Me.DemocratButton = True Then josh = josh + " and DEM > ' '"
If Me.IndependentButton = True Then josh = josh + " and IND > ' '"
If Me.PrecinctButton > "' '" Then josh = josh + " and PRECINCT = forms!multipleselectform!precinctbutton"
If Me.LastVoteButton > "' '" Then josh = josh + " and LASTVOTERG >= forms!multipleselectform!lastvotebutton"

Me.Filter = josh
Me.FilterOn = True

Dim stDocName As String

stDocName = "multipleselectreport"
DoCmd.OpenReport stDocName, acPreview

On Error GoTo Err_Command50_Click



Exit_Command50_Click:
Exit Sub

Err_Command50_Click:
MsgBox Err.Description
Resume Exit_Command50_Click

End Sub



how do I write the line

DoCmd.OpenReport stDocName, acPreview

and attach the "josh" filter??

 

[Alexandre]

You would easily find the syntax for OpenReport in Access Help:
docmd.OpenForm "stDocName",acPreview,, josh

 

[slyvsspy]

Thanks, but that didn't work

The only filter you can use on a report is a "query" or a filter "saved as a query". Is there any way I can save the filter that the form has using VISUAL BASIC??

 

[Rich]

Where did you get that information from? You can filter a report from a filtered form, here's one example
Private Sub Command12_Click()
If Not IsNull(Me.Filter) Then
DoCmd.OpenReport "rptCustomers", A_PREVIEW, , Me.Form.Filter
Else
MsgBox "Apply a filter to the form first"
End If
End Sub

 

[Pat Hartman]

I don't think that the filter you are building makes any sense. It seems to have field names and relational operators but no data values to compare to.

josh = "VOTERID>' '"
If Me.PrimaryButton.Value = True Then josh = josh + " and PRIMARY > ' '"

Is going to produce the following string -

"VOTERID>' and PRIMARY > '"

I also think that your structure needs a little work. A single individual cannot be a democrat and a republican and an indipendent at the same time. These categories are mutually exclusive and therefore should be stored in a single field that contains ONE of three (actually more options are necessary) values rather than three separate fields that contain either true or false values.

 

[slyvsspy]

Thanks

Thanks Rich, that was exactly the piece of code I was looking for!!

Pat, you are right, but you would have to look at my whole setup of things to see why I did it that way.

 

[David R]

I'm going to back Pat up, although she doesn't need it

Simply because I'm still thanking her every day for setting me straight last year when I started my first database.

If you'll read over at least the first three forms of database normalization, you'll save countless headaches and hours of time trying to make Access jump through mobius hoops you've created for it.

A voter cannot be Democratic, Republican, AND Independent all at the same time, can they? If not, then there should be one field "Affiliation", with options for each possibility. You'd save a mountain of code, streamline your queries and reports, and shrink the size of your database, all at once. Can you see any problems with this?

Finally, if you think you've put too much work in to change your database now, consider this: I learned database normalization when my database had <500 entries in it. I didn't get my boss' approval to actually finish normalizing it, however, until we had more than 5000 entries in some tables. It took nearly a week and a half of my time, when it would have taken a day or two initially, to fix what had happened. Since then I haven't had to fiddle with the table structure in the slightest though, and I can give her reports and queries at the drop of a hat for even unforseen concepts.

 

[slyvsspy]

Reasoning

I also have a field called "PRECINCT". This stands for the precinct number. Each candidate probably has 10 to 20 precincts in a "district".

So lets say that a candidate wants to pull all the democratic and republican voters from a specific precinct but doesn't want the independents. he would check the "republican" box, the "democratic" box and specify which precinct he wanted.

Then he would apply the filter. Then he would preview the report which is based off the the filter.

Does this make any more sense. This is why I can't make the user choose specifically Republican, Democrat, or Independent. Because they may want to pull records from more than just 1.

 

[David R]

How you store data directly affects how you can draw it out

Consider this:

Your way:
SELECT .... WHERE [Democratic] = -1 OR [Republican] = -1 AND [Independent] = 0

The normalized way:
SELECT .... WHERE [Affiliation] = "Democratic" OR [Affiliation] = "Republican"
OR, if you've stored them in another table where Dems are 1 and Repubs are 2:
SELECT .... WHERE [Affiliation] < 3
OR, if you really just wanted everyone who's not "Independent":
SELECT .... WHERE [Affiliation] <> "Independent" (or 3, or whatever).

Your way is extremely inflexible and will require massive recoding if you need to start tracking the Greens, or the Libertarians, or...

While the proper way takes no more trouble to build, it is faster to update (add an entry to your table of Parties, and maybe change a few query lines), needs less fields and thus less data storage space, and will make you the envy and hero of your peers. Well, perhaps not that last bit...

If you're confused as to how to build a WHERE clause into your report that is this flexible, check this topic: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30739

 

[slyvsspy]

OK!! your right

You are definitely right. But right now I don't have time to change it, so I will within the next week.

Thanks