Extremely simple form needs filtering

[Guest]

My form is ultra simple -- but then, be patient with me -- I am a VFP
programmer, not an Access programmer, so I understand relational DATA, but
not how to tie it together in Access <g>!

Here it is: Combo box, based on a table, Company (6 records -- that's right
SIX!)
CompanyID AutoNum
Company Text

SubForm containing Listbox based on a table Payload (3 records -- in case
they change criteria)
PayloadID AutoNum
Payload Text

Subform containing Listbox based on query displaying Item, filtered by
Company and Payload

In this mix is a Table called Group which ties Company and payload together
by ID only, and the Group table is supposed to drive the query for the Item
listbox.

The Item table has fields
ItemID AutoNum
GroupID Numeric
Item Text
Reference_Address Hyperlink

The Reference_Address field is present in the query driving the subform, but
not displayed (hidden by putting it on the right and turning off the
horizontal scroll). It's content is called with a button which I have working
on a different form and I will copy for this form.

Now, with any luck I've give y'all enough information!

The problem is that when I select a different company in the Combobox or a
different payload, nothing changes in the Item list -- all items are
displayed all the time. Therefore, obviously I have no filtering in place.

I sit obediently at the feet of the gurus awaiting further light and
knowledge.

 

[Guest]

To clarify -- the second Subform displays a GRID, the components of which
include the Items field and the Reference_Address field of the Items table
(from the query).

I know, I know -- I should have stuck to VFP and left all you nice Access
people alone -- but believe me, I was just as annoying among the VFP support
folks <VBG>!!

 

[Marshall Barton]

My form is ultra simple -- but then, be patient with me -- I am a VFP
programmer, not an Access programmer, so I understand relational DATA, but
not how to tie it together in Access <g>!

Here it is: Combo box, based on a table, Company (6 records -- that's right
SIX!)
CompanyID AutoNum
Company Text

SubForm containing Listbox based on a table Payload (3 records -- in case
they change criteria)
PayloadID AutoNum
Payload Text

Subform containing Listbox based on query displaying Item, filtered by
Company and Payload

In this mix is a Table called Group which ties Company and payload together
by ID only, and the Group table is supposed to drive the query for the Item
listbox.

The Item table has fields
ItemID AutoNum
GroupID Numeric
Item Text
Reference_Address Hyperlink

The Reference_Address field is present in the query driving the subform, but
not displayed (hidden by putting it on the right and turning off the
horizontal scroll). It's content is called with a button which I have working
on a different form and I will copy for this form.

Now, with any luck I've give y'all enough information!

The problem is that when I select a different company in the Combobox or a
different payload, nothing changes in the Item list -- all items are
displayed all the time. Therefore, obviously I have no filtering in place.

It sounds like you need to requery the Items object when the
company combo box value changes. For user entry changes,
use the combo box's AfterUpdate event:

Sub cboCompany_AfterUpdate()
ME.subformcontrol.FORM.companylistbox.REQUERY
End Sub

To sync the list box when you navigate form one main record
to another, use the same line of code in the main form's
Current event.

 

[Marshall Barton]

To clarify -- the second Subform displays a GRID, the components of which
include the Items field and the Reference_Address field of the Items table
(from the query).

Pleasu use the actual names of the objects you are talking
about. Second subform is just not specific enough to remove
all ambiguity.

Be careful, a Grid control is a VB control, not an Access
control. I think some people have made that arrangement
work, but, especially for beginners, you should stick with
the native Access features.

 

[Guest]

I learned YEARS ago that getting tech support is a lot like saying confession
-- if you whine enough, long enough, you will make progress on your own <BWG>.

The GRID to which I made reference in my lst reply to myself is gone away.
Now I have a nice simple sub form with two Textbox fields on it, one visible,
one not, populated courtesy of this simple SQL code:

SELECT Reference.Item, Reference.Reference_Address FROM Reference;

(Reference is the Query that knows all about the Company IDs and the Payload
IDs.)

So, now what I need is code to apply somewhere so that when there is a
Change in the value of the Company name Combo box (whose code is SELECT
Company.CompanyID, Company.Company FROM Company; That CompanyID value will
chug across to the Reference Query and filter it down to just the items for
the selected company.

Then, from the Payload Listbox, which is managed by the Table Payload, I
would like to further filter the Reference Query based on which of the three
items is currently selected there.

I sincerely hope I am making this a tiny bit clear <g>!

 

[Guest]

Thanks, Marsh -- I'll give that a shot, and return and report. Looks like
this message and my last self-flagellating message must have crossed in the
mail <g>!

 

[Guest]

I think that perhaps the key may lie in my Reference Query, which is the core
of all the magic. Here it is in all its "glory"

SELECT Company.CompanyID, Payload.PayloadID, Item.Item, Item.Reference_Address
FROM Company INNER JOIN (([Group] INNER JOIN Item ON
Group.GroupID=Item.GroupID) INNER JOIN Payload ON
Group.PayloadID=Payload.PayloadID) ON Company.CompanyID=Group.CompanyID
ORDER BY Company.Company, Payload.Payload, Item.Item;

You will note an absence of any WHERE clause. That would be because I have
no idea either where to put it, or what it should say <g>! My suspicion is
that if belongs just before the ORDER clause, but this is really the guy that
we want to REQUERY, isn't it, when we poke around on the combo box and
listbox? However I have no clue how to reference the CompanyID or the
PayloadID from within the Query as a filter -- or am I completely off the
mark?

Don't worry -- I'll get it eventually -- just about the time I won't be
temping in Access any longer, no doubt <g>!

 

[Marshall Barton]

The query used as the Record Source of the item subform
definitely needs a WHERE clause to tell it how to filter the
data to the selection in the combo box:

SELECT Reference.Item, Reference.Reference_Address
FROM Reference
WHERE CompanyID = Forms!mainform.cboCompany

I don't quite follow what you want in the other query.
Maybe(?) you want to add a WHERE clause to the Reference
query?? If so, it might(?) look similar to:

SELECT Company.CompanyID, Payload.PayloadID, Item.Item,
Item.Reference_Address
FROM Company
INNER JOIN (([Group]
INNER JOIN Item
ON Group.GroupID=Item.GroupID)
INNER JOIN Payload
ON Group.PayloadID=Payload.PayloadID)
ON Company.CompanyID=Group.CompanyID
WHERE PayloadID = Forms!formwithlistbox.lstPayload
AND [Company ID] = Forms!mainform.cboCompany
ORDER BY Company.Company, Payload.Payload, Item.Item