Form filter using a combo box

[rlmorgan]

Hi,
I have a rather complex from based on a query. The form is populated using an unbound combo box. The query has 50 records and the combo box will successfully find the first of 4 matching records. All this is working great with one problem. The forms Navigation Buttons are showing record 12 of 50. I need to get it to only show the 4 matching records, not all. I using the “After Update” event to filter the form.

[FONT=Arial][SIZE=3]Private Sub cboTitle_AfterUpdate()[/SIZE][/FONT]
[SIZE=3][FONT=Arial]    Dim rs As Object[/FONT][/SIZE]
[FONT=Arial][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Arial]    Set rs = Me.Recordset.Clone[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    rs.FindFirst "[Title] = '" & Me![cboTitle] & "'"[/FONT][/SIZE]
[SIZE=3][FONT=Arial]    If Not rs.EOF Then Me.Bookmark = rs.Bookmark[/FONT][/SIZE]
[FONT=Arial][SIZE=3] [/SIZE][/FONT]
[FONT=Arial][SIZE=3]End Sub[/SIZE][/FONT]

Is there a better way to filter the form?

I would also like to add two navigation buttons inside the form to allow the user to move back a forth within the filtered records. Although I think I can get the Nav buttons working if I can get the form filter to work.

Any help???

 

[pbaldy]

You're not filtering the form, you're simply going to a specified record. If you want to filter, set the Filter and FilterOn properties of the form.

 

[rlmorgan]

Thanks for the fast reply. I know just enough about Access to get me in trouble.

Your suggestion makes logical sense now that you’ve pointed it out. I have tried setting the Filter and FilterOn properties of the form in various ways but still can’t get it to work.

I have tried the following:
Form Properties> Data tab> Filter: Me.Filter = cboSoftwareTitle
Along with:

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
    Me.Filter = cboSoftwareTitle
    Me.Filter = True
End Sub

Still not working.

 

[rlmorgan]

Problem solved. I decided to go about it another way and it may allow the DB to be converted to Access 2007 or 2010 with fewer problems. I simply added a Criteria to the query that the form is based on.

On the “Title” Field of the query I set the Criteria to: [Forms]![fsubAddKeys]![cboTitle]

Thanks for the help.

 

[pbaldy]

No problem. For the record, this line:

Me.Filter = cboSoftwareTitle

would have been

Me.Filter = "[Title] = '" & Me![cboTitle] & "'"

 

[rlmorgan]

I will keep that in mind. Is it true that this method may cause upgrade problems going from '03 to '07?

 

[pbaldy]

As far as I know, setting the filter property still works in 2007. Have you heard otherwise? I don't have 2007 on this PC or I'd test it.

 

[rlmorgan]

The only thing I was going by was a few comments found in other posts while researching the above problem. I do have Access '07 and '10 available but neither is approved for use in our environment yet. Just watch, as soon as I finish this project, management will want me to upgrade the DB to 2010.