Creating Form from multiple tables (1 Viewer)

[MattShaw]

Hi All,

I have tried to create a form linked to multiple tables. Some of the form works but other parts do not allow data entry. Also when I try to add certain fields from other tables, this makes the form go blank?

I am a total novice with Access so any help would be appreciated. I think this may be to do with the relationships in my tables?

Thanks in advance

 

[isladogs]

See this link

Why is my query read only?

 

[missinglinq]

Allen Browne's article, that ridders provided a link to, explains why basing Forms on multi-table Queries frequently fails. One of the cardinal rules of relational databases, such as Access, is 'one Form/one Table.'

To allow data entry into multiple Tables, from a single Form, usually requires using a Main Form and one or more Subforms based on the related Tables.

Linq ;0)>

 

[MattShaw]

Thanks for the help, might take me some time to understand what that stuff means!

 

[Uncle Gizmo]

My blog on "Many to Many" also demonstrates subforms.

See Here:-

 

[isladogs]

Thanks for the help, might take me some time to understand what that stuff means!

In short, (though its one of several causes), the more tables you use as your record source for a form or query, the more chance there is of it being read only.

Now I don't hold to missinglinq's view that forms should only be based on 1 table.
I do however, always CHECK that the form/query is still editable after adding another table or query to the recordsource.
Having said that, if you do follow linq's advice, you KNOW the form/query will be editable

 

[missinglinq]

...Also when I try to add certain fields from other tables, this makes the form go blank...

FYI...when a Form based on a Query is Read-Only, for the reasons Allen Browne discusses in his article, the Form will 'go blank,' meaning that not even the Controls, themselves will show, if there are no Records in the underlying Record Source.

Linq ;0)>

 

[MattShaw]

Hi,

That blog post was pretty useful, thanks!

This is my current setup. I am putting together a database for my student's placement activity. Students can go on multiple placements for multiple different modules. So I have one table of general student information and a second table with the placement information.

However because the university uses the same organisations multiple times for students I have two other tables. One is the employers organisation details and the second table is the named mentor within that organisation.

I am trying to create a form that takes fields from all four tables.

 

[plog]

I am trying to create a form that takes fields from all four tables.

Yes, and you shouldn't do that. For forms that interact with data (add/edit/delete): One table, one form.

 

[missinglinq]

To each his (or her) own...but most experienced Access developers, as plog and I suggested, abide by the rule of one table/one form (or one form/one table) about 99% of the time...it's really what relational databases are all anout.

Linq ;0)>

 

[Mark_]

A slight clarification regarding previous posts;

You can have a "Subform" on another form. This is often used for updating child records. As such on the update form for Students you can have a subform for their placements.