FORM LINK TO TABLE

[Jim]

I have a data base consting to two tables with a form 1 in table two.
Form 1 has record ID# but it does not update with record ID# in Table
1. How do I get the Record ID# to show in form 1?

 

[Gina Whipp]

Jim,

I'm a little confused here...

Form 1 has a RecordSource of table 2 but...
....you want Form 1 to get the Record ID# from table 1???

Okay, well, what joins those two tables together? Perhaps using a query to
*join* the two tables? What are the fields in each of the tables?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Jim]

Jim,

I'm a little confused here...

Form 1 has a RecordSource of table 2 but...
...you want Form 1 to get the Record ID# from table 1???

Okay, well, what joins those two tables together?  Perhaps using a query to
*join* the two tables?  What are the fields in each of the tables?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm






- Show quoted text -

Both tables have the Record ID# and also Client ID#. In the
relationship the tables are linked by Client ID#. If I am updating
information in Table 2, the Record ID# and Client ID# should show for
the record that I am updating.
As it appears now, Table 2 acts as a totally separate data base. I am
using Acess 2007.

 

[Gina Whipp]

Jim,

Please post the fields in each of the tables and identify which is the
Primary Key. I suspect that your tables are set up incorrectly which is why
you are not getting the results you want. (Pardon the use of word
incorrectly but it's the only word I could think of.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Jim,

I'm a little confused here...

Form 1 has a RecordSource of table 2 but...
...you want Form 1 to get the Record ID# from table 1???

Okay, well, what joins those two tables together? Perhaps using a query to
*join* the two tables? What are the fields in each of the tables?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm






- Show quoted text -

Both tables have the Record ID# and also Client ID#. In the
relationship the tables are linked by Client ID#. If I am updating
information in Table 2, the Record ID# and Client ID# should show for
the record that I am updating.
As it appears now, Table 2 acts as a totally separate data base. I am
using Acess 2007.

 

[Jim]

Jim,

Please post the fields in each of the tables and identify which is the
Primary Key.  I suspect that your tables are set up incorrectly which is why
you are not getting the results you want.  (Pardon the use of word
incorrectly but it's the only word I could think of.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm









Both tables have the Record ID# and also Client ID#. In the
relationship the tables are linked by Client ID#. If I am updating
information in Table 2, the Record ID# and Client ID# should show for
the record that I am updating.
As it appears now, Table 2 acts as a totally separate data base.  I am
using Acess 2007.- Hide quoted text -

- Show quoted text -

The parent table consists of the following fields:
ID# (Record auto number)
Client#
Last Name(primary key)
First Name
DOB
Address
city
state
zip

The child table consists of the following fields:
ID# (record auto number)
Client#
Last Name (Primary Key)
First Name
Enrollment date
Completion date
Followup

Client# is the relationship between the two tables.

 

[Gina Whipp]

Jim,

Below I have updated your tables. Here are my comments...

1. NEVER make LastName a PK (Primary Key). What happens when two Jones'
comes up?
2. Storing LastName, FirstName in two tables defeats the purpose of
Normalization and Relational Database Design. Data should be stored in
place and then accessed via queries. I think you need to review...

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

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

3. AFTER you fix your tables, go to Tools|Relationships and establish the
Relationship with Cascade Updates checked. (Refrential Integrity)

4. Now more information needed... Can a Client have ore then one record in
the Child table? If yes, then you're going to need form/subform. If no,
then you will just need a query that includes both tables with the fields
you want to see in your form.


The parent table consists of the following fields:
ID (Make this the Primary Key Autonumber)
ClientID
LastName
FirstName
DOB
Address
city
state
zip

The child table consists of the following fields:
ID (FK - establish realtionship here)
EnrollmentDate
CompletionDate
Followup

Client# is the relationship between the two tables.


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Jim,

Please post the fields in each of the tables and identify which is the
Primary Key. I suspect that your tables are set up incorrectly which is
why
you are not getting the results you want. (Pardon the use of word
incorrectly but it's the only word I could think of.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm









Both tables have the Record ID# and also Client ID#. In the
relationship the tables are linked by Client ID#. If I am updating
information in Table 2, the Record ID# and Client ID# should show for
the record that I am updating.
As it appears now, Table 2 acts as a totally separate data base. I am
using Acess 2007.- Hide quoted text -

- Show quoted text -

The parent table consists of the following fields:
ID# (Record auto number)
Client#
Last Name(primary key)
First Name
DOB
Address
city
state
zip

The child table consists of the following fields:
ID# (record auto number)
Client#
Last Name (Primary Key)
First Name
Enrollment date
Completion date
Followup

Client# is the relationship between the two tables.