Subforms and 'data entry'

[Guest]

I am a very inexperienced Access user, therefore I have been building tables and forms without using code, would have no idea how. Problem is that I have built one form based on a 'subject info' table and have put a subform based on the 'subject address' table within it (it is linked) to show multiple subject addresses. I would ideally like to be able to perform all data entry within this one page form to include those address fields within the subform. Unfortunately you get a nasty 'read-only' message on the subform if you try to type in the fields. I have tried changing the subform properties to allow data entry, but then the fields in the form dissappear all together. I have played with the allow additions, edits, etc. and nothing seems to work. So am I to understand that a subform is only to 'view' info from another table without being able to add or edit to it? Or is there a way to allow data entry? Is there a better way than using a subform? Thanks for any assistance!

 

[John Vinson]

I would ideally like to be able to perform all data entry within this one page form to include those address fields within the subform. Unfortunately you get a nasty 'read-only' message on the subform if you try to type in the fields.

A subform is certainly the way to add this data. I suspect that the
Query upon which the subform is based is not updateable. Check first
the properties of the Form you're using as a subform: are its Allow
Updates and Allow Inserts properties set to True?

If so, check the form's Recordsource property. It should probably be
your Address table (only). If it's a Query, could you post the SQL
view of that query here? Not all queries are updateable; someone
should be able to see why this one isn't.

 

[Guest]

Thanks for responding! Well, I checked the settings that you referred to in your response and they are set to 'yes'. It seems as though what I am trying to do is so simple, but still not working. I don't have any SQL since I don't know how to formulate that. If I may, let me just break down the fields that are to be used in the query

Subject Data Table: 'NAME' and then many more fields that are likely not relevant to the link in the quer
Address Table: 'NAME', 'ADD #', 'STREET ADD', 'CITY', 'TYPE', 'SMUD

How do primary keys get involved? I have the 'NAME' field set as the key in the 'Subject Date' table, but was thinking I need to make an auto id number primary key for the 'address' table since I have multiple addresses for the same name making it so I can't use that field for the key. Is this right

I have made the main Form based on the 'subject data' table and the subform based on the query between the two tables. I tried playing with 'cascading' updates but it won't set the relationship since I have multiple address with the same name in the 'addresss table'. I have tried changing all the little settings but nothing seems to allow me to do 'data entry' into the subform!! Its got to be sooooo simple...

 

[John Vinson]

Thanks for responding! Well, I checked the settings that you referred to in your response and they are set to 'yes'. It seems as though what I am trying to do is so simple, but still not working. I don't have any SQL since I don't know how to formulate that. If I may, let me just break down the fields that are to be used in the query:

You do have SQL - you just haven't seen it because you haven't needed
to! All Forms have a "Recordsource" property; even if you base the
form on a table, Access creates a hidden Query - and the language of
Queries is SQL. You can open the form in design view, click the ...
icon, accept Access' offer to create (actually expose) a query, and
use View... SQL to see (and to copy and paste) the SQL.

Subject Data Table: 'NAME' and then many more fields that are likely not relevant to the link in the query
Address Table: 'NAME', 'ADD #', 'STREET ADD', 'CITY', 'TYPE', 'SMUD'

Name is a bad idea for several reasons. For one, NAME is a reserved
word in Access; for another, it's a LOUSY key, since people's names
are not unique. I know three men named Fred Brown right here in little
Parma.

Your Subject table should - I'd say MUST - have a Primary Key,
SubjectID let's call it, otherwise there is no way to uniquely
identify a person. I'd split the namefield into FirstName and LastName
so that they can be sorted and displayed independently, and add an
Autonumber field.

How do primary keys get involved? I have the 'NAME' field set as the key in the 'Subject Date' table, but was thinking I need to make an auto id number primary key for the 'address' table since I have multiple addresses for the same name making it so I can't use that field for the key. Is this right?

You should have a Primary key field in the Address table... and ALSO a
Foreign Key field, SubjectID. If the Subject table SubjectID is an
Autonumber make this a Long Integer field.

I have made the main Form based on the 'subject data' table and the subform based on the query between the two tables.

That's the problem. The subform should be based JUST on the Address
Table; with your current design use NAME as the master/child link
field to show only the address(es) for that name. With my suggested
revision, use SubjectID as the master/child.

I tried playing with 'cascading' updates but it won't set the relationship since I have multiple address with the same name in the 'addresss table'.

Well... you WANT to have a one to many relationship, from the Primary
Key of the "one" side table (Subjects) to the Foreign Key (not the
primary key!) in the Many side table (Addresses). That's how
relationships WORK.

 

[Guest]

Thats working great so far!! Thanks for your help! But...I followed your recommendations re the last and first name fields. So now how do I tell it to transfer the name data from those fields in the main form that has 'subject id' as the primary key to that same address table? It is showing the multiple addresses as entered, but not the names that were previously entered into the main form that need to be associated with each of those addresses. I need to be able to sort these addresses by the street address later and since there will be multiples for each subject last/first field, I have to be allow for duplicates. Does this require a query or is there some basic link that can be made between the two tables with the given format?
Thanks again!

 

[John Vinson]

Does this require a query

Yes. A simple query joining the two tables. Pull the name from the
names table and the address from the address table.

A basic principle of relational databases is the "Grandmother's Pantry
Principle": "a place - ONE place! - for everything, everything in its
place". If you store the names in the address table (or multiple other
tables), then you have a big hassle if a person changes their name,
tracking down all the instances.

Just store the name once and use Queries. That's how relational
databases work!

 

[Guest]

Ok, everything seems to working at this point. Thanks for all your assistance John, very much appreciated!!