Problem with Relationships, Forms and Checkboxes

[headly]

Split data into tables; Created primary and foreign keys, where primary is
autonumber. Want all the table data in a single form. The frmClinical does
not autonumber the first field (or it appears in the table but not the form),
but won't allow any check boxes (all on subforms) to relate to the data; The
subforms are based on tables with an autonumber pkey field too. I thought
that the subforms would autonumber when a box was checked, but they don't; Is
there a problem with the forms data source? TIA
Dowload file from http://www.headly.com/db1.mdb

 

[Steve]

Open your main form in design view and select a subform control (border
around the subform). open properties and go to the Data tab. Are the
LinkMaster and LinkChild fields filled in?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[headly]

Yes, got both link master and child; Master is the Autonumber pkey and child
is the Integer Foreign key.

 

[jahoobob via AccessMonster.com]

I am hesitant to download a .mdb file from an unknown source and I don't
think many others will either.
That being said and without looking at your tables you will have a primary
key in a table that has corresponding foreign key(s) in other table(s). In
the form, the subform based on one of these secondary tables will
automatically insert the primary key from the main table into the
corresponding foreign key in the secondary table record(s) if you link the
subform to the main form - select the subform from the main form's design
view and then select properties, select the elipses at the end of Link Child
Fields and select the two fields to link. If you have a continuous sub form
all the records entered will be assigned the foreign key same as the primary
key of the main form record that is shown.

 

[headly]

That is how it is set up; When I click on a checkbox on a subform I get the
following error message:
You can't assign a value to this object
*The object may be a control on a read only form
*The object may be on a form that is open in design view
*The value may be too large for this field
The field is a Y/N field, fwiw;
The form was built in design view using the datasource to build the query
that includes 3 of the 10 tables; when I try to include the other 7 yes/no
checkbox tables in the form's data source query, no fields show in form view.

 

[John W. Vinson]

The form was built in design view using the datasource to build the query
that includes 3 of the 10 tables; when I try to include the other 7 yes/no
checkbox tables in the form's data source query, no fields show in form view.

The Subform should be based on *ONE* child table - not on three, not to
mention ten.

What is the structure of your tables? What is the Primary Key fieldname of
each? How are the tables related? What is the structure of a representative
child table? How many yes/no fields are you talking about here?

John W. Vinson [MVP]

 

[headly]

OK, Let's keep it simple
2 tables: TblMaster and TblCheckbox
TblMaster Structure:
PKey - Autonumber
Fkey - Number/Long Int
Field - Text
TblCheckbox Structure:
PkeyCheckbox - Autonumber
Field1 - Y/N
Field2 - Y/N
Relationships: PkeyCheckbox to Fkey
Forms: 1 existing form for TblCheckbox
Create new form for TblMaster, Insert tblcheckbox subform, indicating that
master is Fkey, child is PkeyCheckbox;
When I enter data for tblMaster field1, when I check a box in subform
Pkeycheckbox I'd like for the autonumber to correspond to the autonumber that
should be generated when I click on a checkbox, but I get this error:
You can't assign a value to this object
*The object may be a control on a read only form
The object may be on a form that is open in Design view
The value may be too large for this field
I click OK to the error and the autonumber is generated correctly, but why
the error?

 

[John W. Vinson]

OK, Let's keep it simple
2 tables: TblMaster and TblCheckbox
TblMaster Structure:
PKey - Autonumber
Fkey - Number/Long Int
Field - Text
TblCheckbox Structure:
PkeyCheckbox - Autonumber
Field1 - Y/N
Field2 - Y/N
Relationships: PkeyCheckbox to Fkey
Forms: 1 existing form for TblCheckbox
Create new form for TblMaster, Insert tblcheckbox subform, indicating that
master is Fkey, child is PkeyCheckbox;
When I enter data for tblMaster field1, when I check a box in subform
Pkeycheckbox I'd like for the autonumber to correspond to the autonumber that
should be generated when I click on a checkbox, but I get this error:
You can't assign a value to this object
*The object may be a control on a read only form
The object may be on a form that is open in Design view
The value may be too large for this field
I click OK to the error and the autonumber is generated correctly, but why
the error?

An Autonumber field can be used as a primary key, but it *CANNOT* be used as a
foreign key or as a child link field. You'll need a Long Integer foreign key
field as the link.

You probably also don't want that long integer to also be tblCheckbox's
Foreign Key. This would require a one to one relationship - in which case you
might as well include the fields in tblMaster.

However... if you have enough checkbox fields to fill four subforms... your
table design is probably incorrect. What are these yes/no fields? Is this a
questionnaire, and are the checkboxes one checkbox per question? If so that's
a common error, but still an error. A better design would be a child table
with one *RECORD* per question, not one *FIELD*.

John W. Vinson [MVP]