Subform

[Helen]

I'm new to Acccess and have run out of troubleshooting ideas for this
problem: I have created a simple subform, works fine on it's own but when I
drag it into a main form it 1) doesn't advance in synch with the main form
and 2) when it does advance, the records go blank. The form and subform are
linked by site ID (a field common to the respective underlaying tables which
are linked one-to-one). They are each from 2 different tables that contain
data for the same job site. I'm using subforms because when I tried to build
one form using fields from more than 2 tables the form suddenly became
read-only with message "This recordset is not updateable" when I tried to
update data.
Thank you for any guidance you can give me on this.

 

[John W. Vinson]

I'm new to Acccess and have run out of troubleshooting ideas for this
problem: I have created a simple subform, works fine on it's own but when I
drag it into a main form it 1) doesn't advance in synch with the main form
and 2) when it does advance, the records go blank. The form and subform are
linked by site ID (a field common to the respective underlaying tables which
are linked one-to-one). They are each from 2 different tables that contain
data for the same job site. I'm using subforms because when I tried to build
one form using fields from more than 2 tables the form suddenly became
read-only with message "This recordset is not updateable" when I tried to
update data.
Thank you for any guidance you can give me on this.

What are the Recordsources of the two forms?
What are the Master and Child Link Fields?
Do you have a relationship defined with referential integrity enforced?
And why do you have a one to one relationship, instead of putting all the data
in one table? One to one relationships are VERY rare; if you're not doing
"Subclassing" or "Field level security" or a few other specialized techniques,
it's likely that your table structure is wrong.

 

[Helen]

John,
Thank you so much for the fast response.
The recordsources are SiteDetails table 1 (main form) and Assessment Scores
table2 (used for sub form).
Master link is field SiteID in table 1, child is SiteID in table 2.
I had not turned on the ref integrity because it made it 1to many and I knew
it would only be 1to1 for these tables. I can go and turn those on. Have I
confused things by having the SiteID field in table 2 be a lookup combo box
from table 1?

I have a 1to1 because the table seemed too cumbersome with 3 or 4 very
different chunks of information per job site. Plus I'm trying to teach
myself Access using a manual and someone else's (unrelated) sample dbase -
they used about a dozen tables in the same way. Though I see now that even
though it's a 1to1 reship they have it linked as 1 to many, even though it is
specific info per job site and the SiteID is unique, would never show up more
than one record per table. I thought I was being more technically correct -
I guess a little knowledge can be a dangerous thing.

Perhaps the FIRST question should be this instead: Do you think that if I
put everything into one table I won't have the problem of the form turning
into read-only? That's why I started using sub-forms (though I'll probably
need some anyway for other tables bec client wants to enter everything per
job site in one big form). It took me a while but I found something on some
help site that said the more tables used in a form the more likely to get
that "not updateable" error message and that sub-forms are the way to go.
The worst thing is that even when I delete the fields from the second table
my form is still read-only, can't go back I guess. Hence, I went to
sub-forms.

 

[John W. Vinson]

John,
Thank you so much for the fast response.
The recordsources are SiteDetails table 1 (main form) and Assessment Scores
table2 (used for sub form).
Master link is field SiteID in table 1, child is SiteID in table 2.
I had not turned on the ref integrity because it made it 1to many and I knew
it would only be 1to1 for these tables. I can go and turn those on. Have I
confused things by having the SiteID field in table 2 be a lookup combo box
from table 1?

Yes, you absolutly have. The "Lookup Field" is considered a misfeature by
many; see http://www.mvps.org/access/lookupfields.htm for a critique. It
*CONCEALS* the actual contents of your table and can cause great confusion.

I have a 1to1 because the table seemed too cumbersome with 3 or 4 very
different chunks of information per job site. Plus I'm trying to teach
myself Access using a manual and someone else's (unrelated) sample dbase -
they used about a dozen tables in the same way. Though I see now that even
though it's a 1to1 reship they have it linked as 1 to many, even though it is
specific info per job site and the SiteID is unique, would never show up more
than one record per table. I thought I was being more technically correct -
I guess a little knowledge can be a dangerous thing.

Putting a unique Index on the SiteID field (non-lookup!!!) in the second table
will automatically make it a one to one relationship; enforcing RI will
(should anyway) make the form updateable.

Perhaps the FIRST question should be this instead: Do you think that if I
put everything into one table I won't have the problem of the form turning
into read-only?
Yes.

That's why I started using sub-forms (though I'll probably
need some anyway for other tables bec client wants to enter everything per
job site in one big form). It took me a while but I found something on some
help site that said the more tables used in a form the more likely to get
that "not updateable" error message and that sub-forms are the way to go.

Well... not quite. The more tables in a Query, the less likely it is to be
updateable. Subforms are indeed the way to go for multiple tables on a Form.

The worst thing is that even when I delete the fields from the second table
my form is still read-only, can't go back I guess. Hence, I went to
sub-forms.

Good move I'd say. You may just need to correct the form's Recordsource
property. You *can* go back; deleting fields within the table won't help, but
changing the recordsource to something reasonable should.

 

[Helen]

Thank you for the advice John. I think I'll keep it simple and use just one
table for everything, probably better for reports as well. I really
appreciated your help.