Form/Table structure

[Mike]

I want to create a form which contains details of a job to be performed by a
contracting company on the site. Each company will have several jobs and many
workers.
I need to be able to create a new job, select a company it will be performed
by and then select the workers from that company to do the job.
I'm normally quite good with access but the construction of this one is
baffling me.
Any help on table structures and form code would be much appreciated.
Thanks

 

[Allen Browne]

You need at least these 5 tables:

a) Company table, with CompanyID primary key.

b) Worker table, with WorkerID primary key.

c) CompanyWorker table, with fields:
- CompanyID Number the company this person works for
- WorkerID Number the person who works for this company.

d) Job table, with JobID primary key and CompanyID foreign key.

e) JobWorker table, with fields like this:
- JobWorkerID AutoNumber primary key
- JobID Number relates to Job.JobID
- WorkerID Number relates to Worker.WorkerID

Your company form will have a subform where you select the workers for that
company.

Your job form will have a subform where you assign the workers to the job.
In the Current event of the job form, set the RowSource of the WorkerID
combo. That way, only the workers for that company show up in the subform.

(There is a potential display issue here: if a worker switches company after
you've assigned them to a job, when you go back and visit that job, the
subform will show a blank combo, because the name for that WorkerID is no
longer associated with the company doing the job.)

 

[Mike]

Thanks very much for your help.

You need at least these 5 tables:

a) Company table, with CompanyID primary key.

b) Worker table, with WorkerID primary key.

c) CompanyWorker table, with fields:
- CompanyID Number the company this person works for
- WorkerID Number the person who works for this company.

d) Job table, with JobID primary key and CompanyID foreign key.

e) JobWorker table, with fields like this:
- JobWorkerID AutoNumber primary key
- JobID Number relates to Job.JobID
- WorkerID Number relates to Worker.WorkerID

Your company form will have a subform where you select the workers for that
company.

Your job form will have a subform where you assign the workers to the job.
In the Current event of the job form, set the RowSource of the WorkerID
combo. That way, only the workers for that company show up in the subform.

(There is a potential display issue here: if a worker switches company after
you've assigned them to a job, when you go back and visit that job, the
subform will show a blank combo, because the name for that WorkerID is no
longer associated with the company doing the job.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.




.

 

[Mike]

So far so good. I'm still having a little trouble with the rowsource part.
Would you be able to give me the coding structure for this also?
Cheers

 

[Allen Browne]

The code will take this approach (in the AfterUpdate event of CompanyID in
the main form):

Private Sub CompanyID_AfterUpdate()
Dim strSql As String
strSql = "SELECT ID, TheName FROM Table1 WHERE "
If IsNull(Me.CompanyID) Then
strSql = strSql & "(False);"
Else
strSql = strSql & "(CompanyID = " & Me.CompanyID & ");"
End If
Me.[Sub1].Form![Combo1].RowSource = strSql
End Sub

Private Sub Form_Current()
Call CompanyID_AfterUpdate
End Sub

You'll need to use CompanyID.OldValue in the Undo event.