Help: Apply Form Filter using checkbox & linked table

[Guest]

I'm pretty new to access coding so bear with me,

I have a call log db with the following tables:
Buyer Info Table:
Has BuyerID as key and has a "check Box field" called BuyerFilter, and some
other stuff like name, phone.

Call Log Table:
Has CallLogID as key, Date, Notes, & BuyerID field which is linked to the
Buyer table.

What I want to do is put a check box in my form that when checked, applies a
filter to the Call Log Form to show only records with Buyers that have the
BuyerFilter Checked in their table.

Here is the query that I made up that seems to work outside the form:
How do I code the form so that it applies this filter?

SELECT CallLog.LogDate, CallLog.BuyerID AS CallLog_BuyerID, CallLog.Notes
FROM Buyers INNER JOIN CallLog ON Buyers.BuyerID = CallLog.BuyerID
WHERE (((Buyers.BuyerFilter)=True));

Thanks
Mikez

 

[Rob Oldfield]

Various ways of doing it... one would be to use a filter...

Add an unbound CheckBox (chkFilt in this example) and, in it's AfterUpdate
event...

if me.chkFilt Then
me.filter="BuyerFilter"
me.filteron=true
else
me.filteron=false
endif

 

[Guest]

I did this and nothing happens.

The field BuyerFilter is not shown anywhere on the form.
It's just a field in the Buyer Table which is linked to the Call Log in the
Relationships.

The Log Form just shows all the records in the Calllog Table.
One of the fields in the Calllog Table is a lookup to the BuyerID.

Do I need to go into the record detail of the form and add a hidden field
that shows if the BuyerID is filtered after the BuyerID is selected?

Thanks,
MikeZz

 

[Rob Oldfield]

Hmmm. The BuyerFilter field does need to be included in the data source of
the form, but it doesn't need to be displayed. What's the RecordSource of
your form? And how does the call log form display? As a standard subform?

 

[Guest]

The RecordSource is the CallLog Table.
There is no sub-form.
It's a ContinuousForm with the callLog records in the Detail Section.
Thanks,
MikeZz

 

[Rob Oldfield]

Hmmm. If you open the form, go to Records, Filter, Advanced... set up the
criteria you want there... then Filter, Apply Filter... then (assuming
that's giving you the filter that you want) go back to design view of the
form... and look at the filter property of the form. Then try...

if me.chkFilt Then
me.filter="whatever the filter property said above"
me.filteron=true
else
me.filteron=false
endif

 

[Guest]

Rob,
Here's my code but it doesn't work.
I get "Enter Paramater Value" Dialag askign for:
Buyers.BuyerFilter next to the input box.

Private Sub chkFilt_Click()
If Me.chkFilt = -1 Then
Me.Filter = "(((([Buyers].[BuyerFilter])=True)))"
Me.FilterOn = True
OrderBy = "CallLog.LogDate, CallLog.LogTime"
Else
Me.FilterOn = False
End If
End Sub

 

[Rob Oldfield]

Strange. I can't see why that's not working for you at all. (Unless you
have the AllowFilters property of the form switched off).

Anyway... another way of doing it is just to modify the underlying SQL of
the record source. So something like...

sql="SELECT CallLog.LogDate, CallLog.BuyerID AS CallLog_BuyerID,
CallLog.Notes "+ _
"FROM Buyers INNER JOIN CallLog ON Buyers.BuyerID = CallLog.BuyerID "

if chkFilt then
me.recordsource=sql+"WHERE (((Buyers.BuyerFilter)=True));"
else
me.recordsource=sql
endif

(...you don't need the = -1 by the way - chkFilt is already either True or
False...)

Rob,
Here's my code but it doesn't work.
I get "Enter Paramater Value" Dialag askign for:
Buyers.BuyerFilter next to the input box.

Private Sub chkFilt_Click()
If Me.chkFilt = -1 Then
Me.Filter = "(((([Buyers].[BuyerFilter])=True)))"
Me.FilterOn = True
OrderBy = "CallLog.LogDate, CallLog.LogTime"
Else
Me.FilterOn = False
End If
End Sub

Hmmm. If you open the form, go to Records, Filter, Advanced... set up the
criteria you want there... then Filter, Apply Filter... then (assuming
that's giving you the filter that you want) go back to design view of the
form... and look at the filter property of the form. Then try...

if me.chkFilt Then
me.filter="whatever the filter property said above"
me.filteron=true
else
me.filteron=false
endif


source
of RecordSource
of Log
in hidden
field BuyerFilter,
and linked
to that
have the
form: