Reply to thread

Message: [QUOTE="Ken Sheridan, post: 1971781"] As it seems from your description that more than one activity can be included in a job you have a many-to-many relationship here, so you need an extra table, JobActivities say, to model this. You don't need any columns in the Jobs table referencing the Activities table or vice-versa; what you have are two foreign key columns JobID and ActivityID in JobActivities, referencing the primary keys of the other two tables respectively. Together these two columns are the composite primary key of the table. As you want to be able to amend the StdHrs value per activity per job, however, you also need a StdHrs column in the JobActivities table to which you'll assign the default value from the Activities table ready for it to be edited. For data entry purposes you'd have a jobs form in single form view, based on the Jobs table or better still a sorted query on that table so the records in the form are ordered logically on whatever column or columns seem appropriate. Within this form you'd have a subform based on the JobActivities table. The subform would be linked to the parent form on the JobID columns as the LinkMasterFields and LinkChildFields properties of the subform control, and would have a combo box bound to the ActiyityID column, a text box bound to the StdHrs column, and unbound text box for the Description. The subform can be in continuous form view or single form view as you prefer. The former is more usual with data like this as you can then see all activities per job listed simultaneously in the subform. The ActivityID combo box, which I'd suggest you name cboActivity, would be set up with the following properties: RowSource: SELECT ActivityID, StdHours, ActivityNo, Description FROM Activities ORDER BY ActivityNo; BoundColum: 1 ColumnCount: 4 ColumnWidths: 0cm; 0cm; 3cm; 5cm ListWidth: 8cm If your units of measurement are imperial rather than metric Access will automatically convert them. The important thing is that the first 2 dimensions of the ColumWidths property are zero to hide the first two columns. Experiment with the last two dimensions to get the best fit, but the ListWidth property should equal the sum of the ColumnWidths. You can change the order from ActivityNo to Description if you'd prefer the list ordered by description rather than activity number. For the unbound text box to show the description, txtDescription say, the ControlSource property would be: =cboActivity.Column(3) The Column property is zero-based, so Column(3) is the fourth column. To assign the default standard hours to the control, called txtStdHours say, bound to the StdHrs column in the subform's underlying JobActivities table put the following in the cboActivityControl's AfterUpdate event procedure: Me.txtStdHrs = Me.cboAcivity.Column(1) When you select an activity in the combo box on the subform it will show the activity number in the combo box, the description will show in the unbound txtDescription control and the default standard hours will show in the bound txtStdHrs control ready fro editing if necessary. The AcitivityID is not seen as its an arbitrary value with no intrinsic meaning; it does its job of linking the rows in each table behind the scenes. Ken Sheridan Stafford, England [/QUOTE]

Verification