AutoFill of fields... a clear understanding is needed 2

Niki · Mar 27, 2000

I have started to develop a dbase for maintenance workorders. I have developed the following tables: Employee, MWO details, and Trade. 
 
In the EMPLOYEE table I have fields: employee name, badge (primary - YES No dups), trade 
 
In the MWO Details table I have fields: MWO Number, MWO Details, MWO assigned to...(with a combo box that has the badge & employee name)& finally Trade Title. 
 
In the TRADE table I have fields: Trade (Yes No dups), ID (primary key) 
 
My question is...... When in MWO Detail FORM.... how can I have the TRADE and EMPLOYEE NAME fill in when I enter the employee badge? What relationships... if any... do I need? 
 
Should I be using SUBFORMS? 
 
I have searched the forum for reference made to this and have found a few... but I am still unclear. If someone would help me out this would be appreciated. Also, if there are some related sites that provide clear info and even examples on this I would be interested in these as well. Thanks in advance!

Amiel · Mar 28, 2000

Interesting. I'd assume that an employee is associated with a trade (hint) ; and if allowing for more than a single trade per employee; you'll need employee/trade entries. I assume that the 'TRADE' table serves as a  'look-up' for the names of trades (to be associated w/ 'EMPLOYEES'). So to create a given list of employees w/ a specific trade we select from employees where employee.tradeID = ID_WERE_LOOKIN_FOR. This suggests that the trade is assigned to employeeID first, and then . . . For employee badge criterion; if you've allowed multiple 'Trades' for each employee, you'll have to deal w/ multiple records being returned for the badge number (contained in the WorkOrder record of course!) Just use 'distinct' clause. No big deal. And oh, yes. Do use a sub form.   Amiel <a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a> <a href= > </a> Amiel

Niki · Mar 29, 2000

Thanks Amiel for your response. I am going to try this right now, but I just wanted to say a quick thank-you. The employee is associated with ONE TRADE only. The trade field is used for lookup purposes only. I will let you know of any success or if I need further help. Cheers!

elizabeth · Mar 29, 2000

Based on your response that there will be only one trade per employee, I would recommend an alternate structure: tblEmployee: EmployeeID, TradeID, other desc (FirstName, LastName, Address...) tblTrade: TradeID, other decriptive fields describing that trade. tblOrder: OrderID, any descriptive date about order (like date etc.) tblOrderDetail: OrderDetailID, EmployeeID tblEmployee 1:n tblOrderDetail tblOrder 1:n tblOrderDetail tblTrade 1:n tblEmployee You can use a form based on Order with a subform based on OrderDetail. In the form should be a bound combobox. Re: showing Employee-related data, there are several ways to do it. If you need to capture the EmployeeID but only want to display Name and Trade, here's a sort of easy way to do it:   1. Write a query using  tblEmployee and tblTrade. Column1 would be EmployeeID and the second column would be something like this (you have to key it in where the field name usually goes in the query grid):     NameAndTrade: FirstName & " " & LastName & " - " & Trade     This would show up as something like:     John Smith  - Carpenter 2. Go into View/SQL and copy this SQL stmt. 3. Go into the RowSource of the combo and paste it in there. 4. Set the combo's ColumnCount property to 2, and the ColumnWidth to  0,3     You can modify that second number to whatever width you need to display

Niki · Mar 30, 2000

Elizabeth... thank-you for your response! I am thrilled to have received yours and Amiels. I have printed your reply and will be trying these out hopefully today. I do appreciate the help and I will let you know the results. Cheers!

elizabeth · Mar 30, 2000

Good luck

But first... a correction: tblOrderDetail: OrderDetailID, OrderID, EmployeeID

Niki · Mar 30, 2000

Elizabeth.... I was just reviewing my print out and I should have made a note of this in my first reply.... being a somewhat novice... can you tell me in your respone where you have the 1:n ... (what is the n?) I am assuming that it is One to something! I apologize for my daftness. Usually I understand, but this one I don't. Thanks for the amendment as well!

I am about to attempt your instructions.

elizabeth · Mar 30, 2000

That's the generally used notation for "1 to many". You know how people use "x" to mean a single unknown, people generaly use "n" to stand for the final number in a range of unknowns. As in: For counter = 1 to n     Do whatever So 1:n means "from 1 to some unknown number" and actually it can include 0.

Niki · Mar 30, 2000

