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!

 

[TheAceMan1]

How are ya Delindan . . .

1st you say:

[blue]I would like the results in the unselected list boxes to filter when another is updated.[/blue]

Then you say:

[blue][purple]I would like to be able to just choose a crop[/purple] but no selections appear in the crop list box until a breeder is chosen.[/blue]

[ol][li]What you need to do is set the listboxes to all possibilities on open of the form.[/li]
[li]Then as you make selections ... concatenate independently in a [blue]WHERE[/blue] clause each listbox. This allows you to build the proper [blue]WHERE[/blue] clause of the query.[/li][/ol]
Can you see where I'm going!

[blue]Your Thoughts? . . .[/blue]



See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Delindan]

Hi AceMan1...okay searching around here and found the on open event and requeried the values of each of the list boxes and it doesn't do anything. I want the list boxes to filter their list based on what is selected in the other boxes. So when beets are chosen, only those breeders testing beets would show up in the breeder box. I kind of got it to work by putting SELECT DISTINCT crop FROM [crop data] WHERE ((([crop data]!breeder)=Forms![filter form]!lstbreeder)) ORDER BY crop; in each of the row source however it doesn't quite work the way I want because you have to select a breeder to show anything in the crop list box. I'm sure there is a different way to do that part of it. Any thoughts?

 

[dhookom]

I would use the after update event of one listbox to set the RowSource of another listbox and/or the subform

    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]", "'")
    strSQL = "SELECT ... FROM [...] " & strSQL
    Me.Child1.Form.RecordSource = strSQL

I don't know the row sources of each listbox and/or which depend on which other listboxes. Also, it wouldn't hurt to provide the record source of [Child1] and consider changing the name of the subform to something that makes sense.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Delindan . . .

Your asking for two way control. When you make a selection from [blue]lstBreeder[/blue] you want to set the [blue]RowSource[/blue] of [blue]lstCrop[/blue], and when you make a selection from [blue]lstCrop[/blue] you want to set the [blue]RowSource[/blue] of [blue]lstBreeder[/blue]. You can do this by modifying the [blue]WHERE[/blue] clause of the queries involved and writing the new SQL to the rowsource. [blue]lstBreeder[/blue] writes the new SQL for [blue]lstCrop[/blue] and vice versa.

Note that when you do this you'll be performing the equivalent of filtering. So you'll need a button to set you back to the default state of all breeders & crops ... in case the user wants to start over.

And what of the [blue]lst fsspecialist[/blue]? Is it dependent on [blue]lstCrop[/blue] as well?

What ever you choose to do ... we need to see the [blue]RowSource[/blue] of the listboxes. If queries, post the SQL.

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Delindan]

I am really confused but here is what I have.

lstbreeder: rowsource = SELECT DISTINCT breeder FROM [crop data] ORDER BY breeder;
After update =
Private Sub lstbreeder_AfterUpdate()
lstcrop.Requery
lstfsspecialist.Requery
End Sub

lstcrop: rowsource = SELECT DISTINCT crop FROM [crop data] WHERE ((([crop data]!breeder)=Forms![filter form]!lstbreeder)) ORDER BY crop;

After update =
Private Sub lstcrop_AfterUpdate()
lstfsspecialist.Requery
End Sub

lstfsspecialist: rowsource = SELECT DISTINCT [fs specialist] FROM [crop data] WHERE ((([crop data]!breeder)=Forms![filter form]!lstbreeder)) And ((([crop data]!crop)=Forms![filter form]!lstcrop)) ORDER BY [fs specialist];

In my subform, for a source object I tried

Private Sub recsel()

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]", "'")
strsql = "SELECT * FROM [Query.Last 3 Years] " & strsql
Me.Child1.Form.RecordSource = strsql

End Sub

which didn't work. I also don't have anything in linkmaster or link child which I think is a problem. I appreciate the input as you can see I'm a bit confused about this.

 

[Delindan]

One additional thought on this as I was re-examining what I would like this to do. In the main table (actually the only table) that drives this db, 1 breeder can have several crops and several specialists running those crops. It is possible that the user would like to see all the crops and/or specialists they have working on them. Additionally, one might want to see all the breeders who manage a particular crop or what crops a particular specialist manages. Hopefully this makes it more clear

 

[dhookom]

which didn't work" doesn't work here. Please provide your results and possibly debug the code so we know the exact value of strsql prior to setting the RecordSource.

Also, since you are using BuildIN(), I assume the listboxes are multi-select. Is this the case?

You can't use link master/child with multi-select controls.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

When I try to put =recsel() in the source object of the subform, when I change back to the form view, it says 'The record source '=recsel' specified on this form does not exist etc. I don't have the list boxes set up for multi select at this point because I was hoping just to get one selection to work. Also, the list boxes for crop does not populate until breeder is chosen and fsspecialist does not populate until crop is chosen.

 

[Delindan]

In the process of testing different things I decided to try and simplify this and gradually add back. I came to the conclusion that I had something somewhere that was messing. Anyway I created a new form with lstbreeder only and a subform called filteredrecs (I couldn't think of a better name) I added

Private Sub Form_Load()
Call recsel
End Sub

when I print strsql from recsel

Private Sub recsel()

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]", "'")
strsql = "SELECT * FROM [Query.Last 3 Years] " & strsql & ";"
Print strsql
Me.filteredrecs.Form.RecordSource = strsql

End Sub

I get SELECT * FROM [Query.Last 3 Years] WHERE 1=1 ;

It says the syntax is not supported. This is actually advancement over what I had been doing before. Any ideas on the syntax? thanks!

 

[Delindan]

After going to the sql and manually trying the statement I figured out that I needed to get rid of query. from the statment. So now when I run it says it doesn't recognize the form. When I print strsql is a totally runable line its the form. I changed the name back to ME.child1.form.recordsource = strsql and it's still doesn't recognize the form. I going to see if I can check the syntax of the form reference.

 

[Delindan]

the exact error number is 438. doesn't recognize this property or method

 

[dhookom]

Try this code:

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

You can call RecSel() from the after update of each list box.


Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

SELECT * FROM [Last 3 Years] WHERE 1=1 ;
which is a runnable statment. It's having problems with the other part. Ugh! Now I'm getting error 2467...referring to an object that is closed or doesn't exist.

 

[dhookom]

What is "It's having problems with the other part"

Which line is causing "error 2467...referring to an object that is closed or doesn't exist"? I would not call this in the On Open or On Load event of the form. The only time you need to run the code is after something gets changed.


Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

I do want the subform to be populated when you open the form though. Also, if I ignore the error and choose a breeder, I get the same error. The line it is erring on is the Me.Child1.Form.RecordSource = strsql. The strsql is fine the part in front of the equal sign (that's what I mean by other part)

 

[MazeWorX]

Whats the name of your child object? A subform reside in an object refered to as the child ... to the parent form. so in Child1 may reside MyFormName

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

The subform should be populated with all the records since the recordsource doesn't filter out any records.

I thought your subform control name was "filteredrecs" so where is there code that references "Child1"?

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

I changed the name back and forth trying to get it to work. I took the onload out.

 

[dhookom]

And what is the actual name of the subform control? This might or might not be the same as the source object.

Duane
Hook'D on Access
MS Access MVP