Two Forms not updating same field

[coolmar100]

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.

 

[coolmar100]

Sorry, forgot to mention that I am using Access 2003.

 

[John W. Vinson]

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.

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.

 

[coolmar100]

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.

 

[John W. Vinson]

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?

 

[coolmar100]

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.

 

[John W. Vinson]

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?

 

[coolmar100]

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?

 

[John W. Vinson]

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?

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.