Re: Add multiple fields in a form

Philip Herlihy wrote:
Dan wrote:
I will try to explain

Table 1 - work order - w/o ID, date, w/o #, equip eng, equip sp, user, problem eng, problem sp, qty of parts, mfr p/n description, part cost, mech
Table 2 - user - user ID, user
Table 3 - equipment - equip ID, equip eng, equip sp
Table 4 - MFR - mfr ID, mfr
Table 5 - mechanic, mech ID, mech
Table 6 - problem - problem ID, problem eng, problem sp
Table 7 - inventory - inventory ID, mfr,mfr p/n, description, part cost,in stock, used 09, bought 09

FYI, We own a Lawn Maintenance Company

The idea here is for the workers to fill out the repair form when they get back in the evening, print it out and the mechanics fix the broken equip. overnight. After fixing the equip. the mechanics input the time, parts into the computer. If everything works right it will also take the parts out of inventory and the inventory report should tell us when we are running low on parts. With this info we can track the cost of each repair. We can see if it is cost effective to keep repairing old equip. or replace it. Also we can see if a particular user is breaking alot of equip. I have been using MS Works for the past 12-15 yrs and see how powerful Access can be.

Thanks





"Philip Herlihy" wrote:

Dan wrote:
Phil,
I fully understand Paragrapgh 2. I'm clueleess on Paga. 1. The issue is the form that prints out for the mechanic needs to have both the english and spanish names and description because we don't know who will be working on it. So and engilish person might fill it out and a spanish guy works on it or visa versa.

Second issue, If the mechanic inputs p/n abc123 on the form I want want the associated description of the part and the price to automaticlly be inputed on the form.

Thanks, Dan - Virginia

"Philip Herlihy" wrote:

Dan wrote:
I am building a program for repair orders in 2007. I am dealing with both english and spanish speaking employees. I have one field with the english name for a piece of equipment and another field with the spanish name. I can do a lookup box from another table that shows both fields so either employee can choose the correct piece of equip. The issue is when they choose one from the drop down box only the english name shows up and either a english or spanish guy may work on the equip. so both need to show up. Also on the same note when the mechanic fills out the repair order he looks up the part form a drop down box and I would like the description (same issue both in english and spanish) and price to be put up on the work order w/o having to go to each field and looking it up.


My solution to this would be to have an option group so that the user can choose the preferred language, and have the RowSource properties of the Combo boxes set to a query that references that checkbox, delivering the text the user needs.

Of course, combo boxes can display more than one field - they routinely have two fields, with the field width of one (the key field) set to 0. If you add the Spanish field(s) to the RowSource and set column widths to appropriate amounts, then both will appear together.

Phil, London

Are we talking about a form (on-screen) or a report (printed, and fixed) here? If it's a form, then you can choose to display only the preferred language. If it's a report, then you'll have to print both.

Just to expand on the first option (which assumed a form on-screen): an option group is sometimes called a radio-button - when one is pressed, the other cancels. You could use a checkbox to select Spanish, but that might be interpreted as assuming English has priority (or vice versa), and you don't want to go there!

A form is (normally) based on a query, rather than a table - more flexibility. Your query can detect which language is selected on the form, and return the Spanish version from the table, rather than the English (or vice versa). You'd need an Expression in the query to do this (have a play with the Expression Builder). Alternatively, you could change the Control Source property of your text boxes to pick one field or another, depending on which language is selected. It would be possible to devise a form which would change from one language to another entirely (labels too) at the click of a button - just don't expect Access to do any translating for you (so any text input by users will stay in their language).

The real key to all this will lie (as is just about always the case) in the table structure. Each item will have a description, and you'll need two description fields, one for each language. Then you'll need to devise a way of displaying both, or displaying whichever is preferred. Why don't you post a description of how you think the data should be divided between tables, and we'll have a look at that. If your tables aren't right, everything is about 100 times harder. You might find this video on table design useful:
http://www.lynda.com/home/Player.aspx?lpk4=31001

Phil, London (away now for about 48 hours)


Just a quick look for now (more tomorrow) but I can see problems in your tables, which aren't correctly "normalised". Table 1 (you could name this better) has equip eng and equip sp, and so does Table 3. You should store in Table 1 only the Equip-ID from Table 3. Same for Problem, and probably same for Part (although I don't see a table for Part). Part, in turn, would contain only a reference to the MFR table. This sort of thing is vital if you're to have the flexibility you'll need, and time spent getting this right will be repaid handsomely.

Phil


Linq's suggestion will certainly work, but may be ugly when printed, especially where descriptions are lengthy.

It sounds as if you are printing the forms directly, instead of creating a report - that's fine. You may need to set a filter on the form before printing or you'll get all the records in the database, not just todays! (Use the Filter-by-form button, built-in to Access and described in Help).

For your first issue (getting both languages to display when selection is made in either language); I suggest you'd want your form based on the Work Order table, which would have a field referencing a record in the Equipment table, where all other Equipment details would be held. When filling in the form, you're aiming to have just the ID entered into the Work Order table.

You'd have two combo boxes, one for each language, probably side-by-side. The RowSource for the English combo would be a query (or SQL string) returning the EquipmentID and the Equipment-Description-English from the Equipment table. The Bound Column should be set at 1 (the ID field) and Column Widths set to 0cm;5cm - which would hide the ID value actually being stored and display only the English description in the drop-down list. So far so good - an English user could select the item from the descriptions, and the combo would store the ID into the Work Order table.

You'd have an equivalent for the Spanish combo, with the RowSource set to pick up the ID and the Spanish description; again, the Bound Column would be 1 (to store only the ID) and the column widths would have the first field set to 0cm so you don't see the ID, only the Spanish description text.

The next trick is to have one combo update the other. That's quite easy: you just configure the OnUpdate event for each control to Requery the other combo. So, your Spanish chap picks out an Equipment item, and the English combo immediately displays the corresponding English description and vice versa.

To do this, click on the button at the end of the OnUpdate item under the Events tab in the control's Properties ***. Choose the Code Builder, and if the control to be updated is named Equip_Sp_combo, you'd insert the line into the prodecure:
Me!Equip_Sp_combo.Requery
If you haven't done this before, it's all much easier than it sounds, but you do want to include some error-handling code, so you can track down what's wrong if/when you see problems. Here's the error-handling code from one of my command-buttons:

======================================
Private Sub Command_Next_Click()
On Error GoTo bust1
DoCmd.GoToRecord
Exit Sub
bust1:
MsgBox Err.Description, vbCritical, "Command_Next_Click()"
End Sub
======================================
If the instruction to go to the next record (DoCmd.GoToRecord) fails for any reason, it pops up a Message Box with "Command_Next_Click()" in the title, the Critical button and the system description of the particular error in the body of the Message Box.

This might help on Combo boxes:
http://www.techonthenet.com/access/comboboxes/bind_index.php

as might this:
http://www.youtube.com/watch?v=v_INowd8ro4

Phil
.