Filter Form Question

[tgilbride]

I have a form that is based a table ("tblPartNumber").

I have another tlble ("tblPOPN") that I would like to use in a filter, if at all possible.

Bascially, tblPartnumber is made of of part number data (pricing, descrition, etc.)

tblPOPN is a table that links a Purchase order to a Part Number.

The form in question is a list of every part number listed one time. I want to be able to filter the form based on a combo box, in which I enter a purchase order number, and the form brings up all Part Numbers on the PO.

Is this possible?

 

[dhookom]

Have you considered a main form with the PO value and a continuous subform based on the tblPOPN to display all the part numbers.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Do what Duane said.
1. Main form unbound
2. Combo box on main form with purchase orders "cmboPurchaseOrders"
3. Subform made of a query linking tblPOPN to tblPartNumber
which I assume there is a field. I assume in this link there is a PurchaseOrderNo.
4. Link the combo to the subform in the subform control
LinkMasterFields:[cmboPurchaseOrders]
linkChildFieldsurchaseOrderNo

No when you pick a purchase order number all related parts show in the subform. Also if you want to add a part to a purchase that happens automatically.

 

[tgilbride]

I have, but then I have to go to several different forms within my database when working with it. I was hoping to avoid that.

Does this mean there is not way to do this?

 

[MajP]

Many ways to do this. You can either store the filter in a global variable, or keep the form with the combo box open.

1) in a standard module at the top
public glblFilter as string
2) In the combo after update
if the purchase order number is text
glblFilter = "PurchaseOrderNo = '" & me.CmboPurchaseOrderNumber & "'"

if numeric
glblFilter = "PurchaseOrderNo = " & me.CmboPurchaseOrderNumber

On a form where you want to apply the global filter

public sub applyFilter()
if not glblFilter = "" then
me.filteron = false
me.filter = glblFilter
me.filterOn = true
end if
end sub

You can also use this to build sql string.

public function getGlblFilter() as string
getGlblFilter = glblFilter
end function

now you can use this in any query.

 

[MajP]

Forgot to say if the form with the combo stays open (even if hidden) then you can reference the criteria directly in a query.

...where PurchaseOrderNo = Forms![yourFormName].cmboPurcaseOrderNo...

 

[tgilbride]

How do I link this global feature to another query that is not open?

That is the real issue. In the AfterUpdate area, I want to be able to see all parts on the form that share the same PO number. However, the PO number is not an open table query.

This is a bit confusing, am I explaining it right?

 

[MajP]

If you leave the form with the combo open (visible or invisible) you can simply have this as the queries criteria.

purchaseOrderNo = Forms![yourFormName].cmboPurcaseOrderNo

When you open the query it pull the value from the form.

If you have to close the form with the combo then you can use the global variable to store the filter and you can call it at anytime. You can have the form's on load event call applyFilter.

 

[tgilbride]

Thanks for the help. Let me process this for a little bit and see if I can make this work.

I will let you know.

 

[MajP]

Can you describe, how exactly you want this to work? There are several different ways to do this, and if you give more details it will help to describe the preferred solution.

1) Is the combo on a pop up?
2) Are you popping open a new form, report, query?
3) Is the filter applied to the current form?
4) Does the filter get applied to multiple forms, reports, queries?
5) Does the form with the combo stay open or close?

 

[tgilbride]

I have a form with multiple tabbed subforms. The form is based on a table "tblPartNumber". I want it based on that, because I only want to see each part number 1 time as I scroll through the form. I have another table called tblPOPN. This table references Purchase Orders and all part numbers that are on that PO. I want to be able to type a PO in a combo box on the current form, and from that apply a filter for all Parts that are on that PO. I would like to work with one form from beginning to end. No popups, or multiple forms etc.

 

[dhookom]

You can set the Link Master/Child from the main form to all subforms that contain part number. If you want to search the main form for a specific part number, use the combo box wizard that does this for you.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Can you describe the subforms. Are they linked to the part number on the main form or are they linked to a purchase order?

I want to be able to type a PO in a combo box on the current form, and from that apply a filter for all Parts that are on that PO.

Do you want to filter the subforms or the main form?


If you want to filter the main form for parts that are in a selected purchase order, I would base the main form on a query.
The query would have a where clause something like

Select partID from tblPartNumber where partID in (select PartID from tblPOPN where purchaseOrderID = forms!frmMain.cmboPurshcaseOrder or forms!frmMain.cmboPurshcaseOrder is null)

If nothing is selected in the combo then it returns all records. If you choose a purchase order then it filters the main form to only those parts contained in a selected purchase order.

Not sure if that is what you are trying to do. If you are trying instead to filter the subforms, then you need to clearly explain how the subforms link to the main form. Normally this is all done with subform linking, but I think your many to many relationship might be set up strangely.

 

[tgilbride]

I am trying to filter the main form, so the subforms do not come into play. If I base the main form on a query, then a part number will appear for every PO that it is on. I am trying to avoid that. I am trying to only show the part number 1 time.

 

[MajP]

If I base the main form on a query, then a part number will appear for every PO that it is on.
[/quote}
False!

If you base the main form on a query that inner joins tblPartNumber and tblPoPn then it would be true. No one suggested that. The query I suggested was

Select
partID
From
tblPartNumber
where partID in (select PartID from tblPOPN where purchaseOrderID = forms!frmMain.cmboPurshcaseOrder or forms!frmMain.cmboPurshcaseOrder is null)