enterabel form (data going into multiple tables)
in [General]
|
From: Graeme on 9 Mar 2010 11:49 Hello I am currently designing a database for my company, and I keep on running into problems when I want to create an enterable form using multiple tables. This means that when i create the form, I will be using fields from different tables, and when it is entered into the form is should go to the tables. However, when I create this, there seems to be problems, It works if I input information into every field. However, if this is not done than the record is not remembered. I would like to know how to fix this problem. Regards Graeme --- frmsrcurl: http://msgroups.net/microsoft.public.access
From: George Hepworth on 9 Mar 2010 13:06 As a general rule, queries with joins to multiple tables are more likely NOT to be updatable. Therefore, when you use such a query as the recordsource for your forms, it is quite likely the form will not be updatable. The solution is to go to a better design. We can not see your tables (we don't even know what the subject of the database might be), so this will have to be pretty generic. Usually, you will have tables which are in a one-to-many relationship--assuming you have a properly designed database. In those cases, the common approach is to base data entry on a main form/subform design, in which the main form is bound to a single table, that table being the one on the "One" side of the one-to-many relationship. The subform, which is inserted into a subform control on the main form, is bound to the table on the "Many" side of the one-to-many relationship. You tell Access about the relationship between the two forms by using the Master/Child fields property. the Primary key for the table bound to the main form is the "Master" linking field, and its corresponding Foreign key in the table bound to the subform is the "Child" linking field. With such a design, Access is able to manage the relationship between records entered. By limiting your bound forms to a single table (or a query which is based on a single table) you are able to design updatable forms. It is possible to create multi-table queries which are updatable, but that requires pretty good understanding of normalization. George "Graeme" <user(a)msgroups.net/> wrote in message news:Odcq#h6vKHA.5008(a)TK2MSFTNGP05.phx.gbl... > Hello > I am currently designing a database for my company, and I keep on running > into problems when I want to create an enterable form using multiple > tables. This means that when i create the form, I will be using fields > from different tables, and when it is entered into the form is should go > to the tables. However, when I create this, there seems to be problems, It > works if I input information into every field. However, if this is not > done than the record is not remembered. I would like to know how to fix > this problem. > > Regards > Graeme > > --- > frmsrcurl: http://msgroups.net/microsoft.public.access
From: John W. Vinson on 9 Mar 2010 13:11 On Tue, 09 Mar 2010 08:49:31 -0800, Graeme <user(a)msgroups.net/> wrote: >Hello >I am currently designing a database for my company, and I keep on running into problems when I want to create an enterable form using multiple tables. This means that when i create the form, I will be using fields from different tables, and when it is entered into the form is should go to the tables. However, when I create this, there seems to be problems, It works if I input information into every field. However, if this is not done than the record is not remembered. I would like to know how to fix this problem. It sounds like you're starting your database design with the Form. That's not going to work very well! You first need to start with the tables - properly normalized and related tables. THEN you design a form to fit the tables. Rather than building one Great Master Query with all the tables, you would use the tools that Access provides - Forms with Subforms, listboxes and combo boxes, etc; it's rather rare that you'll need more than one table in a Form's Recordsource query. What are your tables? How are they related? Are you in fact trying to throw all of the tables onto the form at once, or are you using Subforms? You might want to check out some of the resources and form examples below, or of course you're welcome to post back with more details. Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/accessjunkie/resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP]
From: Graeme on 9 Mar 2010 15:28 Thankyou both John W. Vinson [MVP] and George I will try what was said and if I have anymore problems I will not hesistate to use this form. Regards Graeme --- frmsrcurl: http://msgroups.net/microsoft.public.access/enterabel-form-data-going-into-multiple-tables
From: Graeme on 10 Mar 2010 10:10
Hello To answer the above question my tables are Problem and Results. I would like to have one form, that would combine the fields from these tables. They are linked by an autonumber field called ID in the Problem table and a number field called ID in the Result field. What I would like to happen is every time autnoumber ID field increases the corresponding ID field for results also increases. I am infact trying to throw all the fields in one form. Could you tell me if this is a good Idea or if I should, hault what i am doing and find a different approach. I do not understand the purpose of subforms, however both fields have a primary Key field called ID (one to one relationship), so the one-to-many relationship you had discussed may not be valid. Regards Graeme --- frmsrcurl: http://msgroups.net/microsoft.public.access/enterabel-form-data-going-into-multiple-tables |