Filtering a Form on Opening

[andycambo via AccessMonster.com]

Hi,

I am trying to open a form (frmCurrentTimes) which has text boxes with DSum
calculations in, which calculates times from a table based on a filter.

What I need to do is open the form, filter the results, calculate the
findings and show the results.

My code for the button is this:

Private Sub btnCurrentTimesMags_Click()

Dim theMatterID As Long

theMatterID = Me.mID

DoCmd.OpenForm "frmCurrentTimes", , , "tFundingMethod = '" & "M" & "' OR
tFundingMethod = '" & "LM" & "' AND tMatterID=" & theMatterID

End Sub

My text boxes are like this

=DSum("[tTravelTime]","tblTimeRecord","tMatterID = " & [mID])

But everytime I open the form the text boxes calculate everything under the
matterID regardless of the the tFundingMethod is. Where I want it to only
calculate times with the funding method "M" and "LM"

Have I got the code correct on the button? Or have I got the text boxes
wrong? Or am I completly wrong?!

Any help is appreciated.

Thanks
Andy.

 

[Marshall Barton]

I am trying to open a form (frmCurrentTimes) which has text boxes with DSum
calculations in, which calculates times from a table based on a filter.

What I need to do is open the form, filter the results, calculate the
findings and show the results.

My code for the button is this:

Private Sub btnCurrentTimesMags_Click()
Dim theMatterID As Long
theMatterID = Me.mID
DoCmd.OpenForm "frmCurrentTimes", , , "tFundingMethod = '" & "M" & "' OR
tFundingMethod = '" & "LM" & "' AND tMatterID=" & theMatterID [snip]
But everytime I open the form the text boxes calculate everything under the
matterID regardless of the the tFundingMethod is. Where I want it to only
calculate times with the funding method "M" and "LM"

You should be aware of the evaluation precedence order of
the various operators (+, /, And, Or, etc). In your case,
the And is evaluated before the Or.

You need to use parenthesis to force the evaluation to the
order that you want:

.... , "(tFundingMethod = '" & "M" & "' OR tFundingMethod =
'" & "LM" & "') AND tMatterID=" & theMatterID

In this case, you can use the IN operator to make the
expression shorter and simpler:

.... , "tFundingMethod IN('M','LM') AND tMatterID=" &
theMatterID

 

[John Spencer]

Perhaps you need to include some parentheses in the criteria string.

The way I read it you would get
ALL records where TfundingMethod = M
plus records where TfundingMethod = LM and tMatterID = TheMatterID

And I think you want only records where tMatterID = M or LM and of
those records TmatterID Must = TheMatterID

DoCmd.OpenForm "frmCurrentTimes", , , "(tFundingMethod = '" & "M" & "'
OR tFundingMethod = '" & "LM" & "') AND tMatterID=" & theMatterID

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[andycambo via AccessMonster.com]

Thanks for both of your replies.

I've never heard of using IN before but that makes the code looks a lot
neater and clearer to read which is always a plus.

I've managed to get it working perfectly now so thank-you for all your help.