Update tables and all records update accordingly

[Guest]

Hello,

I have a form that consists of two combo boxes that are bound to tables,
tbldept and tbljobtitle. each table had two columns DeptID & Dept, JobTitleID
& JobTitle. Both ID's are autonumber and the other fields are text. In the
main table "Training", both these fields are text. What I am trying to
accomplish is this:

1. When data is choosing from cbo on form, I want the number to fill on the
main table but the text to show in the query for reports.

2. If a Job Title and or Department name should change, by changing it in
the respective table, I want each record that had the old name to update with
the new name instead of going back to each record to update manually.

I know this is possible and I think I have the start of the set up correct
but need some help. I would appreciate as much help as possible and please
spell it out with a visual as I have never tried this before.

Thanks Much!!

 

[John Vinson]

Hello,

I have a form that consists of two combo boxes that are bound to tables,
tbldept and tbljobtitle. each table had two columns DeptID & Dept, JobTitleID
& JobTitle. Both ID's are autonumber and the other fields are text. In the
main table "Training", both these fields are text. What I am trying to
accomplish is this:

1. When data is choosing from cbo on form, I want the number to fill on the
main table but the text to show in the query for reports.

2. If a Job Title and or Department name should change, by changing it in
the respective table, I want each record that had the old name to update with
the new name instead of going back to each record to update manually.

I know this is possible and I think I have the start of the set up correct
but need some help. I would appreciate as much help as possible and please
spell it out with a visual as I have never tried this before.

This is exactly why one would ordinarily NOT store the names as text.
If you store the *numeric* DeptID and JobtitleID in the table
Training, you can use a Combo Box on a form to display the current job
title and department name; you would base a Report on a query joining
the Training table to tblDept and tblJobTitle, and pick up the current
value of the titles from those tables.

If you're assuming that a Report must be based on a Table, correct
that assumption; almost all reports are in fact based on Queries. If
you look at the table datasheet, you'll see numbers - but that's how
it's supposed to be, table datasheets are NOT designed for human
consumption.

Storing the text in your Training table is undesirable precisely for
the reason that you describe: it makes for hassles when those tables
are edited. You're defeating the purpose of storing your data
relationally!

John W. Vinson[MVP]

 

[Guest]

I don't want to store the Text, I want to sotre the numbers but the text is
showing in the Training table. So are you saying I should delete the Dept and
JobTitle fields from the Training table and replace them with the DeptID and
JobTitleID which are both Autonumber fields? Is there something in the combo
box properties that may need to be set up to reflect what I want to show in
the Training table? Yes, I always run reports based on the queries. However,
I need to get my table issue fixed before the query will get me the correct
information I am looking for right? I want to learn to set up my database
this way because I have come across times the information changes and then
it's a mess to try and fix.

Thanks!!

 

[John Vinson]

I don't want to store the Text, I want to sotre the numbers but the text is
showing in the Training table. So are you saying I should delete the Dept and
JobTitle fields from the Training table and replace them with the DeptID and
JobTitleID which are both Autonumber fields? Is there something in the combo
box properties that may need to be set up to reflect what I want to show in
the Training table? Yes, I always run reports based on the queries. However,
I need to get my table issue fixed before the query will get me the correct
information I am looking for right? I want to learn to set up my database
this way because I have come across times the information changes and then
it's a mess to try and fix.

I'm puzzled. What's the Datatype of the DeptID and JobTitleID fields
in the Training table? Text, or Number - Long Integer? The latter is
correct. Are they showing up as text because you have used the Lookup
Wizard, or did you perhaps have the wrong Bound Column in the combo
box used to populate the fields? The Control Source should be the
(numeric) ID field in the Training table, and the bound column should
be the autonumber field from the Department or JobTitle table.

John W. Vinson[MVP]

 

[Guest]

Both data types for the ID fields in the training table are number long
integer. In the dept combo box, I changed the control field to DeptID and in
the bound field on the properties it says 1 which is the first of the two
columns in the Department table and the autonumber field. Should field be a
primary key field? Anyhow, it's not working. I can click on a name but it
comes up with a "The value you entered isn't valid for this field". I know
this can't be this difficult. Obviously I'm not getting something here.

 

