combo box problems

[franc sutherland]

Hi,

I am using Access 2003. I am having a problem with a pair of combo
boxes. I want the second one (cbo_service) to have it's row source
limited by the value I select in the first one (cbo_supplier). I have
added the criteria in the query for cbo_service that it is limited by
the value selected in cbo_supplier, using the full location of forms!
frm_x!cbo_servi ce. However, when I click on the pull-down, it brings
up the select criteria dialog box. I put in an after update event to
show a message box with the value selected in cbo_supplier, and it
comes up fine. However, the value from cbo_supplier is not making it
to the query for cbo_service in time, if you see what I mean.

The background to this is that I have a main form, called frm_event.
This is based on a table which has the key event_id. In this there is
a subform which is based on a linking table which has the fields
link_id, event_id, supplier_id and service_id. When I type in the
name of the event on the main form, the event_id autonumber comes up
on the main form and is carried through to the event_id box in the
subform. I then go into the subform and select the supplier from
cbo_supplier, and the supplier_id number comes through fine. When I
then go into cbo_services, it asks me for the supplier_id with a
select criteria dialog box. If I remove the criteria, it lists all
the services for all suppliers. I need to limit it to the supplier
just selected in the previous combo box.

All help appreciated!

Thanks,

Franc.

[Salad]

Re: combo box problems

franc sutherland wrote:

Hi,
>
I am using Access 2003. I am having a problem with a pair of combo
boxes. I want the second one (cbo_service) to have it's row source
limited by the value I select in the first one (cbo_supplier). I have
added the criteria in the query for cbo_service that it is limited by
the value selected in cbo_supplier, using the full location of forms!
frm_x!cbo_servi ce. However, when I click on the pull-down, it brings
up the select criteria dialog box. I put in an after update event to
show a message box with the value selected in cbo_supplier, and it
comes up fine. However, the value from cbo_supplier is not making it
to the query for cbo_service in time, if you see what I mean.
>
The background to this is that I have a main form, called frm_event.
This is based on a table which has the key event_id. In this there is
a subform which is based on a linking table which has the fields
link_id, event_id, supplier_id and service_id. When I type in the
name of the event on the main form, the event_id autonumber comes up
on the main form and is carried through to the event_id box in the
subform. I then go into the subform and select the supplier from
cbo_supplier, and the supplier_id number comes through fine. When I
then go into cbo_services, it asks me for the supplier_id with a
select criteria dialog box. If I remove the criteria, it lists all
the services for all suppliers. I need to limit it to the supplier
just selected in the previous combo box.
>
All help appreciated!
>
Thanks,
>
Franc.
>

Let's say you have Combo1 in the form MainForm. It's rowsource could be
Select OrderNumber, OrderDesc From Orders

The rowsource for Combo2 might be
Select OrderDetailID, Item, Qty From OrdDetail Where
OrderNumber = Forms!MainForm! OrderNumber

In the AfterUpdate event to Combo1 enter
Me.Combo2.Reque ry

The requery will synch the 2 together. It's possible your criteria for
Combo2 is incorrect or you aren't requerying.

[franc sutherland]

Re: combo box problems

On Sep 18, 7:02 pm, Salad <o...@vinegar.c omwrote:

franc sutherland wrote:

Hi,

>

I am using Access 2003.  I am having a problem with a pair of combo
boxes.  I want the second one (cbo_service) to have it's row source
limited by the value I select in the first one (cbo_supplier).  I have
added the criteria in the query for cbo_service that it is limited by
the value selected in cbo_supplier, using the full location of forms!
frm_x!cbo_servi ce.  However, when I click on the pull-down, it brings
up the select criteria dialog box.  I put in an after update event to
show a message box with the value selected in cbo_supplier, and it
comes up fine.  However, the value from cbo_supplier is not making it
to the query for cbo_service in time, if you see what I mean.

>

The background to this is that I have a main form, called frm_event.
This is based on a table which has the key event_id.  In this there is
a subform which is based on a linking table which has the fields
link_id, event_id, supplier_id and service_id.  When I type in the
name of the event on the main form, the event_id autonumber comes up
on the main form and is carried through to the event_id box in the
subform.  I then go into the subform and select the supplier from
cbo_supplier, and the supplier_id number comes through fine.  When I
then go into cbo_services, it asks me for the supplier_id with a
select criteria dialog box.  If I remove the criteria, it lists all
the services for all suppliers.  I need to limit it to the supplier
just selected in the previous combo box.

>

All help appreciated!

>

Thanks,

>

Franc.

>
Let's say you have Combo1 in the form MainForm.  It's rowsource could be
        Select OrderNumber, OrderDesc From Orders
>
The rowsource for Combo2 might be
        Select OrderDetailID, Item, Qty From OrdDetail Where
                OrderNumber = Forms!MainForm! OrderNumber
>
In the AfterUpdate event to Combo1 enter
        Me.Combo2.Reque ry
>
The requery will synch the 2 together.  It's possible your criteria for
Combo2 is incorrect or you aren't requerying.

Hi Salad,

You were right, the criteria for Combo 2 was wrong. When I built the
criteria using the expression builder within the query it writes out
the syntax differently I would use in the VB editor.

It writes it as:

[Forms]![frm_main]![frm_subform].[Form]![cbo_2]

So, because the combo boxes are in a subform, the referencing is
different?

Thanks, its working now.

All the best,

Franc.