Forms and Subforms - sharing info between text/combo boxes

[Guest]

I've been banging my head against a few roadblocks in an Access 2000 database
I'm creating. I'm a relative newby and haven't created anything in 4 years,
so please bear with me. Database Description: The single data input form has
a primary form with 3 tabs; 2 of the tabs have subforms (I'll call level 2)
and 1 of these subforms has subforms (level 3). The primary form has agency
info, with a Tab1/level 2 subform that has multiple agency contacts (one per
continuous record). Tab2/level 2 subform with shipment info that has a combo
box to access only those contacts associated with that agency. Problem:
currently this continues to list all agencies' contacts as a single dropdown
list and doesn't discriminate by agency. 2nd Problem: In the agency contact
subform, I have two combo boxes that list the category and subcategory,
respectively, of where they heard about us (e.g., "Advertising" and "HR
Magazine"). I want the subcategory box only to list those items associated
with the category chosen in the first box. Problem: I found assistance on
this site re: basing one combo box on another and got it to work when the
ONLY the subform is open, but I got a parameter box prompt when I tried to
access it through the primary input form. I checked control box names for
correct name already. Can anyone help? I've been beating myself against
this rock for 3 days and getting nowhere. My boss wants this up and running
pronto and I'm stuck. Thanks so much.

 

[tina]

when you're filtering a value in a subform combo box, using a value from a
control in the open form/subform, the form reference can be tricky.

if the "other" control is in:

1) the main form, the subform combo's criteria would be

Me!MainFormName!ControlName

2) the same subform or another subform at the same level, the criteria would
be:

Me!MainForm!SubformControlName.Form!ControlName

3) a "level 2" subform, the criteria would be

Me!MainForm!SubformControlName.Form!SubformControlName.Form!ControlName

re the problem of "contacts combo box lists all agencies' contacts, instead
of specific agency contacts", the solution is the same solution you're
already using for category/subcategory - filter the contacts combo box.
again, you need to use the correct syntax to reference the filtering
control.

hth

 

[Guest]

I'm afraid this didn't help either. Is this VBA code? In the books, the SQL
stuff and the Query Tables refer to forms like so...

[Forms]![Subform]![ControlName]

In the one problem (and I know the answer to the first will lead to the
second), I want to use the [Agency] control in my [PRIMARY_INPUT_FORM] to
filter only contacts associated with that agency under the [AgencyContact] in
the [Shipments_subform]. The source of the contacts is the
[Contact_Name_and_Title_subform] under the same primary form and drawing from
the [Contact_Name] control .

I have no idea whether this is a workaround but, I've created another field
(that won't be visible) in the [Shipments_subform] that draws the value from
the [Agency] control in my [PRIMARY_INPUT_FORM] -- basically a duplicate
field that is populating accurately -- to try and use THAT as the filter for
populating [AgencyContact] in the [Shipments_subform]. I'm still getting a
parameter box that pops up when I shift to that subform; of course, if I fill
in the parameter box with the accurate agency, it filters properly, but keeps
the same list across all primary agency records -- doesn't appear to be
linking to the Agency (primary data).

O.K. -- so that was the long explanation that states, "I'm still stuck where
I was." If you have the time (and I know how busy everyone is!), maybe you
can give me a hypothetical blow-by-blow description of how you do this in
Access 2000.

Thanks for the help - Margie

 

[tina]

I'm afraid this didn't help either. Is this VBA code? In the books, the
SQL

stuff and the Query Tables refer to forms like so...

[Forms]![Subform]![ControlName]

specifying the Form object of a subform is not an absolute must in all
situations, but when the syntax doesn't work without it, then i try adding
it. generally speaking, i usually don't use it in modules, but i do use it
in calculated controls on forms. in SQL...well, as i said, if all the
form/subform/control names are correct in the reference but it still doesn't
work, then i try including the subforms' Form object reference.

other than that, i don't know what else to offer you, Margie. double- and
triple-check all form/subform/control names, make sure you're referencing
the correct field (usually a combo box RowSource is the parent table in a
parent/child relationship - so the filtering value is usually the primary
key field or a foreign key field of the child table, set as criteria on the
parent table's matching foreign key field), and make sure your syntax is in
the correct sequence - form/control, or form/subform/control, or
form/subform/subsubform/control.

if i had time, i'd offer to look at your db to see what the problem is - but
i'm just too busy right now, sorry! good luck.

I'm afraid this didn't help either. Is this VBA code? In the books, the SQL
stuff and the Query Tables refer to forms like so...

[Forms]![Subform]![ControlName]

In the one problem (and I know the answer to the first will lead to the
second), I want to use the [Agency] control in my [PRIMARY_INPUT_FORM] to
filter only contacts associated with that agency under the [AgencyContact] in
the [Shipments_subform]. The source of the contacts is the
[Contact_Name_and_Title_subform] under the same primary form and drawing from
the [Contact_Name] control .

I have no idea whether this is a workaround but, I've created another field
(that won't be visible) in the [Shipments_subform] that draws the value from
the [Agency] control in my [PRIMARY_INPUT_FORM] -- basically a duplicate
field that is populating accurately -- to try and use THAT as the filter for
populating [AgencyContact] in the [Shipments_subform]. I'm still getting a
parameter box that pops up when I shift to that subform; of course, if I fill
in the parameter box with the accurate agency, it filters properly, but keeps
the same list across all primary agency records -- doesn't appear to be
linking to the Agency (primary data).

O.K. -- so that was the long explanation that states, "I'm still stuck where
I was." If you have the time (and I know how busy everyone is!), maybe you
can give me a hypothetical blow-by-blow description of how you do this in
Access 2000.

Thanks for the help - Margie

when you're filtering a value in a subform combo box, using a value from a
control in the open form/subform, the form reference can be tricky.

if the "other" control is in:

1) the main form, the subform combo's criteria would be

Me!MainFormName!ControlName

2) the same subform or another subform at the same level, the criteria would
be:

Me!MainForm!SubformControlName.Form!ControlName

3) a "level 2" subform, the criteria would be

Me!MainForm!SubformControlName.Form!SubformControlName.Form!ControlName

re the problem of "contacts combo box lists all agencies' contacts, instead
of specific agency contacts", the solution is the same solution you're
already using for category/subcategory - filter the contacts combo box.
again, you need to use the correct syntax to reference the filtering
control.

hth


form
has level
2) (one
per a
combo