Combo Record Filter

iksotof
3StarLounger
Posts: 313
Joined: 04 May 2010, 15:18

Combo Record Filter

Post by iksotof »

Hi


I am using Access through Office 365 and wanting to add a combo on a form to filter to th slected record. I have been out of the loop for afew years and see the option, whe using the wizard, the 'find a record based on a value...' isn't avilable as an option. toa bit of code it is but a few iu have researched do not seem to invoke anything when the combo changes. Maybe it's something different in 365?


Thank you
Darren.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combo Record Filter

Post by HansV »

It's strange that the option is not available. Do you want to filter the form (so that it only shows the records matching what you select), or do you want to jump to the first matching record?
Best wishes,
Hans

iksotof
3StarLounger
Posts: 313
Joined: 04 May 2010, 15:18

Re: Combo Record Filter

Post by iksotof »

Hello Hans

I would like it to jump to the matching record, the combo will take values from the underlying table of the entries on the form, it is a company name, each company has one record.


Thank you

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combo Record Filter

Post by HansV »

Set the Row Source of the combo box to a query or SQL string that selects the (unique) company names from the table.
Leave its Control Source empty - the combo box should be unbound.
Create an After Update event procedure for the combo box. I'll use cboFind as name for the combo box, and CompanyName as name of the relevant field.

Code: Select all

Private Sub cboFind_AfterUpdate()
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.FindFirst "[CompanyName]=" & Chr(34) & Me.cboFind & Chr(34)
    If rst.NoMatch Then
        Beep
    Else
        Me.Bookmark = rst.Bookmark
    End If
End Sub
Best wishes,
Hans

iksotof
3StarLounger
Posts: 313
Joined: 04 May 2010, 15:18

Re: Combo Record Filter

Post by iksotof »

Thank you Hans

School boy error, really am ring rusty. I had been trying to put the search combo on the Dashboard form (hadn't realised) rather than the company form, once I put on the company form, the 'find a record based on a value...' was there!

Really sorry.


Best
Darren.