Form/ subform problems

[troy_lee@comcast.net]

I have a table (Table A). It has one field, a PK. It is in a 1:M with
another table (Table B). I am having trouble with a form/subform setup
to view the 1:M records.

On the parent form, there is a combo box with a Select query to select
one of the records in Table A. (The parent form is bound to Table A.)
I want to be able to first, view all of the corresponding records from
Table B and then second, give an option for editing these records via
a command button.

My first problem is that when I select a record in the combo box on
the parent form, the corresponding records in the subform don't
appear. I have the parent and child fields linked properly, I think. I
have tried to change my key structure in Table B as well, trying both
a single and a composite key. I thought that when I selected a record
on the main form, the subform automatically populates with the data.
BTW, the subform is in single form display mode, not continuous. Does
this matter when attempting to view many records from Table B? Doesn't
Access create a recordset of all the records in Table B so that I can
view them one at a time with the navigation buttons in the subform?

My second problem is that when I move out of the combo box to a field
on the subform, Access tells me that it can't create a record because
it would create duplicates values in the PK field (Table A). I know
this. I don't want to create a record, I just want to view the
records. I have another form for the creation of a new record and it
works fine. What is the preferred way for opening a form/subform in
view mode only?

Thanks in advance.

Troy Lee

[Rick Brandt]

Re: Form/ subform problems

troy_lee@comcas t.net wrote:

I have a table (Table A). It has one field, a PK. It is in a 1:M with
another table (Table B). I am having trouble with a form/subform setup
to view the 1:M records.
>
On the parent form, there is a combo box with a Select query to select
one of the records in Table A. (The parent form is bound to Table A.)
I want to be able to first, view all of the corresponding records from
Table B and then second, give an option for editing these records via
a command button.

Unless you actually intend to edit the single field in tableA you do not
need the main form to be bound. All you really have is a form for selecting
the gropups of records from tableB.

My guess is that you have bound not only the form, but also the ComboBox and
that would be incorrect. When you make a choice in that you are actually
editing the current main record (not what you want I assume).

My first problem is that when I select a record in the combo box on
the parent form, the corresponding records in the subform don't
appear. I have the parent and child fields linked properly, I think. I
have tried to change my key structure in Table B as well, trying both
a single and a composite key. I thought that when I selected a record
on the main form, the subform automatically populates with the data.
BTW, the subform is in single form display mode, not continuous. Does
this matter when attempting to view many records from Table B? Doesn't
Access create a recordset of all the records in Table B so that I can
view them one at a time with the navigation buttons in the subform?
>
My second problem is that when I move out of the combo box to a field
on the subform, Access tells me that it can't create a record because
it would create duplicates values in the PK field (Table A). I know
this. I don't want to create a record, I just want to view the
records. I have another form for the creation of a new record and it
works fine. What is the preferred way for opening a form/subform in
view mode only?

This confirms that you are editing the current main record and trying to
make its value equal to that of another record in the same table and thus
you ARE trying to make two records have identical keys.

Just remove the RecordSource of the main form and everything will work.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com