PC Review


Reply
Thread Tools Rate Thread

How To Apply Filter To Form Using VBA

 
 
MikeC
Guest
Posts: n/a
 
      21st Oct 2004
I'm developing a time sheet form in AXP that filters records using two
unbound text boxes to define the upper and lower limits of a date range.
The form itself is bound to a recordsource query. When I attempt to apply
the filter using the below code, the form fails to display any records at
all.

The below code runs in a command button's On Click event procedure.

With Me
!txtWkStart = DateAdd("d", -7, !txtWkStart)
!txtWkEnd = DateAdd("d", -7, !txtWkEnd)
.FilterOn = True
DoCmd.ApplyFilter , "[TMS_DATE] >= " & DateValue(!txtWkStart) & "
And [TMS_DATE] <= " & DateValue(!txtWkEnd)
End With

Below is an actual text string that was passed to the above
DoCmd.ApplyFilter method:

[TMS_DATE] >= 10/18/2004 And [TMS_DATE] <= 10/24/2004

In addition, I have also tried using the below lines immediately after
executing the above code, but the form continues to display zero records:

DoCmd.RunCommand acCmdApplyFilterSort
Me.Requery

No error messages display at all. I have stepped the code through the
debugger and it appears to execute just fine, except for the part where it's
supposed to display my data. ;-)

Also, if I toggle-off the "Apply Filter" button using the built-in tool bar,
then *all* records display.

I just can't seem to filter today. What am I missing?



 
Reply With Quote
 
 
 
 
Rex Gillit
Guest
Posts: n/a
 
      21st Oct 2004
Your dates need to be delimited by the symbol "#" to tell Access that they
are dates. Try this:

DoCmd.ApplyFilter , "[TMS_DATE] >= #" & DateValue(!txtWkStart) & _
"# And [TMS_DATE] <= #" & DateValue(!txtWkEnd) & "#"

"MikeC" <(E-Mail Removed)> wrote in message
news:L2Vdd.17055$(E-Mail Removed)...
> I'm developing a time sheet form in AXP that filters records using two
> unbound text boxes to define the upper and lower limits of a date range.
> The form itself is bound to a recordsource query. When I attempt to apply
> the filter using the below code, the form fails to display any records at
> all.
>
> The below code runs in a command button's On Click event procedure.
>
> With Me
> !txtWkStart = DateAdd("d", -7, !txtWkStart)
> !txtWkEnd = DateAdd("d", -7, !txtWkEnd)
> .FilterOn = True
> DoCmd.ApplyFilter , "[TMS_DATE] >= " & DateValue(!txtWkStart) & "
> And [TMS_DATE] <= " & DateValue(!txtWkEnd)
> End With
>
> Below is an actual text string that was passed to the above
> DoCmd.ApplyFilter method:
>
> [TMS_DATE] >= 10/18/2004 And [TMS_DATE] <= 10/24/2004
>
> In addition, I have also tried using the below lines immediately after
> executing the above code, but the form continues to display zero records:
>
> DoCmd.RunCommand acCmdApplyFilterSort
> Me.Requery
>
> No error messages display at all. I have stepped the code through the
> debugger and it appears to execute just fine, except for the part where

it's
> supposed to display my data. ;-)
>
> Also, if I toggle-off the "Apply Filter" button using the built-in tool

bar,
> then *all* records display.
>
> I just can't seem to filter today. What am I missing?
>
>
>



 
Reply With Quote
 
 
 
 
MikeC
Guest
Posts: n/a
 
      21st Oct 2004
That solved it. Thanks.

"Rex Gillit" <(E-Mail Removed)> wrote in message
news:ZpVdd.8391$(E-Mail Removed)...
> Your dates need to be delimited by the symbol "#" to tell Access that they
> are dates. Try this:
>
> DoCmd.ApplyFilter , "[TMS_DATE] >= #" & DateValue(!txtWkStart) & _
> "# And [TMS_DATE] <= #" & DateValue(!txtWkEnd) & "#"
>
> "MikeC" <(E-Mail Removed)> wrote in message
> news:L2Vdd.17055$(E-Mail Removed)...
>> I'm developing a time sheet form in AXP that filters records using two
>> unbound text boxes to define the upper and lower limits of a date range.
>> The form itself is bound to a recordsource query. When I attempt to
>> apply
>> the filter using the below code, the form fails to display any records at
>> all.
>>
>> The below code runs in a command button's On Click event procedure.
>>
>> With Me
>> !txtWkStart = DateAdd("d", -7, !txtWkStart)
>> !txtWkEnd = DateAdd("d", -7, !txtWkEnd)
>> .FilterOn = True
>> DoCmd.ApplyFilter , "[TMS_DATE] >= " & DateValue(!txtWkStart) & "
>> And [TMS_DATE] <= " & DateValue(!txtWkEnd)
>> End With
>>
>> Below is an actual text string that was passed to the above
>> DoCmd.ApplyFilter method:
>>
>> [TMS_DATE] >= 10/18/2004 And [TMS_DATE] <= 10/24/2004
>>
>> In addition, I have also tried using the below lines immediately after
>> executing the above code, but the form continues to display zero records:
>>
>> DoCmd.RunCommand acCmdApplyFilterSort
>> Me.Requery
>>
>> No error messages display at all. I have stepped the code through the
>> debugger and it appears to execute just fine, except for the part where

> it's
>> supposed to display my data. ;-)
>>
>> Also, if I toggle-off the "Apply Filter" button using the built-in tool

> bar,
>> then *all* records display.
>>
>> I just can't seem to filter today. What am I missing?
>>
>>
>>

>
>



 
Reply With Quote
 
New Member
Join Date: Jul 2010
Posts: 2
 
      23rd Jul 2010
Does anyone have a module or vba for filtering a form, using a subform's table? The main form is unbound and the subform is using a table as it's recordsource. I want to allow users to filter the subform's data using text box controls. Without using command buttons(I hate command buttons). I was using Allen Browne's AppFindAsUType module, for a bound form that I worked on, but it does not work on an unbound main form with a subform.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
"Apply Filter" and "Filter by Form" shortcut JNB Microsoft Access Getting Started 0 9th Jul 2008 04:12 PM
using a form to apply filter on a different form (HOW) =?Utf-8?B?c2t1bG1hdA==?= Microsoft Access Form Coding 0 29th Jun 2005 10:55 PM
Filter by Form/Apply Filter Command Buttons Chris Microsoft Access Forms 1 9th Jan 2004 04:22 PM
Filter on Form and Apply Filter jimtyson Microsoft Access Forms 0 7th Nov 2003 03:17 PM
Filter by form and Apply Filter JF Bouthillier Microsoft Access VBA Modules 4 9th Sep 2003 08:34 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:02 PM.