Re: use combo box to filter data in a form

Tech-Archive recommends: Speed Up your PC by fixing your registry

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???

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lesley" <Lesley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:61DCF7EA-0A58-45C5-BB03-260CE5206C35@xxxxxxxxxxxxxxxx
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
"Allen Browne" wrote:

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

"Lesley" <Lesley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:802BD423-9E61-4769-9355-130D785A1F87@xxxxxxxxxxxxxxxx
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


"Allen Browne" wrote:

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

"Lesley" <Lesley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0C22972C-2F62-47E7-80E8-220F5035A3E8@xxxxxxxxxxxxxxxx
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?



.

Quantcast