Forms, Subforms and ComboBoxes....

I hope that someone can help......
I am working on a database that stores information on Funds. Each Fund is
unique, but can have many different series. The price of each series needs to
be updated and a record stored for it every month.
I need to create a form whereby the user can select a Fund Name in a combo
box - the fund name should only appear once in the combo box.
This selection will mean that the user can now select the series number from
another combo box. These numbers in this box must only relate to the Fund in
the previous combo box.
Having selected the series number from the combobox the subform where the
user will the new months price show information only for this series of this
fund....This subform is the only area of the form that should be updating a
table, the main form is basically record navigation
I just can't get it to work for me, I'm sure I am not doing something really
stupid....Please help

"George" wrote in message

I hope that someone can help......
I am working on a database that stores information on Funds. Each
Fund is unique, but can have many different series. The price of each
series needs to be updated and a record stored for it every month.
I need to create a form whereby the user can select a Fund Name in a
combo box - the fund name should only appear once in the combo box.
This selection will mean that the user can now select the series
number from another combo box. These numbers in this box must only
relate to the Fund in the previous combo box.
Having selected the series number from the combobox the subform where
the user will the new months price show information only for this
series of this fund....This subform is the only area of the form that
should be updating a table, the main form is basically record
navigation
I just can't get it to work for me, I'm sure I am not doing something
really stupid....Please help

You must have three tables to represent this information:

Funds, related one to many with ...
FundsSeries, related one to many with ...
FundsSeriesPrices

Your first combo box, for choosing the Fund, is based on the Funds
table. Your second combo box, for choosing the Series, will be based on
a query of the FundsSeries table that uses a reference to the first
combo box as a criterion on the FundID field. That reference will have
the general form, "[Forms]![FormName]![ComboBoxName]".

The crucial thing is that, in the AfterUpdate event of the first combo
box, you execute a statement that requeries the second combo box. The
event procedure might conceivably look like this:

Private Sub cboFund_AfterUpdate()

Me.cboSeries.Requery

End Sub

Your subform will be based on the table FundsSeriesPrices, and will use
both cboFund and cboSeries (or whatever you name these controls) as Link
Master Fields, with appropriate Link Chld Fields. That way, it will
show only records for the chosen fund and series.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk thank you for your help....but i haven't quite got it to work
yet.....The problem lies i think with the first combo box, this is related to
my funds table and hence when i start the form it was a text box that i
converted into a combo box, the combo does contain exactly the data I want it
to however I cannot select any of options from within the combo box and hence
I can't really check whether the next part is working...do you know why this
might be?

"Dirk Goldgar" wrote:

"George" wrote in message

I hope that someone can help......
I am working on a database that stores information on Funds. Each
Fund is unique, but can have many different series. The price of each
series needs to be updated and a record stored for it every month.
I need to create a form whereby the user can select a Fund Name in a
combo box - the fund name should only appear once in the combo box.
This selection will mean that the user can now select the series
number from another combo box. These numbers in this box must only
relate to the Fund in the previous combo box.
Having selected the series number from the combobox the subform where
the user will the new months price show information only for this
series of this fund....This subform is the only area of the form that
should be updating a table, the main form is basically record
navigation
I just can't get it to work for me, I'm sure I am not doing something
really stupid....Please help

You must have three tables to represent this information:

Funds, related one to many with ...
FundsSeries, related one to many with ...
FundsSeriesPrices

Your first combo box, for choosing the Fund, is based on the Funds
table. Your second combo box, for choosing the Series, will be based on
a query of the FundsSeries table that uses a reference to the first
combo box as a criterion on the FundID field. That reference will have
the general form, "[Forms]![FormName]![ComboBoxName]".

The crucial thing is that, in the AfterUpdate event of the first combo
box, you execute a statement that requeries the second combo box. The
event procedure might conceivably look like this:

Private Sub cboFund_AfterUpdate()

Me.cboSeries.Requery

End Sub

Your subform will be based on the table FundsSeriesPrices, and will use
both cboFund and cboSeries (or whatever you name these controls) as Link
Master Fields, with appropriate Link Chld Fields. That way, it will
show only records for the chosen fund and series.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Sorry - I know what i did wrong - my text box was bound. However i still
can't get the form to select anything in the subform despite the fact that
the linked fields are matching and i think correct...

"George" wrote:

I hope that someone can help......
I am working on a database that stores information on Funds. Each Fund is
unique, but can have many different series. The price of each series needs to
be updated and a record stored for it every month.
I need to create a form whereby the user can select a Fund Name in a combo
box - the fund name should only appear once in the combo box.
This selection will mean that the user can now select the series number from
another combo box. These numbers in this box must only relate to the Fund in
the previous combo box.
Having selected the series number from the combobox the subform where the
user will the new months price show information only for this series of this
fund....This subform is the only area of the form that should be updating a
table, the main form is basically record navigation
I just can't get it to work for me, I'm sure I am not doing something really
stupid....Please help

"George" wrote in message

Sorry - I know what i did wrong - my text box was bound. However i
still
can't get the form to select anything in the subform despite the fact
that
the linked fields are matching and i think correct...

"George" wrote:

I hope that someone can help......
I am working on a database that stores information on Funds. Each
Fund is unique, but can have many different series. The price of
each series needs to be updated and a record stored for it every
month.
I need to create a form whereby the user can select a Fund Name in a
combo box - the fund name should only appear once in the combo box.
This selection will mean that the user can now select the series
number from another combo box. These numbers in this box must only
relate to the Fund in the previous combo box.
Having selected the series number from the combobox the subform
where the user will the new months price show information only for
this series of this fund....This subform is the only area of the
form that should be updating a table, the main form is basically
record navigation
I just can't get it to work for me, I'm sure I am not doing
something really stupid....Please help

It's hard to say what the problem is, partly because I'm not sure what
you mean by "can't get the form to select anything in the subform".
Please explain in a bit more detail, and maybe post the Link
Master/Child Fields properties of the subform control. In the mean
time, I'll offer these bits of general advice:

1. From all you've said, the main form should be unbound; that is, its
RecordSource property should be blank.

2. Make sure the main form's AllowEdits property is set to Yes.
Otherwise, you won't be able to actually select anything in the combo
boxes.

3. The subform control (on the main form) should be Enabled and not
Locked.

4. The form object that is displayed in the subform control -- its
Source Object -- should be bound to the appropriate table, and should
have its AllowEdits and AllowAdditions properties set to Yes. Almost
certainly you also want its AllowDeletions property set to Yes, too.

5. If you can't edit any data on the subform, and you haven't set any
property that would prevent it, it may be that you have bound it to a
nonupdatable recordsource. That can happen if you use certain types of
queries, involving more than one table, as the recordsource. You can
check updatability by opening the recordsource directly as a datasheet
and seeing if you can add or update records there. From what you've
told me so far, you probably don't need more than one table to be
involved in the subform's recordsource.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)