Opening a form with two filters set with VBA code 1

[Harr777]

Hello,

I have a bottum that opens a continous form while applying a filter. What I need to do is apply two filters at the same time. I don't know if its possible. Here's the VBA code that work with just one filter item:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "tblMeterReads subform1"

stLinkCriteria = "[Location]=" & "'" & Me![cboLocation] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Any advise?

Thanks

 

[RoyVidar]

Helpfiles says the where condition for the openform method is a sql where clause without the keyword Where.

[tt]stLinkCriteria = "field1 = '" & me!txtText & "' and field2 = " & Me!txtNumber[/tt]

- so try building a where condition, and be sure where you place or not place the qualifiers (' for text # for dates)

Roy-Vidar

 

[Harr777]

Great, thanks Roy. I could write a book on the potential improvements for Access that I've come accross. Here is a good example. Why does the Button Wizard limit you to one link?!

Thanks

 

[Harr777]

I've been trying to get more than one filter for a form that opens with two primary key values. I need the new form to open with the two values coresponding to two combo boxes on the first form.
Tried the above sugestion, but I am getting nowhere fast.
Can anyone help?

 

[Zion7]

Harr777,

Isn't Roy's suggestion correct?

stLinkCriteria = "[Location]='" & Me![cboLocation] & "' AND [OtherField]='" & Me![OtherCombo] & "'"

Is it possible your single quotes are incorrect?

If you notice, I slightly changed the syntax of 1st criterion (incidental).
But, if other criterion is a number, then as Roy showed, no single quotes.
just for the record, if one of the criterion has an apostrophe, or quotes in it, itself eg; St. John's, Then use this syntax... [Location]=""" & cboLocation & """".

2 double quotes, for every single quote.

Hope this helps, Good Luck!

 

[Harr777]

I tried this code:

stLinkCriteria = "[SerialNumber]='" & Me![cboEditMeterRead] & "' And [ReadPeriod] = '" & Me![cboReadPeriod] & "'"

When I click on the button that runs this code (which should open a new form with the applied filters) I get an "Open Form Action was Cancelled" message.

I would really appreciate any further advice.

 

[RoyVidar]

Both of those are text (as indicated by the single quotes) and the combination of them exists?

Roy-Vidar

 

[Harr777]

SerialNumber is text and ReadPeriod is a number. Should this have a bearing on the code?

As always- Thanks alot

 

[RoyVidar]

Yes. An expample, and a brief explanation is found in my first reply (text and numeric).

Roy-Vidar

 

[Zion7]

So, the result should be...

stLinkCriteria = "[SerialNumber]='" & Me![cboEditMeterRead] & "' And [ReadPeriod] =" & Me![cboReadPeriod]

 

[Harr777]

You nailed it, Zion7. That code worked perfectly. The quotation system is still a little confusing to me. I'm going to have to spend some more time trying understand it better.
Thanks