Tech Support Guy banner
Status
Not open for further replies.

Solved: Access Reports from Forms

5K views 16 replies 2 participants last post by  Erm 
#1 ·
I have a very basic one table database in Access 2002, which users only use as a store for data. They view the database from a form and perform searches ad hoc using the form filter. I would like to enable them to produce a report of the filtered information to be printed as required. How would I go about this?

I imagine it will be some sort of command button, but none from the wizard seem to suit so if I have to write the code....where do I start? I can do code but no idea on the structure of it to achieve this.

Thanks in advance!
 
#2 ·
Debs, it is not very easy to use the "Filter by Form" as a Report as the Form has to be first saved as a report and then view or printed as a report and then deleted for the next "filter". It is much more efficient to have a "Search" Form. This is used in conjunction with a query that uses the Form input(s) as it's Filter (Criteria). The Report gets it's data from the query, so each time you do you search filter the data is refreshed.
You are correct that you normally use a Command Button to open the Report either in Preview mode or print it directly, but you can also use the Search Field's After update event to hold the VB code to do the same thing.

I can provide examples of "Search Forms" if you want or I can create you a Form/Query/Report.
 
#5 ·
Sorry....I must be having a blonde week (I am blonde so I can get away with saying that!)...I'm just not sure what you mean. I've attached the database in case I haven't explained what I need.

I need users to be able to search by one or more of the fields on the form that loads at start up. eg. Contact type and where all specialisms contain *pi*
 

Attachments

#10 ·
If it can be done then yes as it would be most advantageous in that format....just didn't want you to break your back over it! So only if you have time! Otherwise I'll figure it out eventually.

Thanks a lot for your help so far.
 
#12 ·
Debs, do only want Exact Matches for the data, or do you think you might want "Pattern Matching"?
Pattern Matching allows the user to enter De and it would find all names with De in them, so it would find debs, debby and dennis, enter deb and it would only find debs or debby etc. This can be useful as a short cut.
 
#14 ·
Debs, before I go any further, can you have a look at the attached database, I have created 4 fields up to now. Can you have a look and see if they are doing what you want, 3 are Combos using your data tables, the other is just for the name and isn't Pattern Matching yet.
But I need you to look at the Report because it is just a copy of your Form, but uses the Search Query for it's data.
If you want a conventional report with say 4 line s of data per record ( due to the number of fields) then I will have to change your Table as it does not work well with "lookup" fields.
You can see this if you open the report called "tblExperts Report".
 

Attachments

#15 ·
Since the width fits onto one page it should be fine...I would have never realised it could be done like that...but it isn't too complicated so I will finish it off now. A conventional report is not necessary for this one but why is it that lookups cause a problem? (Or is this an unanswerable question that is the result of Microsoft idiosyncrasies?)...how would it need to be done?

Thanks sooo much for your help and time. Much appreciated.
 
#16 ·
Debs, the reason that the "Lookups" cause a problem is that in the queries, forms and reports they always show up as lookups, i.e. multiple choices. The way to make it much more flexible and not affect the report is to have the Table fields as ordinary text or preferably number fields to store either the City (text) or City ID (number) etc. You then create a query for each lookup table,which you can sort in to order and then use the query as the data source for your Combo boxes on your form.

Are you OK with making the other fields "Pattern matching"?

You use the Like "*" & [forms]![fromname]![fieldname] & "*" in the Criteria row of the Query.
If you want to give the user the ability to have more than one choice at the same time then you put the form selections on the same Criteria Row.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top