[John Vinson]

Both data types for the ID fields in the training table are number long
integer. In the dept combo box, I changed the control field to DeptID and in
the bound field on the properties it says 1 which is the first of the two
columns in the Department table and the autonumber field. Should field be a
primary key field? Anyhow, it's not working. I can click on a name but it
comes up with a "The value you entered isn't valid for this field". I know
this can't be this difficult. Obviously I'm not getting something here.

Please open the form you're using to update this field in design view.
Post the following information for one of the combos (I'm guessing
they have the same problem):

Row Source (post the SQL if it's a query, the table fields if it's a
table)
Control Source
Bound Column
Column Widths


John W. Vinson[MVP]

 

[Guest]

Hi John,

Here is the Row Source from the Dept Combo:
SELECT DISTINCT [Dept] FROM tbldept WHERE Dept<>"" Or Dept Is Not Null ORDER
BY Dept;

Here is the Row Source from the JobTitle Combo:
SELECT DISTINCT [JobTitle] FROM tbljobtitle WHERE JobTitle<>"" Or JobTitle
Is Not Null ORDER BY JobTitle;

The following is the same on both properties for these combos:
Column Count = 1
Column Heads = No
Column Width = 1"
Bound Column = 1

Please let me know if this is what you were looking for. I appreciate you
sticking with me on this. Once I get an understanding of how this works, I'll
be good from here on in regards to setting up my database in this format.

Thanks John!

 

[John Vinson]

Hi John,

Here is the Row Source from the Dept Combo:
SELECT DISTINCT [Dept] FROM tbldept WHERE Dept<>"" Or Dept Is Not Null ORDER
BY Dept;

Here is the Row Source from the JobTitle Combo:
SELECT DISTINCT [JobTitle] FROM tbljobtitle WHERE JobTitle<>"" Or JobTitle
Is Not Null ORDER BY JobTitle;

The following is the same on both properties for these combos:
Column Count = 1
Column Heads = No
Column Width = 1"
Bound Column = 1

Now I'm REALLY confused.

This is doing exactly and precisely what you're asking it to do:
selecting the Text values Dept or JobTitle from the respective tables.

It's then trying to store that *text* value in a Number field, and, of
course, it's failing; "Human Resources" is not a legal integer value.

I *think* what you need to use is:

SELECT DeptID, Dept FROM tblDept WHERE Dept & "" <> "" ORDER BY Dept;

with ColumnCount 2, Bound Column 1, ColumnWidth 0;1", Control Source
DeptID

and similarly for the other combo box.

John W. Vinson[MVP]

 

[Guest]

Hi John,

It didn't work. That's ok, obviously I am doing something wrong. This
database was just a quick fix for temporary use anyway. So I am taking all
the ID out except for theone in the main table. I hate to set it up like this
but need to put it in production. The next database I create this week will
definitely need to set up properly. Thank you for all your help and time you
spend with me. I am still going to find out what went wrong.

Thanks John!

Hi John,

Here is the Row Source from the Dept Combo:
SELECT DISTINCT [Dept] FROM tbldept WHERE Dept<>"" Or Dept Is Not Null ORDER
BY Dept;

Here is the Row Source from the JobTitle Combo:
SELECT DISTINCT [JobTitle] FROM tbljobtitle WHERE JobTitle<>"" Or JobTitle
Is Not Null ORDER BY JobTitle;

The following is the same on both properties for these combos:
Column Count = 1
Column Heads = No
Column Width = 1"
Bound Column = 1

Now I'm REALLY confused.

This is doing exactly and precisely what you're asking it to do:
selecting the Text values Dept or JobTitle from the respective tables.

It's then trying to store that *text* value in a Number field, and, of
course, it's failing; "Human Resources" is not a legal integer value.

I *think* what you need to use is:

SELECT DeptID, Dept FROM tblDept WHERE Dept & "" <> "" ORDER BY Dept;

with ColumnCount 2, Bound Column 1, ColumnWidth 0;1", Control Source
DeptID

and similarly for the other combo box.

John W. Vinson[MVP]