Open a form using Mulitple Filters

[Paul Fenton]

I have a form where each record has, among others, a JOBID field, a
CONTRACTOR field and a TASK field. The JobID will be the same for
every record, but there could be a variety of Contractors and Tasks.

I have a button on this form, with each record, and I want to open a
second form which will show all the task records for this JOBID and
this CONTRACTOR. Here's the OnClick event for that button.

stLinkCriteria = "[JobID]=" & "'" & Me![JobID] & "'"
stLinkCriteria = stLinkCriteria & " and [contractor]=" & "'" &_
Me![txtContractor] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

I get an error here, "The OpenForm Action was Cancelled" and the form
won't open.

If I use just one criteria, JobID for example, the new form opens
fine.


Paul Fenton
(e-mail address removed)

 

[Allen Browne]

What type of field is JobID? Contractor? If Number, drop the extra quotes.
They are needed only for Text type fields.

stLinkCriteria = "([JobID] = "& Me![JobID] & _
") AND ([contractor] = " & Me![txtContractor] & ")"

You will need to check the 2 values are not Null, else that string will be
mal-formed.

 

[Paul Fenton]

Allen, thank you for the response.

Both fields are text. I tried your suggestion but got an error that
it was not correct syntax. My original string of

stLinkCriteria = "[JobID]=" & "'" & Me![txtJobid] & "'"
stLinkCriteria = stLinkCriteria & " And [contractor] = " & "'" &_
Me![txtContractor] & "'"

resolves to:

[JobID]='03156' And [contractor] = 'Pedro Villa'

Or, if I add the parens (), it resolves to

([JobID]='03156') And ([contractor] = 'Pedro Villa')

In both instances, I get the same error, "The OpenForm Action was
Cancelled."


Paul Fenton
(e-mail address removed)

 

[Paul Fenton]

Also, if I use just one parameter, such as

stLinkCriteria = "[JobID]=" & "'" & Me![txtJobid] & "'"

I get all the records for the correct Job ID. Same thing if I just
use the contractor name.


Paul Fenton

 

[Allen Browne]

Okay. Your string looks correct for Text fields.

Try opening the form without a WhereCondition.
Then apply the same string as the form's Filter, and set the FilterOn
property to True. If this works, the problem is elsewhere. If it fails, see
which part fails, e.g. is the Contractor field available in the form, or
only the ContractorID?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thank you for the response.

Both fields are text. I tried your suggestion but got an error that
it was not correct syntax. My original string of

stLinkCriteria = "[JobID]=" & "'" & Me![txtJobid] & "'"
stLinkCriteria = stLinkCriteria & " And [contractor] = " & "'" &_
Me![txtContractor] & "'"

resolves to:

[JobID]='03156' And [contractor] = 'Pedro Villa'

Or, if I add the parens (), it resolves to

([JobID]='03156') And ([contractor] = 'Pedro Villa')

In both instances, I get the same error, "The OpenForm Action was
Cancelled."


Paul Fenton
(e-mail address removed)

What type of field is JobID? Contractor? If Number, drop the extra quotes.
They are needed only for Text type fields.

stLinkCriteria = "([JobID] = "& Me![JobID] & _
") AND ([contractor] = " & Me![txtContractor] & ")"

You will need to check the 2 values are not Null, else that string will be
mal-formed.

 

[Paul Fenton]

Yes, that works now. Instead of trying to filter on the Contractor's
NAME, I filtered on his ID, a number field. When I substituted the
VendorID, everything worked as it should.

Thank you so much for your help.


Paul Fenton