Filter Form and Unbound Combo Box

[Guest]

I have a form which has a command button to filter for 'Active' records. The
on click event runs the following simple code:

Dim stSearchCriteria As String
stSearchCriteria = "[MemberStatus] = 'Active'"
DoCmd.ApplyFilter , stSearchCriteria

I have an unbound Combo box (cboZoom) for finding members whose row source:
SELECT qryMembers.ContactID, (qryMembers.LastName & ", " &
qryMembers.FirstName) AS Name FROM qryMembers;

My question is, when the form filter is applied how to show only the
'Active' members in cboZoom.

 

[strive4peace]

how about this:

(assuming that the form recordset and the combobox rowsource are alike
enough to use the same criteria...

'~~~~~~~~~~~~~~~`
dim strSQL as string
strSQL = "SELECT qryMembers.ContactID, " _
& " (qryMembers.LastName & ', ' & qryMembers.FirstName) AS Name " _
& " FROM qryMembers"
if len(nz(me.filter)) > 0 then
strSQL = strSQL & " WHERE " & me.filter
end if
strSQL = strSQL & ";"

me.cboZoom.RowSource = strSQL
me.cboZoom.Requery
'~~~~~~~~~~~~~~~`

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Crystal , thanks. That works. I already had this working before you answered
by using a check box instead of a command button. The command button looks
cleaner on the form. Is there something to trigger a combo box requery when
I take the filter off using the standard toolbar button?

dpj

how about this:

(assuming that the form recordset and the combobox rowsource are alike
enough to use the same criteria...

'~~~~~~~~~~~~~~~`
dim strSQL as string
strSQL = "SELECT qryMembers.ContactID, " _
& " (qryMembers.LastName & ', ' & qryMembers.FirstName) AS Name " _
& " FROM qryMembers"
if len(nz(me.filter)) > 0 then
strSQL = strSQL & " WHERE " & me.filter
end if
strSQL = strSQL & ";"

me.cboZoom.RowSource = strSQL
me.cboZoom.Requery
'~~~~~~~~~~~~~~~`

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have a form which has a command button to filter for 'Active' records. The
on click event runs the following simple code:

Dim stSearchCriteria As String
stSearchCriteria = "[MemberStatus] = 'Active'"
DoCmd.ApplyFilter , stSearchCriteria

I have an unbound Combo box (cboZoom) for finding members whose row source:
SELECT qryMembers.ContactID, (qryMembers.LastName & ", " &
qryMembers.FirstName) AS Name FROM qryMembers;

My question is, when the form filter is applied how to show only the
'Active' members in cboZoom.

 

[strive4peace]

Hi dpj,

you're welcome

I believe the form Current event will get triggered when you do that...
but you would not want to rebuild the combo sql that often... in that
case, add a condition...

if me.cboZoom.RowSource <> strSQL then
me.cboZoom.RowSource = strSQL
me.cboZoom.Requery
end if

then, on the form Current event, call the command button procedure

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Crystal , thanks. That works. I already had this working before you answered
by using a check box instead of a command button. The command button looks
cleaner on the form. Is there something to trigger a combo box requery when
I take the filter off using the standard toolbar button?

dpj

how about this:

(assuming that the form recordset and the combobox rowsource are alike
enough to use the same criteria...

'~~~~~~~~~~~~~~~`
dim strSQL as string
strSQL = "SELECT qryMembers.ContactID, " _
& " (qryMembers.LastName & ', ' & qryMembers.FirstName) AS Name " _
& " FROM qryMembers"
if len(nz(me.filter)) > 0 then
strSQL = strSQL & " WHERE " & me.filter
end if
strSQL = strSQL & ";"

me.cboZoom.RowSource = strSQL
me.cboZoom.Requery
'~~~~~~~~~~~~~~~`

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have a form which has a command button to filter for 'Active' records. The
on click event runs the following simple code:

Dim stSearchCriteria As String
stSearchCriteria = "[MemberStatus] = 'Active'"
DoCmd.ApplyFilter , stSearchCriteria

I have an unbound Combo box (cboZoom) for finding members whose row source:
SELECT qryMembers.ContactID, (qryMembers.LastName & ", " &
qryMembers.FirstName) AS Name FROM qryMembers;

My question is, when the form filter is applied how to show only the
'Active' members in cboZoom.

 

[Guest]

I followed above advice and have following code:

Private Sub Form_Current()
Dim strSQL1, strSQL2 As String
strSQL1 = "SELECT tblCdrls.chrSeqNo, " _
& "tblCdrls.chrTitle , tblCdrls.chrSubtitle " _
& "FROM tblCdrls"
strSQL2 = "ORDER BY tblCdrls.chrSeqNo;"
If Len(Nz(Me.Filter)) > 0 Then
strSQL1 = strSQL1 & " WHERE " & Me.Filter
End If
strSQL1 = strSQL1 & strSQL2

If Me.cboCdlrfind.RowSource <> strSQL1 Then
Me.cboCdlrfind.RowSource = strSQL1
Me.cboCdlrfind.Requery
End If
End Sub

The combo box cboCdrlfind has row source type Table/Query.

When I run the form, after selecting the down arrow in the combo box, I get
a dialog "Enter Parameter Value" Lookup_lngcContractID.chrContract. (I
previously ran the form using that field as a filter, but removed the filter
condition.)

Thanks,

John

 

[Guest]

I don't see a way to edit or delete the post. One problem I noticed I have
is if I let the user create a filter by selection, he can select a field with
the contents of a lookup table. The filter property for the form works, but
when I reference the form filter Me.Filter in a SQL statement, it contains

((Lookup_cboContractID.chrContract="DO 3"))

and this is causing problems.

 

[strive4peace]

Hi John,

when you do
strSQL1 = strSQL1 & strSQL2

you need to make sure strSQL2 starts with a space (it doesn't).
Actually, you don't need strSQL2, you can do this:

'~~~~~~~~~~~~
strSQL1 = "SELECT tblCdrls.chrSeqNo, " _
& "tblCdrls.chrTitle, tblCdrls.chrSubtitle " _
& "FROM tblCdrls "
If Len(Nz(Me.Filter)) > 0 Then
strSQL1 = strSQL1 & " WHERE " & Me.Filter
End If
strSQL1 = strSQL1 & " ORDER BY tblCdrls.chrSeqNo;"

'~~~~~~~~~~~~

you have chrContract coming from more than one place in the recordset
for your form, don't do that -- just get it from one place and then
Access won't preface the filter with a tablename. You want it to be

((chrContract="DO 3"))

not

(Lookup_cboContractID.chrContract="DO 3"))

'~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*