Filtered Subform

[Delindan]

I'm attempting to create a form called Filtered Form that has 3 list boxes. These list boxes can either be used or not in any combination. I would like the results in the unselected list boxes to filter when another is updated.

I would like the results of a subform named Child1 to list the filtered results of the 3 list boxes. When none are selected, all records show and when selections are made the results filter accordingly. I have been attempting to do this following examples and didn't get very far. However, here is what I have:

three list boxes, lstbreeder, lstcrop, lst fsspecialist. When I select a breeder (lstbreeder) it filters the next list box appropriately. I would like to be able to just choose a crop but no selections appear in the crop list box until a breeder is chosen.

The second problem is that I have a routine that will build a sql statement out of the results, however I don't know how to use it in the subform...at all. My weak attempt at coding something was the following:

Private Sub Child1_Enter()
Dim strsql As String
Dim qdf As String
strsql = "WHERE 1=1 "
strsql = strsql & BuildIn(Me!lstbreeder, "[Breeder]", "'")
strsql = strsql & BuildIn(Me!lstcrop, "[Crop]", "'")
strsql = strsql & BuildIn(Me!lstfsspecialist, "[FS Specialist]", "'")

If strsql <> "WHERE 1=1 " Then
Me.Filter = strsql
Me.FilterOn = True
Else
Me.FilterOn = False
End If
Me.Refresh
End Sub

Any help would be greatly appreciated!

 

[Delindan]

I'm not sure what the subform control is as opposed to the source object. If it is the sub routine that builds the filter is called recsel().

 

[dhookom]

I'm not sure I can help if you can't determine or understand the difference between the name of the subform control and its source object.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

Well thanks for the help so far. It's difficult to work on forms this way. I suspect I'm missing something pretty obvious but not saying it right. I obviously have some work to do on figuring this out. I'll let you know what I figure out.

 

[dhookom]

If you
- open the main form in design view
- open the properties window
- click on your subform
- make sure you are viewing "All" properties
You will see a "Name" property and a "Source Object" property. All of the code in this thread should use the "Name" property, not the "Source Object" property.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

Thanks for being so patient. I had meetings yesterday and so had to take a break which was probably a good thing. The name is 'filteredrecs' and the source object is 'Query.Last 3 Years'.

 

[Delindan]

OMG...you're going to kill me. I truly wasn't trying to waste your time btw. I cut and paste the form name into the recsel() routine and it worked. I had a misspelling I couldn't see. The only thing I can say is that because I didn't know what I was doing I was looking for a more complicated problem. This is actually a good lesson for me. Sorry I can't do more than apologize.

 

[Delindan]

I feel a bit better now. After trying this a few different ways I realize that I had a misspelling but also the lstbreeder sequence in the sql builder isn't working properly. When I select crops it works and also for fs specialist...but not breeder. The first place I looked was in the sequence builder for lstbreeder but that looks okay. However, when I choose a breeder and look at the sql statement it built it doesn't add it. Here is my code for recsel()

Private Sub recsel()

Dim strsql As String

strsql = "WHERE 1=1 "
strsql = strsql & BuildIn(Me!lstbreeder, "[Breeder]", "'")
strsql = strsql & BuildIn(Me!lstcrop, "[Crop]", "'")
strsql = strsql & BuildIn(Me!lstfsspecialist, "[FS Specialist]", "'")
strsql = "SELECT * FROM [Last 3 Years] " & strsql & ";"
Debug.Print strsql
Me.filteredrecs.Form.RecordSource = strsql

End Sub

 

[Delindan]

I'm sure you're curious about the dialog I'm having with myself here. In trying to figure out what was different about lstbreeder couldn't find any misspellings. However, this listbox did not have multiselect defined as extended. Now it works perfectly. Just goes to show how small stupid oversights and build and be misleading. Thanks for all the input and hanging with my questions