Filter question

[Ron]

Hi All,

I have a form based on a query (qryclientsbyname) that includes all
active records in the applicable table (tblClients).

I have 2 unbound listbox controls on that form that are based on
different queries to find a specific client, either by alpha lookup by
name (qryClientLookupName) or numerical by account number
(qryClientLookupNumber)). Once selected, that client pops into the
form and can be worked on (data changed, input of transactions, etc).

All works great.

I've recently completed a bunch of new files/queries for adding
clients from a file from another source (old dbase program,
actually...that they're still using on a daily basis for another
purpose). That all works great. But, since they don't have ALL the
data fields in the old files that we need in the access db, I came up
with a filter on the main form so, once set, it's only showing the
files transfered, say, today...based on date of transfer that I save
with the client file (can use this for another couple purposes as
well). That whole process works great.

However, when they pop into that unbound control to look up either the
last name or client number for someone within the filtered info, they
get ALL the records. I want to limit those 2 unbound controls to the
filtered clients.

Is there a way, depending on if I have a filter set on the form, for
my unbound fields to automatically choose which query to use for
themselves? So, if a filter is not set, the unbound field will use
qryClientLookupName and if there is one set, it'll use
qryClientLookupNameFiltered or some such? Or, can I put an IIf
statement in the query itself and either use the TransferDate or not,
depending on whether there's a filter currently set or not?

I've tried both these approaches and I get NO list....so obviously
I've not done it correctly, if it can even be done.

Suggestions?

TIA
ron

 

[Jeanette Cunningham]

Hi Ron,
Use a where clause for the queries for the listboxes.
Instead of using a saved query, write the query in VBA.
In the VBA for the query, it is easy to change the where clause to suit the
form's filter.

You could do a routine like this

Private Sub MyListBoxRowSource()
Dim strSQL as String
Dim strWhere as String

strWhere = "[DateField'sName] = "#" & Me.[DateControl'sName] & "#"

strSQL = "Select yadda, yadda " _
& "FROM some table " _
& strWhere

Debug.Print strSQL
Me.ListBoxName.RowSource = MyListBoxRowSource
End Sub



For a variation, you could test the form's filter.
If Len(Me.Filter) >0 Then
strWhere = "[theField] = " & Me.[TheControlName] & ""
Else
strWhere = ""
End If

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ron]

Thanks for the reply, Jeanette.

Since I had NO idea where to put your Private Sub routine (although I
did figure out how to copy the sql from the preset query I had already
used to the sub routine which is nice to know), I ended up using a
variation of your "variation" in the onCurrent property of the form.
I had to change it to:

me.listbox.rowsource = "qryNormal"
If me.FilterOn then
me.listbox.rowsource = "qryWithFilter"
End If

For some reason, if I used your:

If Len(Me.Filter) >0 Then

The rowsource was always the qryWithFilter rather than qryNormal.

But, with the me.FilterOn test, it's working great.

Thanks again,
ron

Hi Ron,
Use a where clause for the queries for the listboxes.
Instead of using a saved query, write the query in VBA.
In the VBA for the query, it is easy to change the where clause to suit the
form's filter.

You could do a routine like this

Private Sub MyListBoxRowSource()
Dim strSQL as String
Dim strWhere as String

strWhere = "[DateField'sName] = "#" & Me.[DateControl'sName] & "#"

strSQL = "Select yadda, yadda " _
& "FROM some table "  _
& strWhere

Debug.Print strSQL
Me.ListBoxName.RowSource = MyListBoxRowSource
End Sub

For a variation, you could test the form's filter.
If Len(Me.Filter) >0 Then
    strWhere = "[theField] = " & Me.[TheControlName] & ""
Else
    strWhere = ""
End If

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi All,

I have a form based on a query (qryclientsbyname) that includes all
active records in the applicable table (tblClients).

I have 2 unbound listbox controls on that form that are based on
different queries to find a specific client, either by alpha lookup by
name (qryClientLookupName) or numerical by account number
(qryClientLookupNumber)).  Once selected, that client pops into the
form and can be worked on (data changed, input of transactions, etc).

All works great.

I've recently completed a bunch of new files/queries for adding
clients from a file from another source (old dbase program,
actually...that they're still using on a daily basis for another
purpose).  That all works great.  But, since they don't have ALL the
data fields in the old files that we need in the access db, I came up
with a filter on the main form so, once set, it's only showing the
files transfered, say, today...based on date of transfer that I save
with the client file (can use this for another couple purposes as
well).  That whole process works great.

However, when they pop into that unbound control to look up either the
last name or client number for someone within the filtered info, they
get ALL the records.  I want to limit those 2 unbound controls to the
filtered clients.

Is there a way, depending on if I have a filter set on the form, for
my unbound fields to automatically choose which query to use for
themselves?  So, if a filter is not set, the unbound field will use
qryClientLookupName and if there is one set, it'll use
qryClientLookupNameFiltered or some such?  Or, can I put an IIf
statement in the query itself and either use the TransferDate or not,
depending on whether there's a filter currently set or not?

I've tried both these approaches and I get NO list....so obviously
I've not done it correctly, if it can even be done.

TIA
ron- Hide quoted text -

- Show quoted text -