On Tue, 13 May 2008 15:57:08 -0700 (PDT), franc sutherland wrote:
> 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.
I don't create .pdf do***ents so, unless you are using Access 2007
which does, the best I can do is show you how to output it as .rtf and
you can go from there.
Change the below Where clause to your needs.
Change field and re****t names as needed.
Dim strWhere As String
strWhere = "[LastName] = """ & [LastName] & """"
DoCmd.OpenRe****t "Re****tName", acViewPreview, , strWhere
DoCmd.SelectObject acRe****t, "Re****tName", False
DoCmd.OutputTo acOutputRe****t, "Re****tName", acFormatRTF,
"C:\Do***ents and Settings\Owner.MYCOMPUTER\Desktop\YourDocName.rtf"
The above will output the previewed re****t (which has been filtered on
the LastName field shown on the current form record) as a .rtf file.
I hope this helps.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
|
