justify text and relationship question

[Katie]

First, in my report I want to justifty the text in a text box. I set the text
alignment to distribute but on the last line it spreads out every letter to
be as long as the box. (ie i t l o o k s l i k e t h i s a l
l t h e w a y a c r o s s). Is there anyway t o justify the text
box so looks just like a justified paragraph in word? Thanks

Second, i can't seem to grasp the relationships idea. Currently, I have two
tables; one table contains employee information such as name, phone, address,
education, etc. and the other table contains the details of an employee's
education (eg, degree, graduation year, school). I have a form liked to the
general information table and then a subform linked to the education table.
What I've been doing is trying to create a relationship of one-to-many from
the education field on the general table to the education table but I can't
get it to create that relationship. Any insight would be appreciated. Thanks!

Katie

 

[jmonty]

Open the properties for the text box.
On the first tab, in the Text Align property - change to Distribute.

jmonty

 

[John Vinson]

First, in my report I want to justifty the text in a text box. I set the text
alignment to distribute but on the last line it spreads out every letter to
be as long as the box. (ie i t l o o k s l i k e t h i s a l
l t h e w a y a c r o s s). Is there anyway t o justify the text
box so looks just like a justified paragraph in word? Thanks

I don't believe that Access provides that capability. I might be wrong
but I've never found it.

Second, i can't seem to grasp the relationships idea. Currently, I have two
tables; one table contains employee information such as name, phone, address,
education, etc. and the other table contains the details of an employee's
education (eg, degree, graduation year, school). I have a form liked to the
general information table and then a subform linked to the education table.
What I've been doing is trying to create a relationship of one-to-many from
the education field on the general table to the education table but I can't
get it to create that relationship. Any insight would be appreciated. Thanks!

Does the Employee table have a Primary Key, such as a unique
EmployeeID (it should)?

Does the Education table have a field of the same datatype (Long
Integer if the EmployeeID is an Autonumber), a so-called "foreign key"
field to be used as a link between the tables? (It must, to do this).

If so - open the Relationships window, add both tables, and drag the
EmployeeID field from the Employee table to the Education table. Check
the "Enforce Relational Integrity" checkbox.

If this isn't working, please explain the relevant fields (datatypes
and sizes) in your tables, and explain how you're trying to create the
relationship and in what manner it's failing.

John W. Vinson[MVP]

 

[Katie]

uh, thanks for the response but i guess you didn't really read my question. I
have it set to distribute and i don't like how the last line looks.

 

[Katie]

Thank you so much for your prompt response. I was able to create the
relationship but I'm still having difficulty getting it to do what I want. I
followed all of your directions so that I have an employeeID which is my
primary key in the general info table and then I have a foreign key in the
education table, there is no primary key for the education table. On my form,
I have text boxes for the fields from the general info table as well as the
subform with text boxes for the fields from the education table. I would like
to scroll through the two forms simultaneiously but as of this moment only
one form at a time changes (i hope that made sense). Thank you again and any
addition assistance you could provide would be great!

Katie

 

[Pieter Wijnen]

Then you have to use a bit off toughish programming, I'm afraid
Look under http://www.mvps.org/access in the forms (programming) section.
A lot of the guys in this newsgroup also have a lot of nice samples.

HTH

Pieter

 

[John Vinson]

Thank you so much for your prompt response. I was able to create the
relationship but I'm still having difficulty getting it to do what I want. I
followed all of your directions so that I have an employeeID which is my
primary key in the general info table and then I have a foreign key in the
education table, there is no primary key for the education table. On my form,
I have text boxes for the fields from the general info table as well as the
subform with text boxes for the fields from the education table. I would like
to scroll through the two forms simultaneiously but as of this moment only
one form at a time changes (i hope that made sense). Thank you again and any
addition assistance you could provide would be great!

Could you explain what you mean by "scroll through the two forms
simultaneously"? Do you in fact have a Subform Control (either
datasheet or continuous) as a control on the single-form main form? If
so I would expect you to be able to use the navigation buttons to move
from one employee record to another; the subform should follow suit.

If you have two mainforms side by side, it's tougher. And it doesn't
make much sense to me to scroll through arbitrary Education records -
whose record is it???

Your Education table *should* have a Primary Key (as every true table
should) - it could just be an Autonumber but you need SOME sort of
key. Another possibility (depending on the structure of the table)
would be a joint two-field key using the EmployeeID foreign key and
the CourseID (if there is one), or some other field which should not
repeat for a given employee.

John W. Vinson[MVP]

 

[Jean]

Thank you, thank you, thank you! I have been working on a report in access
most of the day. I am soooooo happy you shared the ActiveX Justify file.
Works great!

