use combo box to filter data in a form

[Guest]

I'm using a combo box to filter deliverys by group name. The form is based
on a select query which pulls all deliveries within a specific date range. I
have the combo box in the form header pointing to the Group Name table
(Account No, Group Name). In the After Update event procedure I have the
following code:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[Group Name] = " & Me.cboFilterGroup
Me.FilterOn = True
End If
End Sub


In the Form properties the record source is pointing to the select query
showing, date, account #, customer name, state, group name (with criteria set
to [Forms]![DeliverybyGroup]![cboFilterGroup] or
[Forms]![DeliverybyGroup]![cboFilterGroup] Is Null), and delivery time.

At first I had [Forms]![DeliverybyGroup]![cboFilterGroup] as the criteria
in the Group Name Table. When I open the Form I am prompted to enter the date
range (and that's what its suppose to do), but when I click on the group name
drop down list NOTHING happens. The group name is not filtered to select the
records requested. I also notice when testing that any group name previously
selected shows in the Form Properties/Filter field.

I do have a command button in the header to clear and select a new group name.

 

[Allen Browne]

Make sure cboFilterGroup is unbound (i.e. nothing in its Control Source
property.)

You don't need both the criteria in the query and the code in its
AfterUpdate event. Remove the criteria if you want to use the code.

Examine the properties of the combo to ensure the filter is being set as you
expect. The Bound Column property lets you know which column in the combo's
RowSource is understood as its Value. It hat to match the data type of your
[Group Name] field.

 

[Guest]

Thanks Allen for assisting me on this. I think I may have the queries set up
incorrectly.

First, I have a select qry named Delivery Summary ALL (built on the Invoice
tble and the Delivery Time table). Then I have another select qry named
Delivery Group (built on the Group Name table and Delivery Summary All qry).
Next, I created the Delivery By Group Form (build on the Delivery Group qry
and the unbound combo box rowsource points to the Group Name table as its
dropdown list to filter on the Account # and Group/Account Name.

When I click on the dropdown list to filter by Group Name nothing happens.
My question is, the Auto Form is linked to the Delivery Group select qry,
which pulls in the Group Name from the Group Name table and at the same time,
the combo box is linked to the Group Name table to filter out the records by
Group Name - is that ok???

Last, but not least, I need to create a Report based on the filtered data....

Lesley

Make sure cboFilterGroup is unbound (i.e. nothing in its Control Source
property.)

You don't need both the criteria in the query and the code in its
AfterUpdate event. Remove the criteria if you want to use the code.

Examine the properties of the combo to ensure the filter is being set as you
expect. The Bound Column property lets you know which column in the combo's
RowSource is understood as its Value. It hat to match the data type of your
[Group Name] field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm using a combo box to filter deliverys by group name. The form is
based
on a select query which pulls all deliveries within a specific date range.
I
have the combo box in the form header pointing to the Group Name table
(Account No, Group Name). In the After Update event procedure I have the
following code:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[Group Name] = " & Me.cboFilterGroup
Me.FilterOn = True
End If
End Sub


In the Form properties the record source is pointing to the select query
showing, date, account #, customer name, state, group name (with criteria
set
to [Forms]![DeliverybyGroup]![cboFilterGroup] or
[Forms]![DeliverybyGroup]![cboFilterGroup] Is Null), and delivery time.

At first I had [Forms]![DeliverybyGroup]![cboFilterGroup] as the criteria
in the Group Name Table. When I open the Form I am prompted to enter the
date
range (and that's what its suppose to do), but when I click on the group
name
drop down list NOTHING happens. The group name is not filtered to select
the
records requested. I also notice when testing that any group name
previously
selected shows in the Form Properties/Filter field.

I do have a command button in the header to clear and select a new group
name.

 

[Allen Browne]

Lesley, we can't see why you have all those queries on top of each other.
Sometimes that's needed, but you would not filter both in the query and in
the AfterUdpate of the combo.

The choice is yours as to which of the 2 alternatives you take.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen for assisting me on this. I think I may have the queries set
up
incorrectly.

First, I have a select qry named Delivery Summary ALL (built on the
Invoice
tble and the Delivery Time table). Then I have another select qry named
Delivery Group (built on the Group Name table and Delivery Summary All
qry).
Next, I created the Delivery By Group Form (build on the Delivery Group
qry
and the unbound combo box rowsource points to the Group Name table as its
dropdown list to filter on the Account # and Group/Account Name.

When I click on the dropdown list to filter by Group Name nothing happens.
My question is, the Auto Form is linked to the Delivery Group select qry,
which pulls in the Group Name from the Group Name table and at the same
time,
the combo box is linked to the Group Name table to filter out the records
by
Group Name - is that ok???

Last, but not least, I need to create a Report based on the filtered
data....

Lesley

Make sure cboFilterGroup is unbound (i.e. nothing in its Control Source
property.)

You don't need both the criteria in the query and the code in its
AfterUpdate event. Remove the criteria if you want to use the code.

Examine the properties of the combo to ensure the filter is being set as
you
expect. The Bound Column property lets you know which column in the
combo's
RowSource is understood as its Value. It hat to match the data type of
your
[Group Name] field.

I'm using a combo box to filter deliverys by group name. The form is
based
on a select query which pulls all deliveries within a specific date
range.
I
have the combo box in the form header pointing to the Group Name table
(Account No, Group Name). In the After Update event procedure I have
the
following code:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[Group Name] = " & Me.cboFilterGroup
Me.FilterOn = True
End If
End Sub


In the Form properties the record source is pointing to the select
query
showing, date, account #, customer name, state, group name (with
criteria
set
to [Forms]![DeliverybyGroup]![cboFilterGroup] or
[Forms]![DeliverybyGroup]![cboFilterGroup] Is Null), and delivery time.

At first I had [Forms]![DeliverybyGroup]![cboFilterGroup] as the
criteria
in the Group Name Table. When I open the Form I am prompted to enter
the
date
range (and that's what its suppose to do), but when I click on the
group
name
drop down list NOTHING happens. The group name is not filtered to
select
the
records requested. I also notice when testing that any group name
previously
selected shows in the Form Properties/Filter field.

I do have a command button in the header to clear and select a new
group
name.

 

[Guest]

ok, so what's the best way to use the combo box to filter by Group?

Lesley, we can't see why you have all those queries on top of each other.
Sometimes that's needed, but you would not filter both in the query and in
the AfterUdpate of the combo.

The choice is yours as to which of the 2 alternatives you take.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen for assisting me on this. I think I may have the queries set
up
incorrectly.

First, I have a select qry named Delivery Summary ALL (built on the
Invoice
tble and the Delivery Time table). Then I have another select qry named
Delivery Group (built on the Group Name table and Delivery Summary All
qry).
Next, I created the Delivery By Group Form (build on the Delivery Group
qry
and the unbound combo box rowsource points to the Group Name table as its
dropdown list to filter on the Account # and Group/Account Name.

When I click on the dropdown list to filter by Group Name nothing happens.
My question is, the Auto Form is linked to the Delivery Group select qry,
which pulls in the Group Name from the Group Name table and at the same
time,
the combo box is linked to the Group Name table to filter out the records
by
Group Name - is that ok???

Last, but not least, I need to create a Report based on the filtered
data....

Lesley

Make sure cboFilterGroup is unbound (i.e. nothing in its Control Source
property.)

You don't need both the criteria in the query and the code in its
AfterUpdate event. Remove the criteria if you want to use the code.

Examine the properties of the combo to ensure the filter is being set as
you
expect. The Bound Column property lets you know which column in the
combo's
RowSource is understood as its Value. It hat to match the data type of
your
[Group Name] field.

I'm using a combo box to filter deliverys by group name. The form is
based
on a select query which pulls all deliveries within a specific date
range.
I
have the combo box in the form header pointing to the Group Name table
(Account No, Group Name). In the After Update event procedure I have
the
following code:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[Group Name] = " & Me.cboFilterGroup
Me.FilterOn = True
End If
End Sub


In the Form properties the record source is pointing to the select
query
showing, date, account #, customer name, state, group name (with
criteria
set
to [Forms]![DeliverybyGroup]![cboFilterGroup] or
[Forms]![DeliverybyGroup]![cboFilterGroup] Is Null), and delivery time.

At first I had [Forms]![DeliverybyGroup]![cboFilterGroup] as the
criteria
in the Group Name Table. When I open the Form I am prompted to enter
the
date
range (and that's what its suppose to do), but when I click on the
group
name
drop down list NOTHING happens. The group name is not filtered to
select
the
records requested. I also notice when testing that any group name
previously
selected shows in the Form Properties/Filter field.

I do have a command button in the header to clear and select a new
group
name.

 

[Allen Browne]

Either way works fine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ok, so what's the best way to use the combo box to filter by Group?

Lesley, we can't see why you have all those queries on top of each other.
Sometimes that's needed, but you would not filter both in the query and
in
the AfterUdpate of the combo.

The choice is yours as to which of the 2 alternatives you take.

Thanks Allen for assisting me on this. I think I may have the queries
set
up
incorrectly.

First, I have a select qry named Delivery Summary ALL (built on the
Invoice
tble and the Delivery Time table). Then I have another select qry
named
Delivery Group (built on the Group Name table and Delivery Summary All
qry).
Next, I created the Delivery By Group Form (build on the Delivery Group
qry
and the unbound combo box rowsource points to the Group Name table as
its
dropdown list to filter on the Account # and Group/Account Name.

When I click on the dropdown list to filter by Group Name nothing
happens.
My question is, the Auto Form is linked to the Delivery Group select
qry,
which pulls in the Group Name from the Group Name table and at the same
time,
the combo box is linked to the Group Name table to filter out the
records
by
Group Name - is that ok???

Last, but not least, I need to create a Report based on the filtered
data....

Lesley

:

Make sure cboFilterGroup is unbound (i.e. nothing in its Control
Source
property.)

You don't need both the criteria in the query and the code in its
AfterUpdate event. Remove the criteria if you want to use the code.

Examine the properties of the combo to ensure the filter is being set
as
you
expect. The Bound Column property lets you know which column in the
combo's
RowSource is understood as its Value. It hat to match the data type of
your
[Group Name] field.

I'm using a combo box to filter deliverys by group name. The form
is
based
on a select query which pulls all deliveries within a specific date
range.
I
have the combo box in the form header pointing to the Group Name
table
(Account No, Group Name). In the After Update event procedure I
have
the
following code:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[Group Name] = " & Me.cboFilterGroup
Me.FilterOn = True
End If
End Sub


In the Form properties the record source is pointing to the select
query
showing, date, account #, customer name, state, group name (with
criteria
set
to [Forms]![DeliverybyGroup]![cboFilterGroup] or
[Forms]![DeliverybyGroup]![cboFilterGroup] Is Null), and delivery
time.

At first I had [Forms]![DeliverybyGroup]![cboFilterGroup] as the
criteria
in the Group Name Table. When I open the Form I am prompted to enter
the
date
range (and that's what its suppose to do), but when I click on the
group
name
drop down list NOTHING happens. The group name is not filtered to
select
the
records requested. I also notice when testing that any group name
previously
selected shows in the Form Properties/Filter field.

I do have a command button in the header to clear and select a new
group
name.