use combo box to filter data in a form

[Guest]

Hi! I need to create a report to show deliveries by a company name. I
started this process by using a form displaying all deliveries and added a
combo box in the header to filter all deliveries made to a specific company.
I'm having trouble linking the selected company name to the records displayed
in the form to filter out just those deliveries made to the selected company.


I later need this to be in report format. Am I doing this right? Is there
an easier way to just do this in report format or do I need to start with a
form and later convert the data into a report?

 

[Allen Browne]

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table where you list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound combo to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate property, and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same filter, by
adding a command button, and putting code like this into its On Click event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

 

[Guest]

Allen, thanks for getting back to me on this. I have another concern that
may or may not be an issue regarding the design of this database...

First, I created a select query called Delivery Summary ALL, which is based
on an Invoice table and a Delivery Time table. Next, I created another
select query called Delivery Group based on the Group Name table and the
Delivery Summary ALL select query. From there, I created the Delivery By
Group Form (autoform format), which is based on the Delivery Group query
where the Group Name is to be filtered by the combo box dropdown list. It's
Rowsource (in the combo box) points to the Group Name table (for the account
# and group name).

Question - can the combo box filter on group name on the form if both fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table where you list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound combo to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate property, and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same filter, by
adding a command button, and putting code like this into its On Click event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

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

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

Hi! I need to create a report to show deliveries by a company name. I
started this process by using a form displaying all deliveries and added a
combo box in the header to filter all deliveries made to a specific
company.
I'm having trouble linking the selected company name to the records
displayed
in the form to filter out just those deliveries made to the selected
company.


I later need this to be in report format. Am I doing this right? Is
there
an easier way to just do this in report format or do I need to start with
a
form and later convert the data into a report?

 

[Allen Browne]

The combo can filter on 2 fields if you build the Filter string for the
form, but probably not if you put the criteria in the query.

To filter your form so that Combo1 matches the value in either Field1 or
Field2, you would use code like this:
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "(Field1 = " & Me.Combo1 & ") OR (Field2 = " & Me.Comob1
& ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

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

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

Allen, thanks for getting back to me on this. I have another concern that
may or may not be an issue regarding the design of this database...

First, I created a select query called Delivery Summary ALL, which is
based
on an Invoice table and a Delivery Time table. Next, I created another
select query called Delivery Group based on the Group Name table and the
Delivery Summary ALL select query. From there, I created the Delivery By
Group Form (autoform format), which is based on the Delivery Group query
where the Group Name is to be filtered by the combo box dropdown list.
It's
Rowsource (in the combo box) points to the Group Name table (for the
account
# and group name).

Question - can the combo box filter on group name on the form if both
fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table where you
list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound combo to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate property,
and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same filter, by
adding a command button, and putting code like this into its On Click
event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Hi! I need to create a report to show deliveries by a company name. I
started this process by using a form displaying all deliveries and
added a
combo box in the header to filter all deliveries made to a specific
company.
I'm having trouble linking the selected company name to the records
displayed
in the form to filter out just those deliveries made to the selected
company.


I later need this to be in report format. Am I doing this right? Is
there
an easier way to just do this in report format or do I need to start
with
a
form and later convert the data into a report?

 

[Guest]

I made the changes you suggested; however, when I select the group name in
the combo box I still get nothing. I don't get any error message or
anything. The selection is made and it doesn't filter. Here's what the
AfterUpdate Event Procedure looks like...

Private Sub cboFilterGroup_AfterUpdate()

End Sub

Private Sub Form_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.cboFilterGroup) Then
strWhere = "(Group Name = " & Me.cboFilterGroup & ")"
Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub SelectGroup_Click()
Me!cboFilterGroup = Null
Me.Requery
End Sub

The combo can filter on 2 fields if you build the Filter string for the
form, but probably not if you put the criteria in the query.

To filter your form so that Combo1 matches the value in either Field1 or
Field2, you would use code like this:
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "(Field1 = " & Me.Combo1 & ") OR (Field2 = " & Me.Comob1
& ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

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

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

Allen, thanks for getting back to me on this. I have another concern that
may or may not be an issue regarding the design of this database...

First, I created a select query called Delivery Summary ALL, which is
based
on an Invoice table and a Delivery Time table. Next, I created another
select query called Delivery Group based on the Group Name table and the
Delivery Summary ALL select query. From there, I created the Delivery By
Group Form (autoform format), which is based on the Delivery Group query
where the Group Name is to be filtered by the combo box dropdown list.
It's
Rowsource (in the combo box) points to the Group Name table (for the
account
# and group name).

Question - can the combo box filter on group name on the form if both
fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table where you
list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound combo to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate property,
and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same filter, by
adding a command button, and putting code like this into its On Click
event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Hi! I need to create a report to show deliveries by a company name. I
started this process by using a form displaying all deliveries and
added a
combo box in the header to filter all deliveries made to a specific
company.
I'm having trouble linking the selected company name to the records
displayed
in the form to filter out just those deliveries made to the selected
company.


I later need this to be in report format. Am I doing this right? Is
there
an easier way to just do this in report format or do I need to start
with
a
form and later convert the data into a report?

 

[Guest]

UPDATE...I added the following code to the AfterUpdate event procedure in the
combo box properties:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Group Name] = " & Me.cboFilterGroup
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Private Sub SelectGroup_Click()
Me!cboFilterGroup = Null
Me.Requery
End Sub

Also, in the Form properties the Record Source = DeliveryByGroup query,
where the criteria for Group Name =
[Forms]![DeliveryByGroupForm]![cboFilterGroup] or
[Forms]![DeliveryByGroupForm]![cboFilterGroup] Is Null.

Now, when I select a group name from the combo box I get the following
Run-time error '3070': "The Microsoft Jet database engine does not recognize
"Group Name" as a valid field name or expression.

I'm not sure what the problem is now. Also, I have a data parameter in the
DeliveryByGroup query. I want to make the selection via the combo box FIRST
and then run the related query on the Form to filter the records. It appears
to be working in reverse order (by running the query connected to the Form
first), then attempting to filter off of the combo box selection.

Thanks again for your time and consideration...

--Lesley

The combo can filter on 2 fields if you build the Filter string for the
form, but probably not if you put the criteria in the query.

To filter your form so that Combo1 matches the value in either Field1 or
Field2, you would use code like this:
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "(Field1 = " & Me.Combo1 & ") OR (Field2 = " & Me.Comob1
& ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

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

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

Allen, thanks for getting back to me on this. I have another concern that
may or may not be an issue regarding the design of this database...

First, I created a select query called Delivery Summary ALL, which is
based
on an Invoice table and a Delivery Time table. Next, I created another
select query called Delivery Group based on the Group Name table and the
Delivery Summary ALL select query. From there, I created the Delivery By
Group Form (autoform format), which is based on the Delivery Group query
where the Group Name is to be filtered by the combo box dropdown list.
It's
Rowsource (in the combo box) points to the Group Name table (for the
account
# and group name).

Question - can the combo box filter on group name on the form if both
fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table where you
list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound combo to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate property,
and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same filter, by
adding a command button, and putting code like this into its On Click
event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Hi! I need to create a report to show deliveries by a company name. I
started this process by using a form displaying all deliveries and
added a
combo box in the header to filter all deliveries made to a specific
company.
I'm having trouble linking the selected company name to the records
displayed
in the form to filter out just those deliveries made to the selected
company.


I later need this to be in report format. Am I doing this right? Is
there
an easier way to just do this in report format or do I need to start
with
a
form and later convert the data into a report?

 

[Allen Browne]

Remove the parameter and criteria from they query.
You don't want it running there as well as the search (or filter) in the
Afterupdate of the combo.

Do you have a field named
Group Name
in the query that feeds the form?
You won't be able to find using that field if it is not in the source query.

Your parallel reply (where you tried to apply a filter instead of finding)
lacked the square brackets around [Group Name]. You must include those when
the field name contains a space.

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

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

UPDATE...I added the following code to the AfterUpdate event procedure in
the
combo box properties:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Group Name] = " & Me.cboFilterGroup
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Private Sub SelectGroup_Click()
Me!cboFilterGroup = Null
Me.Requery
End Sub

Also, in the Form properties the Record Source = DeliveryByGroup query,
where the criteria for Group Name =
[Forms]![DeliveryByGroupForm]![cboFilterGroup] or
[Forms]![DeliveryByGroupForm]![cboFilterGroup] Is Null.

Now, when I select a group name from the combo box I get the following
Run-time error '3070': "The Microsoft Jet database engine does not
recognize
"Group Name" as a valid field name or expression.

I'm not sure what the problem is now. Also, I have a data parameter in
the
DeliveryByGroup query. I want to make the selection via the combo box
FIRST
and then run the related query on the Form to filter the records. It
appears
to be working in reverse order (by running the query connected to the Form
first), then attempting to filter off of the combo box selection.

Thanks again for your time and consideration...

--Lesley

The combo can filter on 2 fields if you build the Filter string for the
form, but probably not if you put the criteria in the query.

To filter your form so that Combo1 matches the value in either Field1 or
Field2, you would use code like this:
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "(Field1 = " & Me.Combo1 & ") OR (Field2 = " &
Me.Comob1
& ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

Allen, thanks for getting back to me on this. I have another concern
that
may or may not be an issue regarding the design of this database...

First, I created a select query called Delivery Summary ALL, which is
based
on an Invoice table and a Delivery Time table. Next, I created another
select query called Delivery Group based on the Group Name table and
the
Delivery Summary ALL select query. From there, I created the Delivery
By
Group Form (autoform format), which is based on the Delivery Group
query
where the Group Name is to be filtered by the combo box dropdown list.
It's
Rowsource (in the combo box) points to the Group Name table (for the
account
# and group name).

Question - can the combo box filter on group name on the form if both
fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley


:

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table where you
list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound combo to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate
property,
and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same filter,
by
adding a command button, and putting code like this into its On Click
event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Hi! I need to create a report to show deliveries by a company name.
I
started this process by using a form displaying all deliveries and
added a
combo box in the header to filter all deliveries made to a specific
company.
I'm having trouble linking the selected company name to the records
displayed
in the form to filter out just those deliveries made to the selected
company.


I later need this to be in report format. Am I doing this right?
Is
there
an easier way to just do this in report format or do I need to start
with
a
form and later convert the data into a report?

 

[Guest]

Ok, I'm almost, but not quite there...

I removed the parameter and criteria from the query as suggested. YES,
"Group Name" IS a field in the query that feeds the form. I guess I will
need to add a second combo box to filter the date parameters, right?

I changed the code in the AfterUpdate event procedure back to:

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

The other code merely did a "Find" and not a FILTER. I need the combo box
to FILTER out the records by group name.

The problem I have now is that when I select the group name (XYZ Company)
from the combo box, I get a 'Enter Parameter Value' box. If I re-enter XYZ
Company then the filter works. So, obviously this is not working right. Is
there a problem with my code???

Remove the parameter and criteria from they query.
You don't want it running there as well as the search (or filter) in the
Afterupdate of the combo.

Do you have a field named
Group Name
in the query that feeds the form?
You won't be able to find using that field if it is not in the source query.

Your parallel reply (where you tried to apply a filter instead of finding)
lacked the square brackets around [Group Name]. You must include those when
the field name contains a space.

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

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

UPDATE...I added the following code to the AfterUpdate event procedure in
the
combo box properties:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Group Name] = " & Me.cboFilterGroup
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Private Sub SelectGroup_Click()
Me!cboFilterGroup = Null
Me.Requery
End Sub

Also, in the Form properties the Record Source = DeliveryByGroup query,
where the criteria for Group Name =
[Forms]![DeliveryByGroupForm]![cboFilterGroup] or
[Forms]![DeliveryByGroupForm]![cboFilterGroup] Is Null.

Now, when I select a group name from the combo box I get the following
Run-time error '3070': "The Microsoft Jet database engine does not
recognize
"Group Name" as a valid field name or expression.

I'm not sure what the problem is now. Also, I have a data parameter in
the
DeliveryByGroup query. I want to make the selection via the combo box
FIRST
and then run the related query on the Form to filter the records. It
appears
to be working in reverse order (by running the query connected to the Form
first), then attempting to filter off of the combo box selection.

Thanks again for your time and consideration...

--Lesley

The combo can filter on 2 fields if you build the Filter string for the
form, but probably not if you put the criteria in the query.

To filter your form so that Combo1 matches the value in either Field1 or
Field2, you would use code like this:
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "(Field1 = " & Me.Combo1 & ") OR (Field2 = " &
Me.Comob1
& ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

Allen, thanks for getting back to me on this. I have another concern
that
may or may not be an issue regarding the design of this database...

First, I created a select query called Delivery Summary ALL, which is
based
on an Invoice table and a Delivery Time table. Next, I created another
select query called Delivery Group based on the Group Name table and
the
Delivery Summary ALL select query. From there, I created the Delivery
By
Group Form (autoform format), which is based on the Delivery Group
query
where the Group Name is to be filtered by the combo box dropdown list.
It's
Rowsource (in the combo box) points to the Group Name table (for the
account
# and group name).

Question - can the combo box filter on group name on the form if both
fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley


:

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table where you
list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound combo to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate
property,
and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same filter,
by
adding a command button, and putting code like this into its On Click
event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Hi! I need to create a report to show deliveries by a company name.
I
started this process by using a form displaying all deliveries and
added a
combo box in the header to filter all deliveries made to a specific
company.
I'm having trouble linking the selected company name to the records
displayed
in the form to filter out just those deliveries made to the selected
company.


I later need this to be in report format. Am I doing this right?
Is
there
an easier way to just do this in report format or do I need to start
with
a
form and later convert the data into a report?

 

[Allen Browne]

Okay, it looks like [Group Name] might be a Text field (not a Number field.)
If so, the code needs extra quotes:
Me.Filter = "[Group Name]=""" & Me.cboFilterGroup & """"

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

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

Ok, I'm almost, but not quite there...

I removed the parameter and criteria from the query as suggested. YES,
"Group Name" IS a field in the query that feeds the form. I guess I will
need to add a second combo box to filter the date parameters, right?

I changed the code in the AfterUpdate event procedure back to:

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

The other code merely did a "Find" and not a FILTER. I need the combo box
to FILTER out the records by group name.

The problem I have now is that when I select the group name (XYZ Company)
from the combo box, I get a 'Enter Parameter Value' box. If I re-enter
XYZ
Company then the filter works. So, obviously this is not working right.
Is
there a problem with my code???

Remove the parameter and criteria from they query.
You don't want it running there as well as the search (or filter) in the
Afterupdate of the combo.

Do you have a field named
Group Name
in the query that feeds the form?
You won't be able to find using that field if it is not in the source
query.

Your parallel reply (where you tried to apply a filter instead of
finding)
lacked the square brackets around [Group Name]. You must include those
when
the field name contains a space.

UPDATE...I added the following code to the AfterUpdate event procedure
in
the
combo box properties:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Group Name] = " & Me.cboFilterGroup
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Private Sub SelectGroup_Click()
Me!cboFilterGroup = Null
Me.Requery
End Sub

Also, in the Form properties the Record Source = DeliveryByGroup query,
where the criteria for Group Name =
[Forms]![DeliveryByGroupForm]![cboFilterGroup] or
[Forms]![DeliveryByGroupForm]![cboFilterGroup] Is Null.

Now, when I select a group name from the combo box I get the following
Run-time error '3070': "The Microsoft Jet database engine does not
recognize
"Group Name" as a valid field name or expression.

I'm not sure what the problem is now. Also, I have a data parameter in
the
DeliveryByGroup query. I want to make the selection via the combo box
FIRST
and then run the related query on the Form to filter the records. It
appears
to be working in reverse order (by running the query connected to the
Form
first), then attempting to filter off of the combo box selection.

Thanks again for your time and consideration...

--Lesley
:

The combo can filter on 2 fields if you build the Filter string for
the
form, but probably not if you put the criteria in the query.

To filter your form so that Combo1 matches the value in either Field1
or
Field2, you would use code like this:
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "(Field1 = " & Me.Combo1 & ") OR (Field2 = " &
Me.Comob1
& ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

Allen, thanks for getting back to me on this. I have another
concern
that
may or may not be an issue regarding the design of this database...

First, I created a select query called Delivery Summary ALL, which
is
based
on an Invoice table and a Delivery Time table. Next, I created
another
select query called Delivery Group based on the Group Name table and
the
Delivery Summary ALL select query. From there, I created the
Delivery
By
Group Form (autoform format), which is based on the Delivery Group
query
where the Group Name is to be filtered by the combo box dropdown
list.
It's
Rowsource (in the combo box) points to the Group Name table (for the
account
# and group name).

Question - can the combo box filter on group name on the form if
both
fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley


:

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table where
you
list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound combo
to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate
property,
and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same
filter,
by
adding a command button, and putting code like this into its On
Click
event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Hi! I need to create a report to show deliveries by a company
name.
I
started this process by using a form displaying all deliveries
and
added a
combo box in the header to filter all deliveries made to a
specific
company.
I'm having trouble linking the selected company name to the
records
displayed
in the form to filter out just those deliveries made to the
selected
company.


I later need this to be in report format. Am I doing this right?
Is
there
an easier way to just do this in report format or do I need to
start
with
a
form and later convert the data into a report?

 

[Guest]

Great - that worked! I guess in flipping back and forth with code I somehow
lost the extra quotes by pasting in the original version of code. Thanks
much!

Now, I still need to filter again by DATE RANGE. Remember, I removed the
parameters from the source query so that the form would not prompt me for the
date before prompting me for the group name.

What's the best way to re-filter on DATE RANGE???

Okay, it looks like [Group Name] might be a Text field (not a Number field.)
If so, the code needs extra quotes:
Me.Filter = "[Group Name]=""" & Me.cboFilterGroup & """"

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

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

Ok, I'm almost, but not quite there...

I removed the parameter and criteria from the query as suggested. YES,
"Group Name" IS a field in the query that feeds the form. I guess I will
need to add a second combo box to filter the date parameters, right?

I changed the code in the AfterUpdate event procedure back to:

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

The other code merely did a "Find" and not a FILTER. I need the combo box
to FILTER out the records by group name.

The problem I have now is that when I select the group name (XYZ Company)
from the combo box, I get a 'Enter Parameter Value' box. If I re-enter
XYZ
Company then the filter works. So, obviously this is not working right.
Is
there a problem with my code???

Remove the parameter and criteria from they query.
You don't want it running there as well as the search (or filter) in the
Afterupdate of the combo.

Do you have a field named
Group Name
in the query that feeds the form?
You won't be able to find using that field if it is not in the source
query.

Your parallel reply (where you tried to apply a filter instead of
finding)
lacked the square brackets around [Group Name]. You must include those
when
the field name contains a space.

UPDATE...I added the following code to the AfterUpdate event procedure
in
the
combo box properties:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Group Name] = " & Me.cboFilterGroup
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Private Sub SelectGroup_Click()
Me!cboFilterGroup = Null
Me.Requery
End Sub

Also, in the Form properties the Record Source = DeliveryByGroup query,
where the criteria for Group Name =
[Forms]![DeliveryByGroupForm]![cboFilterGroup] or
[Forms]![DeliveryByGroupForm]![cboFilterGroup] Is Null.

Now, when I select a group name from the combo box I get the following
Run-time error '3070': "The Microsoft Jet database engine does not
recognize
"Group Name" as a valid field name or expression.

I'm not sure what the problem is now. Also, I have a data parameter in
the
DeliveryByGroup query. I want to make the selection via the combo box
FIRST
and then run the related query on the Form to filter the records. It
appears
to be working in reverse order (by running the query connected to the
Form
first), then attempting to filter off of the combo box selection.

Thanks again for your time and consideration...

--Lesley
:

The combo can filter on 2 fields if you build the Filter string for
the
form, but probably not if you put the criteria in the query.

To filter your form so that Combo1 matches the value in either Field1
or
Field2, you would use code like this:
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "(Field1 = " & Me.Combo1 & ") OR (Field2 = " &
Me.Comob1
& ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

Allen, thanks for getting back to me on this. I have another
concern
that
may or may not be an issue regarding the design of this database...

First, I created a select query called Delivery Summary ALL, which
is
based
on an Invoice table and a Delivery Time table. Next, I created
another
select query called Delivery Group based on the Group Name table and
the
Delivery Summary ALL select query. From there, I created the
Delivery
By
Group Form (autoform format), which is based on the Delivery Group
query
where the Group Name is to be filtered by the combo box dropdown
list.
It's
Rowsource (in the combo box) points to the Group Name table (for the
account
# and group name).

Question - can the combo box filter on group name on the form if
both
fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley


:

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table where
you
list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound combo
to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate
property,
and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same
filter,
by
adding a command button, and putting code like this into its On
Click
event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Hi! I need to create a report to show deliveries by a company
name.
I
started this process by using a form displaying all deliveries
and
added a
combo box in the header to filter all deliveries made to a
specific
company.
I'm having trouble linking the selected company name to the
records
displayed
in the form to filter out just those deliveries made to the
selected
company.


I later need this to be in report format. Am I doing this right?
Is
there
an easier way to just do this in report format or do I need to
start
with
a
form and later convert the data into a report?

 

[Allen Browne]

Okay, if you can assume you have text boxes named txtStartDate and
txtEndDate to enter the dates, use code as in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

Then you can add the group name to the filter string like this:
If Not IsNull(Me.cboGroupFilter) Then
If strWhere = vbNullString Then
strWhere = "[Group Name]=""" & Me.cboFilterGroup & """"
Else
strWhere = strWhere & " AND ([Group Name]=""" &
Me.cboFilterGroup & """)"
End If
End If

Me.Filter = strWhere
Me.FilterOn = True

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

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

Great - that worked! I guess in flipping back and forth with code I
somehow
lost the extra quotes by pasting in the original version of code. Thanks
much!

Now, I still need to filter again by DATE RANGE. Remember, I removed the
parameters from the source query so that the form would not prompt me for
the
date before prompting me for the group name.

What's the best way to re-filter on DATE RANGE???

Okay, it looks like [Group Name] might be a Text field (not a Number
field.)
If so, the code needs extra quotes:
Me.Filter = "[Group Name]=""" & Me.cboFilterGroup & """"

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

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

Ok, I'm almost, but not quite there...

I removed the parameter and criteria from the query as suggested. YES,
"Group Name" IS a field in the query that feeds the form. I guess I
will
need to add a second combo box to filter the date parameters, right?

I changed the code in the AfterUpdate event procedure back to:

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

The other code merely did a "Find" and not a FILTER. I need the combo
box
to FILTER out the records by group name.

The problem I have now is that when I select the group name (XYZ
Company)
from the combo box, I get a 'Enter Parameter Value' box. If I re-enter
XYZ
Company then the filter works. So, obviously this is not working
right.
Is
there a problem with my code???

:

Remove the parameter and criteria from they query.
You don't want it running there as well as the search (or filter) in
the
Afterupdate of the combo.

Do you have a field named
Group Name
in the query that feeds the form?
You won't be able to find using that field if it is not in the source
query.

Your parallel reply (where you tried to apply a filter instead of
finding)
lacked the square brackets around [Group Name]. You must include those
when
the field name contains a space.

UPDATE...I added the following code to the AfterUpdate event
procedure
in
the
combo box properties:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Group Name] = " & Me.cboFilterGroup
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Private Sub SelectGroup_Click()
Me!cboFilterGroup = Null
Me.Requery
End Sub

Also, in the Form properties the Record Source = DeliveryByGroup
query,
where the criteria for Group Name =
[Forms]![DeliveryByGroupForm]![cboFilterGroup] or
[Forms]![DeliveryByGroupForm]![cboFilterGroup] Is Null.

Now, when I select a group name from the combo box I get the
following
Run-time error '3070': "The Microsoft Jet database engine does not
recognize
"Group Name" as a valid field name or expression.

I'm not sure what the problem is now. Also, I have a data parameter
in
the
DeliveryByGroup query. I want to make the selection via the combo
box
FIRST
and then run the related query on the Form to filter the records.
It
appears
to be working in reverse order (by running the query connected to
the
Form
first), then attempting to filter off of the combo box selection.

Thanks again for your time and consideration...

--Lesley
:

The combo can filter on 2 fields if you build the Filter string for
the
form, but probably not if you put the criteria in the query.

To filter your form so that Combo1 matches the value in either
Field1
or
Field2, you would use code like this:
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "(Field1 = " & Me.Combo1 & ") OR (Field2 = " &
Me.Comob1
& ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

Allen, thanks for getting back to me on this. I have another
concern
that
may or may not be an issue regarding the design of this
database...

First, I created a select query called Delivery Summary ALL,
which
is
based
on an Invoice table and a Delivery Time table. Next, I created
another
select query called Delivery Group based on the Group Name table
and
the
Delivery Summary ALL select query. From there, I created the
Delivery
By
Group Form (autoform format), which is based on the Delivery
Group
query
where the Group Name is to be filtered by the combo box dropdown
list.
It's
Rowsource (in the combo box) points to the Group Name table (for
the
account
# and group name).

Question - can the combo box filter on group name on the form if
both
fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley


:

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to
Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table
where
you
list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound
combo
to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM
Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate
property,
and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same
filter,
by
adding a command button, and putting code like this into its On
Click
event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Hi! I need to create a report to show deliveries by a company
name.
I
started this process by using a form displaying all deliveries
and
added a
combo box in the header to filter all deliveries made to a
specific
company.
I'm having trouble linking the selected company name to the
records
displayed
in the form to filter out just those deliveries made to the
selected
company.


I later need this to be in report format. Am I doing this
right?
Is
there
an easier way to just do this in report format or do I need to
start
with
a
form and later convert the data into a report?

 

[Guest]

I had already tried Method 1: Parameter query as outlined in the
below-mentioned article; however, when I add in the parameters/criteria
suddenly the combo box does not work (the form will no longer filter on group
name). It appears that it will do one operation or the other, but not both.
Do you know why? How can I modify the form to do both (filter by group name
and by date range)?

Okay, if you can assume you have text boxes named txtStartDate and
txtEndDate to enter the dates, use code as in this article:
Limiting a Report to a Date Range
at
http://allenbrowne.com/casu-08.html

Then you can add the group name to the filter string like this:
If Not IsNull(Me.cboGroupFilter) Then
If strWhere = vbNullString Then
strWhere = "[Group Name]=""" & Me.cboFilterGroup & """"
Else
strWhere = strWhere & " AND ([Group Name]=""" &
Me.cboFilterGroup & """)"
End If
End If

Me.Filter = strWhere
Me.FilterOn = True

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

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

Great - that worked! I guess in flipping back and forth with code I
somehow
lost the extra quotes by pasting in the original version of code. Thanks
much!

Now, I still need to filter again by DATE RANGE. Remember, I removed the
parameters from the source query so that the form would not prompt me for
the
date before prompting me for the group name.

What's the best way to re-filter on DATE RANGE???

Okay, it looks like [Group Name] might be a Text field (not a Number
field.)
If so, the code needs extra quotes:
Me.Filter = "[Group Name]=""" & Me.cboFilterGroup & """"

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

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

Ok, I'm almost, but not quite there...

I removed the parameter and criteria from the query as suggested. YES,
"Group Name" IS a field in the query that feeds the form. I guess I
will
need to add a second combo box to filter the date parameters, right?

I changed the code in the AfterUpdate event procedure back to:

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

The other code merely did a "Find" and not a FILTER. I need the combo
box
to FILTER out the records by group name.

The problem I have now is that when I select the group name (XYZ
Company)
from the combo box, I get a 'Enter Parameter Value' box. If I re-enter
XYZ
Company then the filter works. So, obviously this is not working
right.
Is
there a problem with my code???

:

Remove the parameter and criteria from they query.
You don't want it running there as well as the search (or filter) in
the
Afterupdate of the combo.

Do you have a field named
Group Name
in the query that feeds the form?
You won't be able to find using that field if it is not in the source
query.

Your parallel reply (where you tried to apply a filter instead of
finding)
lacked the square brackets around [Group Name]. You must include those
when
the field name contains a space.

UPDATE...I added the following code to the AfterUpdate event
procedure
in
the
combo box properties:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Group Name] = " & Me.cboFilterGroup
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Private Sub SelectGroup_Click()
Me!cboFilterGroup = Null
Me.Requery
End Sub

Also, in the Form properties the Record Source = DeliveryByGroup
query,
where the criteria for Group Name =
[Forms]![DeliveryByGroupForm]![cboFilterGroup] or
[Forms]![DeliveryByGroupForm]![cboFilterGroup] Is Null.

Now, when I select a group name from the combo box I get the
following
Run-time error '3070': "The Microsoft Jet database engine does not
recognize
"Group Name" as a valid field name or expression.

I'm not sure what the problem is now. Also, I have a data parameter
in
the
DeliveryByGroup query. I want to make the selection via the combo
box
FIRST
and then run the related query on the Form to filter the records.
It
appears
to be working in reverse order (by running the query connected to
the
Form
first), then attempting to filter off of the combo box selection.

Thanks again for your time and consideration...

--Lesley
:

The combo can filter on 2 fields if you build the Filter string for
the
form, but probably not if you put the criteria in the query.

To filter your form so that Combo1 matches the value in either
Field1
or
Field2, you would use code like this:
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "(Field1 = " & Me.Combo1 & ") OR (Field2 = " &
Me.Comob1
& ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

Allen, thanks for getting back to me on this. I have another
concern
that
may or may not be an issue regarding the design of this
database...

First, I created a select query called Delivery Summary ALL,
which
is
based
on an Invoice table and a Delivery Time table. Next, I created
another
select query called Delivery Group based on the Group Name table
and
the
Delivery Summary ALL select query. From there, I created the
Delivery
By
Group Form (autoform format), which is based on the Delivery
Group
query
where the Group Name is to be filtered by the combo box dropdown
list.
It's
Rowsource (in the combo box) points to the Group Name table (for
the
account
# and group name).

Question - can the combo box filter on group name on the form if
both
fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley


:

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to
Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table
where
you
list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound
combo
to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM
Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate
property,
and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same
filter,
by
adding a command button, and putting code like this into its On
Click
event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Hi! I need to create a report to show deliveries by a company
name.
I
started this process by using a form displaying all deliveries
and
added a
combo box in the header to filter all deliveries made to a
specific
company.
I'm having trouble linking the selected company name to the
records
displayed
in the form to filter out just those deliveries made to the
selected
company.

 

[Allen Browne]

Lesley, I don't know why your database is not doing both.

I do exactly what I suggested for you - adding the code below to that in the
article - in every database.

Not sure what else to suggest.

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

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

I had already tried Method 1: Parameter query as outlined in the
below-mentioned article; however, when I add in the parameters/criteria
suddenly the combo box does not work (the form will no longer filter on
group
name). It appears that it will do one operation or the other, but not
both.
Do you know why? How can I modify the form to do both (filter by group
name
and by date range)?

Okay, if you can assume you have text boxes named txtStartDate and
txtEndDate to enter the dates, use code as in this article:
Limiting a Report to a Date Range
at
http://allenbrowne.com/casu-08.html

Then you can add the group name to the filter string like this:
If Not IsNull(Me.cboGroupFilter) Then
If strWhere = vbNullString Then
strWhere = "[Group Name]=""" & Me.cboFilterGroup & """"
Else
strWhere = strWhere & " AND ([Group Name]=""" &
Me.cboFilterGroup & """)"
End If
End If

Me.Filter = strWhere
Me.FilterOn = True

Great - that worked! I guess in flipping back and forth with code I
somehow
lost the extra quotes by pasting in the original version of code.
Thanks
much!

Now, I still need to filter again by DATE RANGE. Remember, I removed
the
parameters from the source query so that the form would not prompt me
for
the
date before prompting me for the group name.

What's the best way to re-filter on DATE RANGE???

:

Okay, it looks like [Group Name] might be a Text field (not a Number
field.)
If so, the code needs extra quotes:
Me.Filter = "[Group Name]=""" & Me.cboFilterGroup & """"

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

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

Ok, I'm almost, but not quite there...

I removed the parameter and criteria from the query as suggested.
YES,
"Group Name" IS a field in the query that feeds the form. I guess I
will
need to add a second combo box to filter the date parameters, right?

I changed the code in the AfterUpdate event procedure back to:

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

The other code merely did a "Find" and not a FILTER. I need the
combo
box
to FILTER out the records by group name.

The problem I have now is that when I select the group name (XYZ
Company)
from the combo box, I get a 'Enter Parameter Value' box. If I
re-enter
XYZ
Company then the filter works. So, obviously this is not working
right.
Is
there a problem with my code???

:

Remove the parameter and criteria from they query.
You don't want it running there as well as the search (or filter)
in
the
Afterupdate of the combo.

Do you have a field named
Group Name
in the query that feeds the form?
You won't be able to find using that field if it is not in the
source
query.

Your parallel reply (where you tried to apply a filter instead of
finding)
lacked the square brackets around [Group Name]. You must include
those
when
the field name contains a space.

UPDATE...I added the following code to the AfterUpdate event
procedure
in
the
combo box properties:

Private Sub cboFilterGroup_AfterUpdate()
If Not IsNull(Me.cboFilterGroup) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Group Name] = " & Me.cboFilterGroup
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Private Sub SelectGroup_Click()
Me!cboFilterGroup = Null
Me.Requery
End Sub

Also, in the Form properties the Record Source = DeliveryByGroup
query,
where the criteria for Group Name =
[Forms]![DeliveryByGroupForm]![cboFilterGroup] or
[Forms]![DeliveryByGroupForm]![cboFilterGroup] Is Null.

Now, when I select a group name from the combo box I get the
following
Run-time error '3070': "The Microsoft Jet database engine does
not
recognize
"Group Name" as a valid field name or expression.

I'm not sure what the problem is now. Also, I have a data
parameter
in
the
DeliveryByGroup query. I want to make the selection via the
combo
box
FIRST
and then run the related query on the Form to filter the records.
It
appears
to be working in reverse order (by running the query connected to
the
Form
first), then attempting to filter off of the combo box selection.

Thanks again for your time and consideration...

--Lesley
:

The combo can filter on 2 fields if you build the Filter string
for
the
form, but probably not if you put the criteria in the query.

To filter your form so that Combo1 matches the value in either
Field1
or
Field2, you would use code like this:
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "(Field1 = " & Me.Combo1 & ") OR (Field2 = "
&
Me.Comob1
& ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

Allen, thanks for getting back to me on this. I have another
concern
that
may or may not be an issue regarding the design of this
database...

First, I created a select query called Delivery Summary ALL,
which
is
based
on an Invoice table and a Delivery Time table. Next, I
created
another
select query called Delivery Group based on the Group Name
table
and
the
Delivery Summary ALL select query. From there, I created the
Delivery
By
Group Form (autoform format), which is based on the Delivery
Group
query
where the Group Name is to be filtered by the combo box
dropdown
list.
It's
Rowsource (in the combo box) points to the Group Name table
(for
the
account
# and group name).

Question - can the combo box filter on group name on the form
if
both
fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley


:

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to
Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table
where
you
list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound
combo
to
filter it to just one company. The combo will have
properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM
Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the
AfterUpdate
property,
and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " &
Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the
same
filter,
by
adding a command button, and putting code like this into its
On
Click
event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Hi! I need to create a report to show deliveries by a
company
name.
I
started this process by using a form displaying all
deliveries
and
added a
combo box in the header to filter all deliveries made to a
specific
company.
I'm having trouble linking the selected company name to the
records
displayed
in the form to filter out just those deliveries made to the
selected
company.