Print macro

Since no one replied and the topic has dissapeared to the second page I'm going to give it a kick.

Hope no one minds.

Hi Tom

No worries about bumping your post.

I think the complication here is that it's not just about printing records where 'Firma' = 'TSA'. Your form has a number of filters across the top (6 in total?) and I'm guessing that you will want to run any number of these filters and then only print the relevant records. Is that correct? If so, then can I suggest you create a report based on a query where the query gets its criteria from this form? Given the query the report is based on uses the criteria from the form, it will print the same records that are currently displayed.

Here is a page that shows how to build a query that handles null responses in the criteria (i.e. the user can be selective about which criteria they select) :

http://www.fontstuff.com/access/acctut07.htm

But instead of using something like :

[MyCriteria] Or Like [My Criteria] Is Null

you would use something like :

Forms.[MyForm].[MyField] Or Like Forms.[MyForm].[MyField] Is Null

for each filter field from the form. The other advantage is that it is easier to make a better looking report than a form.

So in summary : build a query that picks up the criteria from the form, build a report that is based on the query and change your VBA to print the report instead of the form. If you were to try and do this in VBA, you would need to look at each criteria field in your form and re-create the query anyway using SQL - the way I have suggested will be easier.

HTH, Andrew

Andrew,

First of all, thanks for your reply.

I understand what you mean and it makes more sense then what I had in mind. Looks pretty hard to do for a beginner like me tho but I'll give it a go.

If it doesn't work I'll reply to this topic again

Andrew

I've tried to implement the query but it's a little over the top for me. If you would be so kind to help me on the way?

I made a copy of the query that provides the information for the subform. It contains all the fields that the subform uses.

If I understand you, I need to insert the following to the criteria box:



But how do I need to typ it so that the command is suited for my database.

Forms.[MyForm].[MyField] Or Like Forms.[MyForm].[MyField] Is Null

My subform = frmSubform
My mainform where the subform is located = frmSearch

Hi

You need to enter all of the criteria before you save the query. Under the 'Firma' field, enter the following criteria :

Forms.[frmSearch].[Firma] Or Like Forms.[frmSearch].[Firma] Is Null

This assumes the 'Firma' field on the form also uses the name 'Firma' - if not then you need to use the actual name.

Before you save the query, enter the following under the 'Factuurnummer' field :

Forms.[frmSearch].[Factuurnummer] Or Like Forms.[frmSearch].[Factuurnummer] Is Null

Again, this assumes the field on the form is called 'Factuurnummer' - if not, use the actual field name from the form.

Repeat for the other fields in the query. If you save the query and re-open it in design mode, you will see why I suggested entering all of the criteria before saving the query.

HTH, Andrew

Andrew

I filled in the criteria as you said.



Opened the query in design form and it looks like this:



I made a report based on the qryPrint and made a button to print the report on the frmsearch form. When I press it it shows this:



It asks me to Enter a parameter value for every field I inserted the criteria in. When I typ in TSA for the Firma parameter it will only print the records with TSA in it.

So it's almost working but still not there yet

Double Post

Tom, you could try this alternative approach.

I set this up to print records from a listbox, but the concept is the same. You create a dummy query called qryTemp. A single field from any table will do.
Then you grab the rowsource of the listbox / subform, transfer it to the dummy query, open the query and print it, closing the query when done.

This code is attached to a button's Click event: You will need to set a reference to the Microsoft DAO 3.6 Library (In any module, Tools > References, scroll down and select the reference, click OK and you're done).

{EDIT} To use a subform instead of a listbox, make this change:
{/EDIT}

Denis

Hi Denis

I believe the issue here is that there are half a dozen filters that Tom may or may not populate - hence I took the query approach.

Tom - in your form design, right click the 'Firma' box at the top of your form > click 'Properties' > the first property should be 'Name' - instead of being called something like 'Text1' it should be called 'Firma'. This is what the query is looking for and if it has a different name then the query can't find it, hence the pop up boxes. You need to check the names of all 6 boxes in the top of you form match the names you have used in the query criteria. If the names are the same, then check you have you used the correct form name - per your previous post I took this to be 'frmSearch'.

In summary, the reason for the pop up boxes is because the query can't find the boxes on the form - so you need to check the form name and search box names match those you have in your query criteria.

HTH, Andrew