Add multiple fields in a form

[Dan]

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.

 

[Philip Herlihy]

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

 

[Dan]

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]

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

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)

 

[Dan]

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]

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

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 Adams via AccessMonster.com]

As far as the combobox problem goes, which was the originally stated problem,
I believe, you can solve this by making a simple query with, in this case,
Table 3. Simple place all fields in the query. Now while in the Query Design
Grid, go to a blank field in the query grid and type this in:

BilingualEquip: [equip eng] & " " & [equip sp]

Now go into Design View for your form and replace the table in the
RecordSource with your query. Delete your old combobox and create a new one.
This time, when the Wizard comes up and asks for the table or query to
retrieve the data from, select your new query. Next choose BilingualEquip as
the field for your combobox.

That should do it. Now, after the selection is made, both English and Spanish
part names will still be visible, and can be placed into a textbox, if need
be.

You can do the same thing with the descriptions in English and Spanish.

Having said that, the advice about normalization is well worth heeding.

Linq, from the venerable Commonwealth of Virginia!

 

[Philip Herlihy]

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 sheet. 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:

Phil