Filtering records by code based on combobox

[Guest]

Hi
To learn programming, I've set out a series of tasks to master (well, at
least to become familiar with!) One task I would like to be able to handle
is the following:

A continuous form has an unbound combo box in the header whose after_update
event is used to filter results from the underlying query. After making a
selection, I want the form to show all matching records. I've tried a number
of ideas, (using SQL, clones,...) but continue to have difficulties.

Could some kind soul lay out the general approach, or refer me to some code
that illustrates this task?

mucho thanks

 

[Guest]

Hi Sophie,

In your after update event, have something like this:

me.filter = "IDFIELD = " & me.COMBOBOX
me.filteron = true

If it's valid for the user to clear the combo, you would need to check for
this using the isnull function, eg:

if isnull(me.COMBOBOX) then
me.filter = ""
me.filteron = false
else
me.filter = "IDFIELD = " & me.COMBOBOX
me.filteron = true
end if

Hope this helps.

Damian.

 

[Regan via AccessMonster.com]

Private Sub CboFilter_AfterUpdate()

If Me.Dirty Then
Me.Dirty = False
End If

If IsNull(Me.CboFilter) Then
Me.FilterOn = False
Else
Me.Filter = "[WhateverField] Like """ & Me.CboFilter & "*"""
Me.FilterOn = True
End If

modified from a test box search i use, Cool.

 

[Guest]

First, thanks to both Damian and Regan for your excellent answers. I'm
looking forward to trying this technique. I am confused about one thing,
though. By experimenting, I found the following code seems to work, but I
can't figure out why??? (I 've since put the to-be-filtered records in a
subform linked by City-SchoolID) I don't get why all the correct filtered
records show even though 'FindFirst' is used. Any comments?

If Not IsNull(cboSelectSchool) Then
Me.RecordsetClone.Findfirst "[City-SchoolID] = " &
Me![cboSelectSchool]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

--
Thanks
Sophie

Private Sub CboFilter_AfterUpdate()

If Me.Dirty Then
Me.Dirty = False
End If

If IsNull(Me.CboFilter) Then
Me.FilterOn = False
Else
Me.Filter = "[WhateverField] Like """ & Me.CboFilter & "*"""
Me.FilterOn = True
End If

modified from a test box search i use, Cool.

Hi
To learn programming, I've set out a series of tasks to master (well, at
least to become familiar with!) One task I would like to be able to handle
is the following:

A continuous form has an unbound combo box in the header whose after_update
event is used to filter results from the underlying query. After making a
selection, I want the form to show all matching records. I've tried a number
of ideas, (using SQL, clones,...) but continue to have difficulties.

Could some kind soul lay out the general approach, or refer me to some code
that illustrates this task?

mucho thanks

 

[Regan via AccessMonster.com]

From Help menu

The RecordsetClone property setting is a copy of the underlying query or
table specified by the form's RecordSource property. If a form is based on a
query, for example, referring to the RecordsetClone property is the
equivalent of cloning a Recordset object by using the same query. If you then
apply a filter to the form, the Recordset object reflects the filtering.

The next example uses the RecordsetClone property and the Recordset object to
synchronize a recordset's record with the form's current record. When a
company name is selected from a combo box, the FindFirst method is used to
locate the record for that company and the Recordset object's DAO Bookmark
property is assigned to the form's Bookmark property, causing the form to
display the found record.

Sub SupplierID_AfterUpdate()
Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = Str(Me!SupplierID)
rst.FindFirst "SupplierID = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
End Sub

First, thanks to both Damian and Regan for your excellent answers. I'm
looking forward to trying this technique. I am confused about one thing,
though. By experimenting, I found the following code seems to work, but I
can't figure out why??? (I 've since put the to-be-filtered records in a
subform linked by City-SchoolID) I don't get why all the correct filtered
records show even though 'FindFirst' is used. Any comments?

If Not IsNull(cboSelectSchool) Then
Me.RecordsetClone.Findfirst "[City-SchoolID] = " &
Me![cboSelectSchool]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Private Sub CboFilter_AfterUpdate()

[quoted text clipped - 25 lines]

Have fun

--
Regan,
Paeroa
World famous in New Zealand

Message posted via AccessMonster.com

 

[Guest]

Hi Sophie,

By putting the to-be-filtered records into a subform, Access is handling the
filtering process for you through the "Link Child Field"/"Link Master Field"
properties of your main form.

I am assuming you are doing a findfirst on your main form. This will then
force Access to refresh the subform, which is linked by an ID field - this is
how you should do it. Filtering is useful for when you have a large set of
linked data that you want to restrict further.

Damian.

First, thanks to both Damian and Regan for your excellent answers. I'm
looking forward to trying this technique. I am confused about one thing,
though. By experimenting, I found the following code seems to work, but I
can't figure out why??? (I 've since put the to-be-filtered records in a
subform linked by City-SchoolID) I don't get why all the correct filtered
records show even though 'FindFirst' is used. Any comments?

If Not IsNull(cboSelectSchool) Then
Me.RecordsetClone.Findfirst "[City-SchoolID] = " &
Me![cboSelectSchool]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

--
Thanks
Sophie

Private Sub CboFilter_AfterUpdate()

If Me.Dirty Then
Me.Dirty = False
End If

If IsNull(Me.CboFilter) Then
Me.FilterOn = False
Else
Me.Filter = "[WhateverField] Like """ & Me.CboFilter & "*"""
Me.FilterOn = True
End If

modified from a test box search i use, Cool.

Hi
To learn programming, I've set out a series of tasks to master (well, at
least to become familiar with!) One task I would like to be able to handle
is the following:

A continuous form has an unbound combo box in the header whose after_update
event is used to filter results from the underlying query. After making a
selection, I want the form to show all matching records. I've tried a number
of ideas, (using SQL, clones,...) but continue to have difficulties.

Could some kind soul lay out the general approach, or refer me to some code
that illustrates this task?

mucho thanks

 

[Guest]

Makes a lot of sense. TQ Damian & Regan.