Form Design Questions

[Damian]

It it possible to do something like this:

Consider these 4 tables from this screen shot.
TblCrew
TblJobs
TblCrewType
and a link table between them TblJobDetail.

http://img194.imageshack.us/img194/1790/relt.jpg

I wnt to create a form where the user will be able to first choose the
CrewType from combo box, then in the next combo box will choose the name that
corresponding to the choice You made in the first box. Then he will ender
values for jNumberOfForeman etc from TblJobs set as a subform or whatever
will work.

How will that link together? How can I create the form from TblJobDetail
that will show me all the info that I want? Or do I need to first create a
form that will link each cCrewID to appropriate JJobID for that Day/Date?

Im a little confused on how to make that work, because when I tried to
create a form for TblJobDetail I just got the ID's of fields which told me
nothing.
I know I have been at this for a while, but I really want to learn this and
do this little project I planned.

Thank you,
Damian

 

[CraigH]

Hi,
First you need to rethink the design of the tblJobs and tblDetails.
Will be back to that in a second.

When you see only the ID's for the fields that you want to see a real
value you can change that text box to a combo box to look up the value I'll
point you in the direction but don't have time to explain
Format-Change to Combo box
Properties - Data->Row Source, Format->column Count, Column Widths
You can see an example by useing a wizard to creat a combo box with the
first option "I want the combo box to lookup..."

Ok back to the design of the tables since that is the basics of everything
else:

I don't know how you view it but I will state how I view the names of the
tables so I have some basis to start with.

I view Jobs as the top level since it is the 1 to many.(i.e. Working on a
building)
JobDetails is the individual tasks (electrical, plumbing etc)

Your JobDetail is really just a list of crew for a particlular Job and/or
you are duplicating data that doesn't need to be duplicated for each of those
crew i.e. the weather and Subcontractor.

You need another table:
tblCrewDetails
"Either" cdJobID (if the crew works for the whole Job and you don't need the
details)
"or more likely" cdJobDetailID
cdCrewID FK

And remove jdCrewID from JobDetail

Next if crew type is Forman, Workman, laborers - you probably don't need
that in the Jobs table because you will calculate that as needed. This is
actually more invovled if you really needed to do "projected needed people"
but your description doesn't suggest that.

So with that gone from Jobs you should have just JobName or some other
description. The JobDetails will also need a JobDetailName if you are going
to that level.

So with the CrewDetail table wether it is connected to the Job or JobDetail
it will be a subform and you will have changed the cdCrewID to a combo box
that looks up the items in tblCrew - hint for full names you will have the
row Source query have Something like "FullName: cFname & " " & cLName" for
one of the fields and another field will have the ctType because you will
have that as part of the query.

In this scenario you will not select the crew type first - really no need.
But as a help it can be developed that you can limit the combo box to look up
only a certain type and change this as you go along (to involved for this
begining) also you can show the Type within the subform for CrewDetails

There is still more to consider before you start on the forms - right now
the tables still need to be addressed