Filtering a report made directly from a table? (1 Viewer)

[jimtimber]

HI,

I've created a report which has 4 subreports linked to it via a ClientID. The problem is it prints 2000 reports for the 2000 different ClientIDs. I want, went the button is pressed, for it to ask what ClientID the users wants to print the report for.

I know how to do this in a query based report via criteria, but have no idea how to get this to work when the reports been directly made from a table.

I've been googling all morning and can't find an answer. Is it possible?

Thanks!

Jim

 

[vbaInet]

I know how to do this in a query based report via criteria, but have no idea how to get this to work when the reports been directly made from a table.

You already know best practice so do it the way you know it.

 

[jimtimber]

But the report I've made has taken me days and i'll have the rewrite the whole thing. Is there not something in VBA I code add?
Cheer,

J

 

[vbaInet]

The subreports are linked to the parent report via Client ID right? Filter the parent report.

By the way, if you followed the wizard and created a subreport with a table source, doesn't it automatically create a subreport?

 

[jimtimber]

Thanks VBAInet,

I'm not sure how to filter on the main report. Usually I'd add:

"Like [forms]![Example]![ClientID]"

to the criteria of a query, but because there isn't a query, there's no where to put this I want when the user clicks the button, it opens the form and they choose the clientid and the report runs just for that clientid.

R.e. the subreports, When I created the subreports because I had "use control wizards" selected it asked me what to link it to and I chose Client ID. This seems to have worked.

 

[vbaInet]

Using the Filter and FilterOn properties of the report, I'm sure you've come across this technique before?

 

[jimtimber]

Using the Filter and FilterOn properties of the report, I'm sure you've come across this technique before?

Ha, no! I've just googled it and it says there should be a togglefilter option on the data tab in the report properties, but there isn't?

EDIT: found it I think (see the attached), not sure what to do from this point.

 

[vbaInet]

Ok, you can use that otherwise when you're opening the report you can also set the WHERE clause in the OpenReport method:

Have a look at this link (just swap OpenForm for OpenReport, the parameters are the same):
http://baldyweb.com/wherecondition.htm

 

[jimtimber]

Ok, you can use that otherwise when you're opening the report you can also set the WHERE clause in the OpenReport method:

Have a look at this link (just swap OpenForm for OpenReport, the parameters are the same):
http://baldyweb.com/wherecondition.htm

Thanks for that. I don't understand lots about VBA so i'll just rewrite the report based on a query. Its got to be user friendly too and this will be too complex for end-users to figure out.

Thanks for your help though!

J

 

[vbaInet]

How does this affect your users?

 

[vbaInet]

And even though you recreate the subreport you still cannot filter it unless using the methods mentioned, or doing it in the Record Source directly. Filtering a subreport is different from how you filter a subform.

 

[jimtimber]

How does this affect your users?

They are used to clicking a button on a form to open a Form with a dropdown box for them to choose a record from before running a report. The form I made for this, based on ClientID, also included First and Last name to help them find who they want.

I've started recreating the report based on a new query. It seems a bit long winded to have to create a query when I am using all fields from a table and am not doing any formatting on the values in there...plus VBA confuses me!

 

[vbaInet]

Isn't that what you as a developer is meant to present them with? You do all the hard work and make their lives easy.

The code explained in pbaldy's website doesn't ask you to recreate anything. All you do is pass the relevant WHERE part when opening the report.

 

[jimtimber]

Isn't that what you as a developer is meant to present them with? You do all the hard work and make their lives easy.

The code explained in pbaldy's website doesn't ask you to recreate anything. All you do is pass the relevant WHERE part when opening the report.

Ha, I'm a novice developer, my colleague had a heart attack and I've got to look after her system until she returns. I tend to copy paste her VBA and just change the relevant fields.

Just to confirm, I can't do what I am wanting to do?

 

[vbaInet]

Just to confirm, I can't do what I am wanting to do?

The link I gave you is how you do it. If there was something you don't understand ask specifics rather than throw it out the window and say it's too complex.

 

[jimtimber]

Sorry, it's been a long weekend of :banghead:

Ok, I need help with getting the part of PBaldy's link to work. I've never done this before so not sure what to do first.

Is that better?

 

[vbaInet]

Lots of enthusiasm!!

You want to filter by just one field right?

 

[jimtimber]

Yes, that's right. All report/subreports are linked via ClientID.

In an ideal world, I'd like that the form (Frm_CLientBox) I have created that has the CLientID, Lastname, FirstName come up when the user clicks on the report so they can filter from that point.

 

[vbaInet]

You can filter on any amount of fields you desire, so before we proceed do you want to work on the ideal or stick to ClientID?

By the way, what's Frm_ClientBox? Is that supposed to be the form you're using to get the ClientID from the user and open the report?

 

[jimtimber]

Actually, Access created an "ID" as primary key. This is due to there being 8 duplicate ClientIDs. So what is linking them all is the "ID" field.