SUBFORM DROP DOWN LIST

Question

I have a form that shows employee name, estimated start time, estimated end time, a yes/no check box which all these fields are in one table. I would like to have a subform where I can select what unit # the employee is working on and there maybe be more than one unit so I need to be able to add more than one. The unit # will come from a list of #'s which have their own table. I cannot seem to get this subform to function the way that I would like. Can anyone assist me with this?

Answer 1

If you have multiple units per company then you will need tables for both units and companies as well as the EmployeeUnits table on which the subform is based, e.g.

Units

....UnitID (PK)

....UnitNumber

....CompanyID (FK)

Companies

....CompanyID (PK)

....Company

Each table can have further columns of course to represent other attributes of Units and Companies respectively.  Diagrammatically your model is like this:

Employees----<EmployeeUnits>----Units>----Companies

EmployeeUnits is modelling a binary many-to-many relationship type between Employees and Units by resolving it into two one-to-many relationship types.

In the subform the combo box in which the unit is selected would be set up as follows:

ControlSource:    UnitID

RowSource:     SELECT UnitID, UnitNumber, Company FROM Units INNER JOIN Companies ON Units.CompanyID = Companies.CompanyID  ORDER BY UnitNumber;

BoundColumn:   1

ColumnCount:   3

ColumnWidths:  0cm;2cm;3cm

ListWidth:          5cm

If your units of measurement are imperial rather than metric Access will automatically convert them.  The important thing is that the first ColumnWidths dimension is zero to hide the first column.  Experiment with the other dimensions to get the best fit.  The ListWidth should be the sum of the ColumnWidths dimensions.

To enter a unit number which is not currently represented in the Units table you can type it into the combo box and use its NotInList event procedure to open a form bound to the Units table, passing the new value to it.  In this form you'd select the company to which the unit relates via combo box, whose NotInList event procedure will allow you to enter a new company of necessary.   You'll find examples of the use of the NotInList event in the file NotInList.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file you'll see that there is a form in which you can enter a new city in a combo box, which opens a form in which you can select or enter the region in which the city is located.  This is analogous to your situation.

Answer 2

I have created this child table and when I try and enter the unit # in the subfrom it says 'You cannot add or change a record because a related record is required in table COMPANY INFORMATION T'.

My unit #'s are connected to this table to show which unit # belongs to which company. Any idea what I am doing wrong?

Answer 3

Did you try doing what I suggested? You have a one to many relationship since a worker can be assigned multiple units. Therefore, you need a child table like I suggested, whihc requires that the Units be assigned using a subform.

Answer 4

I am not sure this has helped.  My form kind of looks like this:

Date           Worker             Start Time             End time           Worked

3/1/2013   John Smith      6:00                       16:30

Unit # 1

Unit # 2

I would like to add unit #'s either under neithe or to the right but need to be able to have more than one unit # connected to this one entry on this form and the unit #'s come from a table of unit #'s

Answer 5

You need a table (call it tblEmployeeUnits) something like this:

EmployeUnitID PK Autonumber

EmployeeID (Foreign Key)

UnitID (FK)

TimeSpent.

Your subform is bound to this table and linked tot he main form on EmployeeID. You add a combobox for the user to select the Unit and a text box to enter the Time.