Custom Filter Forms

[YellowOnline]

Hello all,

I feel bad about posting again, but this Access DB is driving me crazy. I finished my database, everything is fully working but it lacks some user friendliness. A thing I should really add is a custom filter. Let me show you first, schematically, how one of my forms looks (this was done very fast in MSPaint):

Let's say that the main form always shows a shop, a shopnumber and a location. The subforms show different kinds of information on that shop. The controls in the form footer make it possible for the user to navigate.

The thing I'm missing is a way to make the user in the footer choose, e.g. 1 specific location so the records browsed are only those in that location. Another option should be a simple textbox where you can enter a shopnumber so the record shown is exactly that one.

I've been experimenting and looking up info on this for hours, but I fail to find a solution for this. The only thing I seem to find is how to filter tables and queries or subforms. This is not what I want here.

Thank you for your opinions,

Yellow

 

[PHV]

Why not simply follow the combo wizard ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[YellowOnline]

Hi PHV,

For the shop number this could indeed be a solution, as this number is unique. It would point directly to the record I want. For the Shops it is no solution however. It would just lead me to the first record that has a Shopname = to the one in the cbo.
So your suggestion is not exactly what I wanted: a real filter. It is an improvement however - for which I thank you - je te remercie.
Or didn't I understand the cbo comment like I should? As far as I know, the only thing a cbo-wizard does is populating the cbo. I don't see any option to make it a special filter cbo.

 

[PHV]

Have a look at the Filter and FilterOn properties of the Form object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeeThru]

Consider using a additional form as a helper.

Create a form with a listbox.
Use controls on the form to filter the contents of the listbox to return matching records.
(i.e. set the SQL for the listbox to
"SELECT * FROM SHOPS WHERE [City]='" & me![citysearch] & "'")
Set the list box on doubleclick (or whatever) to open the selected record in the main form (by using the records unique identifier)

That way the operators can get a quick overview of how many records are in their search, perform fast searches on the data, and still quickly select any one record.

Call the new form 'Search' and add a button that opens it in Modal from your main form.



SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[YellowOnline]

I'm trying SeeThru's suggestion as I really can't figure PHV's suggestion out. I'm not sure about the right syntax though. Right now I tried variations of

SELECT * WHERE [Shop]=me![cboFilterShop].Value

... but I keep getting syntax errors. All info on the right syntax that I can find limits itself to numeric or text values. They nowhere mention the syntax for when you should get the condition from a cbo :/

 

[YellowOnline]

Uh, something went wrong copy/pasting. I didn't forget the FROM part, it just didn't got pasted here But I might have found the problem... apparently my cbo decided to forget the name I gave it and get a random "combo10" name.

 

[YellowOnline]

OK, I finally did it this way, lending some code snippets from a form in my DB made by someone else:

Private Sub cmdFilter_Click()
    Dim AllFilter As String
    Dim Filter1 As String
    Dim Filter2 As String
    Dim Filter3 As String
    Dim Filter4 As String
    Dim Filter5 As String
    
    If cboA.Column(1) THEN Filter1 = " AND Field1 = " & cboA.Column(1)
    If cboB.Column(1) THEN Filter2 = " AND Field2 = " & cboB.Column(1)
    If cboC.Column(1) THEN Filter3 = " AND Field3 IN (SELECT Field3 FROM tblDummy WHERE Dummycondition = " & cboC.Column(1) & ")"
    If cboD.Column(1) THEN Filter4 = " AND Field4 = " & cboD.Column(1)
    If txtE.Value THEN Filter5 = "AND (Left(Field5, 3) = '" & txtE.Value & "')"
    End If
    AllFilter = "(1)" + Filter1 + Filter2 + Filter3 + Filter4 + Filter5
    
    Filter = AllFilter
    FilterOn = True
    Requery
End Sub

I think this example (I removed the real names and fields) can be useful for other people. On a sidenote, Filter5 is something really specific for what I needed.

 

[assets]

hi filter and on filter work well. Look on the web for some examples of how this works. I used it to filter diderent departments so you can only see that department data.

Never give up never give in.

There are no short cuts to anything worth doing