DoCmd.ShowAllRecords AND DoCmd.ApplyFilter issues

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ted - 12 Sep 2005 20:15 GMT

according to my best guesstimations the VBA code (in a2k app'n) should serve
to filter those records meeting the criterion

Private Sub fltCRA_AfterUpdate()
If Me.fltCRA = "<All>" Then
DoCmd.ShowAllRecords
Else
DoCmd.ApplyFilter , "[CRA] = ' " & Me![fltCRA] & " ' "
End If
End Sub

the "fltCRA" unbound combobox is in the form header of my form named
"Screening Log (Edit Only)" and it uses as its row source the query named
lkpFormCRA (in SQL):

SELECT DISTINCT [Screening Log].CRA FROM [Screening Log] UNION SELECT
"<All>" FROM [Screening Log];

my reason for writing is because of the unexpected behavior which ensues
when i select one of the values (other than '<All>') in the combobox.....i.e.
there are no records selected, there are no controls appearing in the details
section of the form, and the controls which are in the form header section
(fltCRA, LastName, FirstName and MI) are all blank??!!!?

anyone have a theory as to what's up with that?

-ted

Reply to this Message

Douglas J Steele - 12 Sep 2005 20:21 GMT

Ted: Do you actually have the spaces between the single and double quotes?
Remove them!

As I said in my original post, the second time (with the spaces) was
intended for clarity only.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> according to my best guesstimations the VBA code (in a2k app'n) should serve
> to filter those records meeting the criterion
[quoted text clipped - 23 lines]
>
> -ted

Reply to this Message

Kevin K. Sullivan - 12 Sep 2005 20:26 GMT

The line:

> DoCmd.ApplyFilter , "[CRA] = ' " & Me![fltCRA] & " ' "

includes a space character before and after the filtered value. Not
surprisingly, none of your records match this exact pattern. Someone may
have included these spaces for code readability *in the newsgroup*, but
you need to actually code:
DoCmd.ApplyFilter , "[CRA] = '" & Me![fltCRA] & "'"

This assumes that "CRA" is the name of a text field in the underlying
table. If it is a numeric field, drop the apostrophes altogether.

HTH,

Kevin

> according to my best guesstimations the VBA code (in a2k app'n) should serve
> to filter those records meeting the criterion
[quoted text clipped - 23 lines]
>
> -ted

Reply to this Message

Ted - 12 Sep 2005 20:55 GMT

doug and kevin (in alphabetic order), the adjusted (per your astute
ophthalmological observations) i'm using is as follows:

Private Sub fltCRA_AfterUpdate()
If Me.fltCRA = "<All>" Then
DoCmd.ShowAllRecords
Else
DoCmd.ApplyFilter , "[CRA] = '" & Me![fltCRA] & "'"
End If
End Sub

what's happening now is that i'm seeing a window titled "Enter Parameter
Value" requesting a value for

Forms!Screening Log (Edit

which almost looks as though it's truncated ("Screening Log (Edit Only)" is
the form's name).

so, i guess i'm almost back where i began.

-ted

ps: CRA is a text field in the query underlying "Screening Log (Edit Only)"

> The line:
> > DoCmd.ApplyFilter , "[CRA] = ' " & Me![fltCRA] & " ' "
[quoted text clipped - 38 lines]
> >
> > -ted

Reply to this Message

Douglas J. Steele - 13 Sep 2005 01:00 GMT

See whether the following works:

Private Sub fltCRA_AfterUpdate()
If Me.fltCRA = "<All>" Then
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[CRA] = '" & Me![fltCRA] & "'"
Me.FilterOn
End If
End Sub

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> doug and kevin (in alphabetic order), the adjusted (per your astute
> ophthalmological observations) i'm using is as follows:
[quoted text clipped - 71 lines]
>> >
>> > -ted

Reply to this Message

Ted - 13 Sep 2005 14:15 GMT

doug, sorry about the delay in responding but i'd guess your response came
'after hours'; anyway, i subbed your code for the other one and found i
needed to explicity set .FilterOn = true in the Else part of the if clause;
with that out of the way, i am still getting the parameter query. i thought
i'd check the properties of the "Screening Log (Edit Only)" mainform we're
talking about afterwards and found the following string in the filter
property:

[Forms]![Screeing Log (Edit Only)]![DaysView].[Form]![Date_Of_Visit]<=Date()

i suspect this has something to do with the fact there's a "DaysView" child
subform on this mainform which had data i am filtering via a cmd button i
created on the main one, the code behind which is as follows:

Private Sub FilterDates_Click()
On Error GoTo Err_FilterDates_Click

With Me.[DaysView].Form
If .FilterOn Then
.FilterOn = False 'Turn the filter off.
lngGreen = RGB(0, 150, 0)
Me.FilterDates.ForeColor = lngGreen
Else
.Filter = "[DateOfVisit] >= Date()" ' <-----NOTE THIS LINE HERER
.FilterOn = True
lngRed = RGB(225, 0, 0)
Me.FilterDates.ForeColor = lngRed
End If
End With

If Me.FilterDates.ForeColor = lngRed Then
Me.FilterLbl.Visible = True
Me.Close.Visible = False
Me.Add_Record.Visible = False
Me.NavigationButtons = False
Else
Me.FilterLbl.Visible = False
Me.Close.Visible = True
Me.Add_Record.Visible = True
Me.NavigationButtons = True
End If

Exit_FilterDates_Click:
Exit Sub

Err_FilterDates_Click:
MsgBox Err.description
Resume Exit_FilterDates_Click

End Sub

since i count myself a newbie in these arcane matters, i guess that even
though i'm not 'activating' the filter dates cmdbutton when i click the
fltCRA button that the date filter is getting stuffed into the filtering role
or something. this seems kind of an impossibility and i may need to rethink
the approach.

best,

-ted

> See whether the following works:
>
[quoted text clipped - 83 lines]
> >> >
> >> > -ted

Reply to this Message

Douglas J. Steele - 13 Sep 2005 22:27 GMT

Sorry, that was a typo on my part: it should definitely have been = True.

The filter you're showing in the other routine should be:

.Filter = "[DateOfVisit] >= " & Format(Date(), "\#mm\/dd\/yyyy\#")

If you need to filter on both at the same time, you could try:

Private Sub fltCRA_AfterUpdate()
If Me.fltCRA = "<All>" Then
Me.Filter = vbNullString
Me.FilterOn = False
Else
If Len(Me.Filter) > 0 Then
Me.Filter = Me.Filter & " AND " & _
"[CRA] = '" & Me![fltCRA] & "'"
Else
Me.Filter = "[CRA] = '" & Me![fltCRA] & "'"
End If
Me.FilterOn = True
End If
End Sub

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> doug, sorry about the delay in responding but i'd guess your response came
> 'after hours'; anyway, i subbed your code for the other one and found i
[quoted text clipped - 158 lines]
>> >> >
>> >> > -ted

Reply to this Message

Ted - 14 Sep 2005 16:04 GMT

hi doug,

during the hiatus up to your latest reply, i did some snooping about and
found an example in (all of places) the Help documentation, under
RecordSource property (example) which i developed a bit and tried to
integrate into the body of my VBA code. the finished code is under:

Private Sub fltCRA_AfterUpdate()
Dim strNewRecord As String
If Me.fltCRA = "<All>" Then
strNewRecord = "SELECT * FROM [Screening Log Query For Forms (Revised)]"
Me.RecordSource = strNewRecord
Else
Me.fltDM.Visible = False
Me.Label102.Visible = False
Me.Label104.Visible = False
strNewRecord = "SELECT * FROM [Screening Log Query For Forms (Revised)]" _
& " WHERE [CRA] = '" & Me.fltCRA.Value & "'"
Me.RecordSource = strNewRecord
End If
End Sub

so as you can see, i bypass the filter and instead use SQL to program the
'flavor' of the recordsource going into the main form!

there is another option the user has that lets them 'filter' the mainform
for the value of the 'Data Manager':

Private Sub fltDM_AfterUpdate()
Dim strNewRecord As String
If Me.fltDM = "<All>" Then
strNewRecord = "SELECT * FROM [Screening Log Query For Forms
(Revised)]"
Me.RecordSource = strNewRecord
Else
strNewRecord = "SELECT * FROM [Screening Log Query For Forms
(Revised)]" _
& " WHERE [Data Manager] = '" & Me.fltDM.Value & "'"
Me.RecordSource = strNewRecord
Me.fltCRA.Visible = False
Me.Label104.Visible = False
End If
End Sub

and so far, it's working like a charm!

what i expect my user will want once they get their hands on this is the
wherewithall to controlt he sorting order of the subsetted records -- i know
i would! but i guess that's the topic of another posting.

all the best,

-ted

> Sorry, that was a typo on my part: it should definitely have been = True.
>
[quoted text clipped - 181 lines]
> >> >> >
> >> >> > -ted

Reply to this Message

Larry Linson - 13 Sep 2005 01:55 GMT

I've never used parentheses in a form name, but seems to me that it would be
easy to confuse Access because parentheses have meaning in many different
places. That may not be your problem, this time, but if it were mine I'd
change it to something like frmScreeningLogEdit.

And, too, filters IMNSHO are "quirky" enough that I don't use them very
often.

Larry Linson
Microsoft Access MVP

> doug and kevin (in alphabetic order), the adjusted (per your astute
> ophthalmological observations) i'm using is as follows:
[quoted text clipped - 71 lines]
>> >
>> > -ted

Reply to this Message