 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2008Two Forms not updating same field
Hello everyone. I am currently making a database for my school and I have run into a problem with two forms, henceforth known as Form 1 and 2. Both forms take First and Last name from one table. The problem is that Form 2 does not pick up a new record whenever I add a name to Form 1. But Form 1 will pick up a new record when I make a new name in Form 2. As I said earlier both forms are taking thier name fields from the same table. Thanks a lot for your time. Sorry, forgot to mention that I am using Access 2003. >Hello everyone. I am currently making a database for my school and I have >run into a problem with two forms, henceforth known as Form 1 and 2. Both [quoted text clipped - 3 lines] >earlier both forms are taking thier name fields from the same table. Thanks >a lot for your time. Is there some particular reason to have two forms open simultaneously? That's legitimate but rather unusual - not least because of this kind of problem. You can Requery or Refresh Form2 in the AfterUpdate of Form1 (either with a Macro or with VBA code); see the online help for these two methods.  SignatureJohn W. Vinson [MVP] Thanks a lot John. To answer your question the forms are not normally open at the same time. The problem is that even after putting a new record into Form1, Form2 does not gain the same record. But when creating a new record in Form2, Form1 gains the same record. Also, I tried the requery and it did not work. I suppose you could call what I am trying to do more of a synchronization of records. Thank you for your time. > Is there some particular reason to have two forms open simultaneously? That's > legitimate but rather unusual - not least because of this kind of problem. You > can Requery or Refresh Form2 in the AfterUpdate of Form1 (either with a Macro > or with VBA code); see the online help for these two methods. >Thanks a lot John. To answer your question the forms are not normally open >at the same time. The problem is that even after putting a new record into >Form1, Form2 does not gain the same record. But when creating a new record >in Form2, Form1 gains the same record. Also, I tried the requery and it did >not work. I suppose you could call what I am trying to do more of a >synchronization of records. Puzzling. Forms do NOT "store" or "contain" records. Forms are just windows, tools to let you look at the data in tables! If you look in the Table after updating using Form1 are the fields updated? How about Form2? What are the Recordsource properties of the two forms? What are the Control Sources of the fields displaying the record? Do the forms contain any VBA code which might be having an effect? SignatureJohn W. Vinson [MVP] Thanks, Form1 pulls all its feilds form one table (table1) Form2 pulls its feilds form2 tables (tables1&2). These tables are connected via the id feild(autonumber) with a one-to-one relationship. When in form2 a new record is created the information is split correctly between the 2 tables, however, when a new record is created in form1 the informations is not split correctly between the 2 tables. > >Thanks a lot John. To answer your question the forms are not normally open > >at the same time. The problem is that even after putting a new record into [quoted text clipped - 11 lines] > What are the Control Sources of the fields displaying the record? > Do the forms contain any VBA code which might be having an effect? >Thanks, Form1 pulls all its feilds form one table (table1) Form2 pulls its >feilds form2 tables (tables1&2). These tables are connected via the id >feild(autonumber) with a one-to-one relationship. When in form2 a new record >is created the information is split correctly between the 2 tables, however, >when a new record is created in form1 the informations is not split correctly >between the 2 tables. WHOA. Several possibly severe problems here. First off, if Table2 is not part of Form1's recordsource, then *nothing* done on Form1 can affect Table2 in any way. So it makes sense that Form1 is not updating Table2. Secondly, one to one relationships are VERY rare and often used incorrectly. If the terms "Subclassing" or "Table-driven field level security" aren't in your design concepts, then your table structures are almost surely wrong. Thirdly, "splitting" a record across two tables would be very uncommon. Fourth, if both Table1 and Table2 have Autonumber primary keys, they CANNOT be used for the relationship. What data do these tables contain? Why do they have a one-to-one? Are you storing "the same" data in both tables, and perhaps erroneously expecting that adding a record to Table1 will automagically create a similar record in Table2? SignatureJohn W. Vinson [MVP] Thanks for your help. In table1 we have information that's related to all records like last name, first name, home address, etc... Table2 has records of secondary information like campus address, or health insurance info (which everyone does not have). So, we set up form2 to pull the names and home address from table1 (which everyone has) and the other info from table2 which everyone does not. So we need to fix our relationships what do you suggest? > >Thanks, Form1 pulls all its feilds form one table (table1) Form2 pulls its > >feilds form2 tables (tables1&2). These tables are connected via the id [quoted text clipped - 24 lines] > adding a record to Table1 will automagically create a similar record in > Table2? >Thanks for your help. In table1 we have information that's related to all >records like last name, first name, home address, etc... Table2 has records [quoted text clipped - 4 lines] > >So we need to fix our relationships what do you suggest? You do NOT need to, nor should you, copy the information "which everybody has" from Table1 into Table2. Store it *once*, and once only; the only field from Table1 that you need in Table2 is the unique identifier, the primary key. This is apparently a case of "subclassing", a valid use of one to one relationships; you store the "main class" information, that common to all records, in the primary table, and *ONLY* the "subclass" information - insurance, let's say - in the second table. You can display and enter this conveniently by basing a Form on Table1 with a Subform based on Table2, using the unique PersonID as the master/child link field. On the other hand, you could make a very good case that Address information is in a many to many relationship to Person information. You might have two siblings with the same home address; you might well have students with two home addresses (regular home and vacation home, or divided custody between separated parents). The students will surely have multiple atschool addresses over time. An alternative design would have a table of Addresses, and a third table with fields StudentID, AddressID, and AddressType (and perhaps StartDate and EndDate) linking them; if a student has four addresses there would be four records in this table; if 42 students live in the Eta Bita Pie house, there would be 42 records linking to the one record for that fat... erm... frat house. SignatureJohn W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.