Filter subform based on listbox 1

[jw5107]

I have a listbox on a form, that when a record is selected is filters data on a subform which is also on the form.
This what is being used:
Me.PartsSubFrm.Form.Filter = "[DocNo] = " & Me.DocList.Column(0)
Me.PartsSubFrm.Form.FilterOn = True

The problem is I need for the filter to work off of 2 fields, not just one. I need for the subform to filter on DocNo and Aircraft. The columns in the listbox are (0) and (4).

Just need help with the syntax - I just can't get it to work!! Any suggestions or examples???

Thanks in advance!!
jw5107

 

[deadzed]

There are probably prettier and better ways of doing this but, one way you could accomplish this is to create 2 unvisible text boxes on your form. Change the control source of one to =Me.DocList.Column(0) and the other to =Me.DocList.Column(1).

Base your subform off of a query with criteria for each field you need to filter like [Forms].[PartsMainForm].[UnvisibleBox1] for your first field and [Forms].[PartsMainForm].[UnvisibleBox2] for your second field.

Then, on your ComboBox do a On Change event (On Click or Double Click event if it's a ListBox) and for the code use something like this:
Forms("PartsMainFrm").Form.Controls("PartsSubFrm").Requery

 

[sdk]

Hi

Does the list have a single value that can be used as a key? - that the Doc-Aircraft is a unique intersection suggests so - then include that key in your subform, make it the first col in you list (hidden if necessary) and link to the subform in a child-master relationship.

Alternatively (1), use hidden text boxes as suggested by deadzed, but again use the child-master properties to link the subform to the parent.

Alternatively (2), does this work:
Me.PartsSubFrm.Form.Filter = "[DocNo] = " & Me.DocList.Column(0) & " And [Aircraft] = " & Me.DocList.Column(4)
Me.PartsSubFrm.Form.FilterOn = True

I would avoid the last option - it seems trivial but it looks to overly complicate the issue. Using the child-matser properties avoids worrying about requeries and strictly controls the linked datasets

cheers

s

 

[jw5107]

deadzed,
Thanks for the example. Worked just fine!!! Star for ya!!
Let me throw another one out there....
The subform is used for the user to add comments about the availability of a given part. A single part can be assigned to multiple aircraft - so there for there can be different "availability comments" for a single part that is assigned to multiple aircraft. The subforms record source is a select query that uses an outer join. I want to see all the aircraft scheduled for maintenance and the parts required..... The example you provided works perfectly if the scenario were one part - one aircraft. I need to be able to make seperate comments for a part that is assigned to several different aircraft. Hope this is making sense!!
Just havin' difficulties setting this up so that the right comments are shown for different aircraft on the subform- even if its the same part.
Any suggestions or examples??
Thanks again!!
jw5107

 

[deadzed]

Shooting from the hip here, but you can give this a try. You could creat an entirely new table (something like tblPartsComments). It would have 3 fields: CommentID - Autonumber, PartID - Number, and Comment - (either text or memo depending on the how long you want the comments to be)

In your Relationship window you would create a one-to-many connection from PartID in your Parts table to PartID in your Comments table.

You could then create a subfrom on your Parts form based on your Comments table (actually I do a query for everything because I like to sort it by name, date, or some other criteria but that's up to you). The subform would be linked to the parent form by the PartID. This should allow you to create and display multiple comments about your part.

If that doesn't work, let me know and I'll try to think of something else.