 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2005Update tables and all records update accordingly
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!! >Hello, > [quoted text clipped - 14 lines] >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] 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!! > >Hello, > > [quoted text clipped - 34 lines] > > John W. Vinson[MVP] >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 [quoted text clipped - 6 lines] >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] 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. > >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 [quoted text clipped - 16 lines] > > John W. Vinson[MVP] >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 [quoted text clipped - 3 lines] >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] 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! > >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 [quoted text clipped - 15 lines] > > John W. Vinson[MVP] >Hi John, > [quoted text clipped - 11 lines] >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] 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, > > [quoted text clipped - 30 lines] > > John 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.