A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Form/Table structure



 
 
Thread Tools Display Modes
  #1  
Old February 2nd, 2010, 12:34 PM posted to microsoft.public.access.forms
mike
external usenet poster
 
Posts: 3,942
Default Form/Table structure

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
  #2  
Old February 2nd, 2010, 01:24 PM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default Form/Table structure

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 he 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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mike" wrote in message
...
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


  #3  
Old February 4th, 2010, 09:34 AM posted to microsoft.public.access.forms
mike
external usenet poster
 
Posts: 3,942
Default Form/Table structure

Thanks very much for your help.

"Allen Browne" wrote:

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 he 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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mike" wrote in message
...
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


.

  #4  
Old February 4th, 2010, 11:17 AM posted to microsoft.public.access.forms
mike
external usenet poster
 
Posts: 3,942
Default Form/Table structure

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" wrote:

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 he 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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mike" wrote in message
...
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


.

  #5  
Old February 4th, 2010, 03:42 PM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default Form/Table structure

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mike" wrote in message
...
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" wrote:

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 he 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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mike" wrote in message
...
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


.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:47 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.