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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|