Forms are evil

[CAL]

I'm trying to create a form for a service call database
where the operator can enter a customer, create a
workorder, and specify which equipment (that the customer
owns) will be repaired on the WO.

Since Equipment ID is a primary key on Equipment, and a
foreign key on Workorder, I cannot create a new workorder
without equipment being present. The problem with this is
that Equipment is a one to many with Workorders.

Do I need to reverse the process so equipment is added to
a location before a workorder is generated?
Then just search present equipment and have a subform for
new workorders?

 

[Pavel Romashkin]

Can't you simply use Equipment data for a RowSource of a Combo box, make
the box 2-column having Column1 as EquipmentID and hiding it, then bind
the combo to the EquipmentID FK on Workorder?
It would definitely make sense to store EquipmentIDs used by each
customer in a separate junction table to limit the RowSource to only
those IDs that current customer owns.

Pavel

 

[Guest]

You're so smart Now I just need to figure out what that
meant. I'll let you know.

Thanks!

 

[CAL]

Hmm.. yes.. I'm officially still clueless.

-----Original Message-----
You're so smart Now I just need to figure out what that
meant. I'll let you know.

Thanks!

hiding
it, then bind used
by each
.

 

[Guest]

Let me take another stab at this...

What I'm aiming at is that when an operator creates a
workorder (which is within a subform with CustomerID and
CustomerName in the primary form) they will assign
equipment to it by choosing Manufacturer, Model and SN out
of an equipment table that is filtered to just the
customer's equipment.

The customers have multiple locations, which in turn have
multiple equipment. I think this is throwing a snag in my
master plan. CustomerID is an FK on LocationID, and
LocationID and WorkorderID are FKs on EquipmentID.

I've tried to create a junction table.. but I get results
of just the equipmentID, which the operator will not be
familiar with.

Thanks

 

[Pavel Romashkin]

Ok... I am slowly getting confused myself.
So, the main form has Customer name and ID. The subform is for creating
the workorder, and you can do that by selecting specific Equipment on
the subform. The equipment may not be for the location that the work
order is for.
How about you add Location to the main form? This will allow to filter
the equipment further and not have Equipment that is not at the needed location?
As for seeing IDs in place of Equipment - this is the matter of
displaying one and entering the other. This is accomplished using a
2-column combo box. If this is the problem, I can elaborate.
If you still are stuck, empty the DB out, leave 1-2 dummy records and
post or send a URL - I will be glad to take a look.

Pavel

 

[CAL]

I had tried to add Location to the main form, but it did
not work for some reason. This confused me to no end
because I have a form that has Customer, Location and then
Equipment underneath it. I'm pretty sure the problem is
between Workorder, Equipment and Location.

Can you please explain what you mean by a 2-column combo
box? I tried toying with these yesterday and (surprise)
ended up confused.

I have put PDFs of my files on
www.photobydesign.net/ServiceDB/


Customers Locations Equipment.pdf - Allows user to scroll
through customers, add locations and add equipment to
those locations (which confuses me because that must mean
the relationship between location and equipment is set up
correctly)

ERDiagram-Revised2-2.pdf - My original ER Diagram for this
project

Table Relationships.pdf - The Access screenshot of table
relationships.. sorry, it's a bit messy.

Workorder Entry Form.pdf - The form I wish I could get
right.. it should be like the form above, but should allow
for workorders to be entered from it.

Workorder Subform.pdf - The subform from the above form.


Thank you so much for your help!

CAL

 

[Pavel Romashkin]

I have been looking through your forms, but the most interesting thing I
found to be is the Relationships.
I see that Workorders is linked to Employees through CustomerID. Is this
a mistake? Equipment is linked to EmployeeID. Is that a mistake?
EquipmentID is not linked to anything at all, and CustomerID is linked
to RID.
Allright, relationships are optional in the first place unless you
follow the defaults when designing queries. But they are often entered
into Parent-Child links of Form-Subform, so if you didn't manually chage
this, your forms might be confused.
Next, CustomerLocation is linked to JunctionEquipment and to Equipment.
Equipment is linked to JunctionEquipment, too. This sort of triangular
linking can prevent table updates if you have referential integrity
turned on.
Now, I would really use combo boxes on your forms. There is no point in
showing various record IDs - they are not intuitive. I recommend using
both unbound lookup combo boxes and bound selecting combo boxes. Use the
former to select Customer and Location for the new Workorder, and you
can use the latter for selecting which Equipment will be on the Workorder.
Multi-column selecting combo box is set up in the following way. You
tell the combo box to use 2 (or more) columns. Set up its row source to be

SELECT EquipmentID, Model & ', ' & MachineType AS Description FROM
Equipment WHERE LocationID = " & Forms!WorkOrder!LocationID

and set column widths to 0",2", set Bound column to 1. This will cause
the EquipmentID to go into the underlying table but Description to be
displayed. Now you will be able to pick Equipment using its name.
It also looks like this thread is going beyond the common interest of
the NG, since it gets to the specifics of your particular design. I am
willing to help you find the solution, but it might be better to go to
private email for this.

Good luck,
Pavel