Query Filter (1 Viewer)

D

diasflac

Registered User.

Local time

Today, 04:59

Joined

May 13, 2013

Messages

45

• May 24, 2013

• #1

Hi,

I am using a query as part of a mail merge, there are two forms that use the query, create and view, both forms use the same table. When I click the print button the query runs, and mail merges in a word document.

What I need is a filter on the query which only shows the record currently open on which ever of the forms is open, so the mail merge only happens for the 1 record you want.

Is there any way to do this?

 

A

AlexHedley

Registered User.

Local time

Today, 04:59

Joined

Aug 28, 2012

Messages

171

• May 25, 2013

• #2

You could either set your Form to have a Record Source based on a Table and then have a list of records with a button to open the specified Record

Code:Copy to clipboard

DoCmd.OpenForm "frmName", , , "ID=" & Forms!frmName2!IDControl

Or use a parameter Query and base it on a value from the Form.

http://www.599cd.com/tips/access/forms-formname-field-notation/?key=AlexForumAF

 

D

diasflac

Registered User.

Local time

Today, 04:59

Joined

May 13, 2013

Messages

45

• May 28, 2013

• #3

Thanks Alex,

I've used the link and managed to get it working for one of the forms. The only problem is there are two forms which use this query both using the same reference number. Is there anyway to make the !Form!FormName!FieldName work for 2 forms at once?

I tried an OR statement with it but it just doesn't work.

 

A

AlexHedley

Registered User.

Local time

Today, 04:59

Joined

Aug 28, 2012

Messages

171

• May 29, 2013

• #4

Where is this 'reference number'.
Say this was on your Main Form, the other two Forms could be based on a Query that uses the Main Form Criteria.

If not the quickest way is to just create two Queries and have separate criterias.

 

D

diasflac

Registered User.

Local time

Today, 04:59

Joined

May 13, 2013

Messages

45

• May 30, 2013

• #5

AlexHedley said:

Where is this 'reference number'.
Say this was on your Main Form, the other two Forms could be based on a Query that uses the Main Form Criteria.

If not the quickest way is to just create two Queries and have separate criterias.

Click to expand...

Thanks Alex, I was afraid you were going to say that. The query is part of a mail merged letter, and a big one at that (44 fields now), the filter means a letter is produced for that specific record. This query has had all sorts of problems (I have a few threads on it), creating another one isn't a problem but I'd have to have a seperate document template and mail merge again etc, which was what I hoping to avoid.

Thank you for your help, the filter works and I best get started on this new query and document .