okay... so here's what I have done so far! created allllll the tables and the fields that you suggested. Did the form...... BUT having trouble creating a BOUND combobox...... so I went to the next step ANYWAYS.... and created the query as suggested........... NOW.... I am on STEP #3 of your instructions and I cannot find that darn RowSource of the combo to paste the SQL stmt. When I was having problems creating the BOUND combo, would that be related to my RowSource issue? That's where I am at. Thanks and cheers...Niki

Niki · Mar 30, 2000

Elizabeth.... here I am again. Found my row source and all that other stuff. But it is still an UNBOUND object. AND... when I use my SQL statement as your suggestion.... well I get a PROMPT asking me for a TRADE .... I enter in a trade... ELECTRICIAN.... and when I go to select an employee they are ALL electricians! I just can't get this thing working. I'm not too frustrated... but its lunch time and I will be okay! If you have a moment to help me I'd appreciate it. I'd even send you cookies or donuts if that would help?! But your steps were very helpful and I am pleased so far. Its just my ignorance that gets in the way! I am taking classes Mon Tues April 3/4 so that should help! Cheers for now... Niki

elizabeth · Mar 30, 2000

To make a bound combo, go into the form in design mode and click on the field list icon on the toolbar. If EmployeeID or whatever is not in there, add that field to the query, that's the source of the items in the field list. Drag and drop the item in the field list that you want the item bound to onto the form. . If it is not a combo by default, right click on it and select Change to... combo.

Guest_imported · Mar 30, 2000

The previous replies were thoughtful but perhaps too much horse for the cart, so to speak. ?  Is there more than one employee sent on a given WO?      (I'll assume it's possible if not common) Then, think in terms of DB structure before going into programming or Access specific features. 1. There are MANY trades but employee has ONE 2. There are MANY work orders and MANY employees per order. So break (2) down into underlying one-to-many's thus: Each WO Detail table entry has a Emp/WO# combination that is unique although the Emp# and WO# ID's can appear in other combinations. TRADE         EMPLOYEE          WO        WO DETAILS TrID#           EmpID#          WoID#       WoID# Description     TrID#(fkey)     Descript.   EmpID# EX: Tr#01           Emp#01(Bob)     WO#1       WO#1 Plumber         Tr#01                      Emp#01                           Emp#02(Betty)              WO#1                                             Emp#02 Bob & Betty are both plumbers and they got sent on the same job.  The same job ID appears, but the combinations of job and emp # are unique (concatenated primary key) To have the trade appear automatically, which is feasible since the trade is linked to each employee, you'll need to build a query that shows the trade for each employee based on the link.  You can then build a form upon the query to show all of it.   From a fellow beginner.

Amiel · Mar 31, 2000

Yea. O.K. Seems like we have a thing going here. Do, by all means identify the 'wizzard' Icon on the 'tools' tool bar. And use it. Every successful programmer does these days. That is; any tool that is available that gets the job done and saves time too, well, we use them.  Wizzards are great! Suggestion. When creating a list box or combo box use the wizzard. If you haven't created the query, or table that the wizzard will use to create the combo box for the form. Stop and create the query or table. Take all day. Then examine the code that the wizzard has generated. This is the functionality that we are after. Code that actually does something usefull (without having to think about it all that much). Routine stuff. O.K. I'm leaving for Washington D.C. early next week so what are the chances of getting this thing up and running before then? Amiel <a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a> <a href= > </a> Amiel

elizabeth · Mar 31, 2000

Sumwhat, what am I overlooking in your response? Your suggestions for table structure look identical to mine, ditto relationships.

Quehay · Mar 31, 2000

Elizabeth, You're quite right!  I realized after that I hadn't read your submission(s) thoroughly enough. I just wanted to parse the SQL since the user appeared to be at a very primitive stage of use (yours truly at perhaps one remove from same. . .)  "sumwhat"

Niki · Mar 31, 2000

Everyone here is WONDERFUL! I will be working on this from home on Saturday and will respond with results sometime that day. Thanks to everyone. I am taking classes Mon/Tues at the college here in the city. And I tend to make more use of Access as I progress thru the training. I am EXTREMLY pleased with this site as the input has been overwhelming. Cheers to all.... Niki

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

AutoFill of fields... a clear understanding is needed 2

Niki

MIS

Amiel

Programmer

Niki

MIS

elizabeth

IS-IT--Management

Niki

MIS

elizabeth

IS-IT--Management

Niki

MIS

elizabeth

IS-IT--Management

Niki

MIS

Niki

MIS

elizabeth

IS-IT--Management

Guest_imported

New member

Amiel

Programmer

elizabeth

IS-IT--Management

Quehay

Programmer

Niki

MIS

Similar threads

Part and Inventory Search

Sponsor