Prev: how do i get a template in MS access for managing employees & payr
Next: Need some advanced help on this one!
From: alex on 20 Nov 2009 07:45
Cascading ComboBox on SubForm

Hello,

Using Access ’03…

I hope this doesn’t confuse too many people (I know I am).

I have a subform (in ds view) on a main form. The subform has about 8
fields, 5 of which are cascading combo boxes.

The records in the subform will never be null, the user is only able
to modify or delete the record.

The combo boxes are all based on 1 table that could be considered a
key for the values contained in all of the 5 combo boxes (there are
over 20k records in that table).

For example the rowsource of each combo box is bound to its respective
field in the table and the other 4 fields are used as criteria.
Something like this:
Like [Forms]![frmMyMainForm]![sfrmMySubform].[form]![FieldID]

What I just described above seems to work fine when the first record
is entered from the main form; i.e., each combo box value is limited
to the value in the other 4 combo boxes!

The problem: when the second and subsequent records are entered, the
values of the combo boxes appear to relate (are based off of) the
first record. I.e., the combo boxes are not being evaluated based on
each individual record, but appear to only relate to the first
record’s value!

If anyone has a suggestion as to what I may be doing wrong, please let
me know.

Thanks,
alex
From: alex on 20 Nov 2009 08:07
On Nov 20, 7:45 am, alex <sql_...(a)yahoo.com> wrote:
> Cascading ComboBox on SubForm
>
> Hello,
>
> Using Access ’03…
>
> I hope this doesn’t confuse too many people (I know I am).
>
> I have a subform (in ds view) on a main form.  The subform has about 8
> fields, 5 of which are cascading combo boxes.
>
> The records in the subform will never be null, the user is only able
> to modify or delete the record.
>
> The combo boxes are all based on 1 table that could be considered a
> key for the values contained in all of the 5 combo boxes (there are
> over 20k records in that table).
>
> For example the rowsource of each combo box is bound to its respective
> field in the table and the other 4 fields are used as criteria.
> Something like this:
> Like [Forms]![frmMyMainForm]![sfrmMySubform].[form]![FieldID]
>
> What I just described above seems to work fine when the first record
> is entered from the main form; i.e., each combo box value is limited
> to the value in the other 4 combo boxes!
>
> The problem:  when the second and subsequent records are entered, the
> values of the combo boxes appear to relate (are based off of) the
> first record.  I.e., the combo boxes are not being evaluated based on
> each individual record, but appear to only relate to the first
> record’s value!
>
> If anyone has a suggestion as to what I may be doing wrong, please let
> me know.
>
> Thanks,
> alex

I think I may have found my solution...requerying the combo boxes in
the on current event of the subform.
alex
From: Lynn Trapp on 20 Nov 2009 08:26
Alex,
Are you requerying the comboboxes when the new record is created?

--
Lynn Trapp


"alex" wrote:

> Cascading ComboBox on SubForm
>
> Hello,
>
> Using Access '03…
>
> I hope this doesn't confuse too many people (I know I am).
>
> I have a subform (in ds view) on a main form. The subform has about 8
> fields, 5 of which are cascading combo boxes.
>
> The records in the subform will never be null, the user is only able
> to modify or delete the record.
>
> The combo boxes are all based on 1 table that could be considered a
> key for the values contained in all of the 5 combo boxes (there are
> over 20k records in that table).
>
> For example the rowsource of each combo box is bound to its respective
> field in the table and the other 4 fields are used as criteria.
> Something like this:
> Like [Forms]![frmMyMainForm]![sfrmMySubform].[form]![FieldID]
>
> What I just described above seems to work fine when the first record
> is entered from the main form; i.e., each combo box value is limited
> to the value in the other 4 combo boxes!
>
> The problem: when the second and subsequent records are entered, the
> values of the combo boxes appear to relate (are based off of) the
> first record. I.e., the combo boxes are not being evaluated based on
> each individual record, but appear to only relate to the first
> record's value!
>
> If anyone has a suggestion as to what I may be doing wrong, please let
> me know.
>
> Thanks,
> alex
> .
>
From: alex on 20 Nov 2009 09:30
On Nov 20, 8:26 am, Lynn Trapp <LynnNoSpam.TrappNoS...(a)hotmail.com>
wrote:
> Alex,
> Are you requerying the comboboxes when the new record is created?
>
> --
> Lynn Trapp
>
>
>
> "alex" wrote:
> > Cascading ComboBox on SubForm
>
> > Hello,
>
> > Using Access ’03…
>
> > I hope this doesn’t confuse too many people (I know I am).
>
> > I have a subform (in ds view) on a main form.  The subform has about 8
> > fields, 5 of which are cascading combo boxes.
>
> > The records in the subform will never be null, the user is only able
> > to modify or delete the record.
>
> > The combo boxes are all based on 1 table that could be considered a
> > key for the values contained in all of the 5 combo boxes (there are
> > over 20k records in that table).
>
> > For example the rowsource of each combo box is bound to its respective
> > field in the table and the other 4 fields are used as criteria.
> > Something like this:
> > Like [Forms]![frmMyMainForm]![sfrmMySubform].[form]![FieldID]
>
> > What I just described above seems to work fine when the first record
> > is entered from the main form; i.e., each combo box value is limited
> > to the value in the other 4 combo boxes!
>
> > The problem:  when the second and subsequent records are entered, the
> > values of the combo boxes appear to relate (are based off of) the
> > first record.  I.e., the combo boxes are not being evaluated based on
> > each individual record, but appear to only relate to the first
> > record’s value!
>
> > If anyone has a suggestion as to what I may be doing wrong, please let
> > me know.
>
> > Thanks,
> > alex
> > .- Hide quoted text -
>
> - Show quoted text -

Lynn, I believe the subform is requeried when a new record is
entered. The combo boxes are requeried in the on current event of the
subforma and in the after update event of each control on the subform.
alex
From: Lynn Trapp on 20 Nov 2009 13:07
I'm glad you found a solution, Alex.
--
Lynn Trapp


"alex" wrote:

>
> I think I may have found my solution...requerying the combo boxes in
> the on current event of the subform.
> alex
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: how do i get a template in MS access for managing employees & payr
Next: Need some advanced help on this one!