dlookup

[Guest]

My employees are required to use anywhere from one to four pieces of equipment for a specific job. I have created four combo boxes on a form that allow the user to select one of twelve pieces of equipment. The combo box lists the equipment name and the equipment number. I’m assuming I need to use a dlookup function so that my form which will recognized the selected equipment and fill in the appropriate price.

Equip1 Equip2 Equip3 Equip4 are the combo box names – the control source is called [Equipment 1], [Equipment 2], [Equipment 3], [Equipment 4]

The equipment name, equipment number and equip price- all come from a table called “Equipâ€

I don’t know where to begin. Any help is appreciated
Thanks
Dan

 

[Mike Painter]

My employees are required to use anywhere from one to four pieces of

equipment for a specific job. I have created four combo boxes on a form
that allow the user to select one of twelve pieces of equipment. The combo
box lists the equipment name and the equipment number. I'm assuming I need
to use a dlookup function so that my form which will recognized the selected
equipment and fill in the appropriate price.

Equip1 Equip2 Equip3 Equip4 are the combo box names - the

control source is called [Equipment 1], [Equipment 2], [Equipment 3],
[Equipment 4]

The equipment name, equipment number and equip price- all come from a table called "Equip"

I don't know where to begin. Any help is appreciated.
Thanks,
Dana

I would use a subform related to the job. The subform would contain a key, a
foreign key (the job number), and the item description in a combo box. The
user would just add the items to the subform.
Another way would be to show all the items with a check box they could tick
off to pick the items.

Your way will cause problems when you want reports on the usage of the items
when some fields are left null, when the boss says, "For this job they can
use five items" and when he or she says that they can always use six items.

 

[Gary Schuldt]

Dana,

can they select the same piece of equipment 4 times?

Gary

My employees are required to use anywhere from one to four pieces of

equipment for a specific job. I have created four combo boxes on a form
that allow the user to select one of twelve pieces of equipment. The combo
box lists the equipment name and the equipment number. I'm assuming I need
to use a dlookup function so that my form which will recognized the selected
equipment and fill in the appropriate price.

Equip1 Equip2 Equip3 Equip4 are the combo box names - the

control source is called [Equipment 1], [Equipment 2], [Equipment 3],
[Equipment 4]

 

[Guest]

yes Gary, it’s rare. However, for a larger job more than one air compressor may be used. They need to know that it will cost $X.00 amount per hour to use each compressor. Dana

 

[Guest]

I will be creating future reports on this information. Which equipment was used for a particular job? How much did it cost per hour for each piece of equipment? and How much did it cost for the entire day (however many hour were spent in one work day)? for the entire project (however many days it may take to complete)
Job 1: used 1 Pump for 3 hours at a cost of $200/hour, 1 backhoe for 2 hours at a cost of $100/hour. PUMP-$600, BACKHOE $200, TOTAL FOR DAY $800, TOTAL FOR JOB $80
Job 2: used 1 tandem trailer on day 1 for 4 hours at a cost of $25/hour, on day 2 for 2 hours at a cost of $25/hour, 2 backhoes for 2 hours on day two
Day 1-TRAILER-$100, TOTAL FOR DAY-$10
Day 2-TRAILER--$50, BACKHOE 1-$200, BACKHOE 2-$200, TOTAL FOR DAY $450, TOTAL FOR JOB - $550

I don’t necessarily need the totals on the form. It would be nice however for the job supervisor to know how much they are spending/wasting as they do the data entry. - not that they really care (did I say that out loud)

 

[John Vinson]

My employees are required to use anywhere from one to four pieces of equipment for a specific job. I have created four combo boxes on a form that allow the user to select one of twelve pieces of equipment. The combo box lists the equipment name and the equipment number. I’m assuming I need to use a dlookup function so that my form which will recognized the selected equipment and fill in the appropriate price.

Equip1 Equip2 Equip3 Equip4 are the combo box names – the control source is called [Equipment 1], [Equipment 2], [Equipment 3], [Equipment 4]

The equipment name, equipment number and equip price- all come from a table called “Equip”

I don’t know where to begin. Any help is appreciated.
Thanks,
Dana

As Mike and Gary say, you should really restructure your table.
"Fields are expensive, records are cheap" - a better structure would
involve THREE tables:

Jobs
JobID
<information about the job as a whole>

Equipment
EquipmentID
Description
ModelNo
SerialNo
CostPerHour
<etc, whatever you need to know about a particular piece of
equipment>

EquipmentUsed
JobID <<< what job was this used on
EquipmentID <<< what equipment was used
HoursUsed
CostPerHour <<< copied from Equipment, since the cost might change
but you want to capture the cost at the time of the job
<any other needed info about this job/this equipment>

You'ld use a Form based on Jobs, with a Subform based on
EquipmentUsed. It would be easy to put a textbox on the footer of the
Subform to sum up the total cost of the job, and to calculate on each
row of the subform the cost for this particular piece of equipment.