Open a form using Mulitple Filters

  • Thread starter Thread starter Paul Fenton
  • Start date Start date
P

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)
 
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.
 
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)
 
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
 
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.

Paul Fenton said:
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.
Click to expand...
Click to expand...
 
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
 
Back
Top