Jean Jones

 

[Katie]

After playing with it for awhile, I realized that the relationship isn't the
problem. One thing I didn't explain previously is that the education subform
is on a tab on the general form. If I put the education form directly on the
general form then when I scroll through the records, the information in both
fields scroll appropriately.

In more detail, this is what I'm trying to create: I have a general table
which holds information including first name, last name, phone number, etc. I
have 4 other tables all linked to the general table. There's an education
table which holds school, degree, graduation year, etc; there's a work
history table which holds previous employer, years with previous employer,
previous job title, etc; there's a certifications table which has room for up
to 10 certifications; and there's an affiliations table which has room for up
to 10 affiliations. I would like to create a form that shows the general
information and then on different tabs will show the other information. I
would like to have a tab for education, one for work history, one for
affiliations, one for certifications. Ideas??

Thanks
Katie

 

[John Vinson]

After playing with it for awhile, I realized that the relationship isn't the
problem. One thing I didn't explain previously is that the education subform
is on a tab on the general form. If I put the education form directly on the
general form then when I scroll through the records, the information in both
fields scroll appropriately.

Being on a tab control should have ZERO effect on subform linkage. A
tab control is merely a way to manage screen real estate; it does not
affect the master/child relationship between the form that it's on,
and the subform that's on it, in any way.

There is *something* else wrong with your subform.

Could you (rather than describing the content of the form) please
describe the linkage? What is the Recordsource of the mainform? of the
subform? What are the Master and Child Link Fields? What is the
symptom that you're trying to solve (your comments so far have
basically been "it's not working right").

John W. Vinson[MVP]

 

[Katie]

Wow, that was quick.
The recordsource for the main form is the general table and the record
source for the subform is the education table. I guess I don't know what the
Master and Child Link Fields are though.

When I place the subform directly on the main form, they scroll
simultaneously (ie. when I scroll to John Doe's name on the main form, the
subform displays John Doe's education) which leads me to believe that my
relationships and recordsource are set up correctly.

When I place the subform on to the tab contol, though, If I scroll to Jane
Smith's name on the main form, the subform still displays John Doe's
education. The reverse is true as well; if I scroll to Jane Smith's education
on the subform, the main form still displays John Doe's name.

Let me know if you need anything else. Thanks
Katie

 

[John Vinson]

Wow, that was quick.
The recordsource for the main form is the general table and the record
source for the subform is the education table. I guess I don't know what the
Master and Child Link Fields are though.

They are what link the data on the main form to the data on the
subform.

Open the mainform in design view. Click the *edge* of the subform (you
may need to click twice to select the Subform control rather than the
tab page); the subform should be highlighted with "handles" at the
corners and the center of each side. Select View... Properties. The
Master Link Field and the Child Link Field properties are on the Data
tab.

When I place the subform directly on the main form, they scroll
simultaneously (ie. when I scroll to John Doe's name on the main form, the
subform displays John Doe's education) which leads me to believe that my
relationships and recordsource are set up correctly.

The Subform is not (directly) using the relationships. The Subform
Wizard will use them when you create a subform - but just moving an
existing subform doesn't change anything!

When I place the subform on to the tab contol, though, If I scroll to Jane
Smith's name on the main form, the subform still displays John Doe's
education. The reverse is true as well; if I scroll to Jane Smith's education
on the subform, the main form still displays John Doe's name.

This suggests a disconnected subform. The subform should ONLY display
John Doe's data if that's the record being displayed on the mainform;
you should not be able to scroll to any other student's data.


John W. Vinson[MVP]

 

[Katie]

Ok, the Data tab on the subform properties says:
Source Object ... Education form
Link Child Field ... foreign key
Link Master Field ... Employee ID

Where Employee ID is an autonumber and the Primary key for the general table
and foreign key is a number on the education table.

I'm sorry this is proving more difficult than originally expected, thanks
for stickin' with me, though.
Katie

 

[John Vinson]

Ok, the Data tab on the subform properties says:
Source Object ... Education form
Link Child Field ... foreign key
Link Master Field ... Employee ID

Where Employee ID is an autonumber and the Primary key for the general table
and foreign key is a number on the education table.

You actually named a field [Foreign Key]...!? strange, but I guess
it's legal. Naming conventions differ, but I'd typically just give the
field the same name in both tables - EmployeeID (I don't use blanks in
fieldnames).

It sounds like there's something odd with your data. Could you open up
the table and pick a record? Note its EmployeeID. Then open the
education table and use the binocular tool to find a record with that
ID; use "Filter by Selection".

Do you see the expected education records for that employee?

John W. Vinson[MVP]