On May 9, 5:54 pm, fredg <fgutk...@[EMAIL PROTECTED]
> wrote:
> On Fri, 9 May 2008 05:17:12 -0700 (PDT), franc sutherland wrote:
> > Hello,
>
> > I have a re****t which I filter using the me.filter command in the
> > OnOpen event.
>
> > Me.Filter = "OrderID=" & Forms!variable_form_name![OrderID]
> > Me.FilterOn = True
>
> > I want to be able to open that re****t, filtered, from different
> > forms. How do I carry the name of the current form into the
> > 'variable_form_name' part of the filter so that the re****t looks at
> > the current form to get the OrderID number and complete the filter?
>
> > I've tried pulling the form name through using
> > application.currentobjectname and putting it into a string, but it
> > didn't like that.
>
> > Many thanks,
>
> > Franc.
>
> That's not the way to filter a re****t.
> Remove whatever you have in the Re****t's Filter property.
> Set FilterOn to No.
>
> If the only difference between the re****ts is how it is filtered, then
> simply use the Where clause argument of the OpenRe****t method to
> filter the re****t differntly.
>
> Let's assume FormA is used to open the re****t filtered on an [ID]
> field.
> You wish to have the re****t filtered according to the record currently
> on the form, the customer number [ID] (a Number datatype).
> Code a Command button's click event:
>
> DoCmd.OpenRe****t "MonthlySales",acViewPreview, , "[ID] = " & Me.[ID]
>
> only records for whichever customer is currently displayed on the form
> will appear in the re****t.
>
> Now lets say you wish to open the same re****t, but this time showing
> the all customers in a specific city (shown in the form's current
> record) but within a specific date range.
> Add 2 unbound control to the same form.
> Set their format to any valid date format.
> Name one control "EnterStart". Name the other control "EnterEnd".
> Code a different button's Click event (or use If .. Then logic on the
> same event to get the correct filter):
>
> DoCmd.OpenRe****t "MonthlySales",acViewPreview, , "[CityID] = " &
> Me.[CityID] & "[DateField] Between #" & Me.[EnterStart] & "# and #" &
> Me.[EnterEnd] & "#"
>
> Enter the dates in the above controls.
> Click the command button.
> The same re****t will now return only records for that [CityID] within
> the entered range of dates.
>
> All of tha above code assumes [ID] and [CityID] are a Number datatyoe
> field.
>
> You have used just 1 form and 1 re****t, filtered according to which
> code is run in the button click event.
>
> See VBA help on Where Clause + Restrict data to a subset of records
> for more information on the correct syntax used for different datatype
> field values.
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
Hi Fred,
Thanks for your thorough reply. Lots of useful tips in there.
I am using the DoCmd.OutputTo command in this instance however, as
part of a process which generates a pdf file directly from a button on
the form.
Is there a way of combining the DoCmd.OutputTo command with the OnOpen
event to replicate the filtering you describe in the DoCmd.OpenRe****t
command Where statement?
Thanks again,
Franc.
|
