Embedding a subform in a continuous form to simulate 1-1 relationship and data entry

Question

Hi!  I want to use Acess to facilitate a simple business process in my organization.  We have a production system that generates transportation requests (Primary Key = TRIP_ID).  My goal with the Access application is to be able to assign each trip to an employee.

I've joined to the production system via ODBC to get the trip table TCA_TRIP.  It is read only.  In Access, I've created a table TRIP_ASSIGNMENT with two fields: TRIP_ID and EMPLOYEE.  I want to display the list of trips from TCA_TRIP via Datasheet View or Continuous Forms, and have a 1-field subform on each row that enables me to enter the assignment in TRIP_ASSIGNMENT.  It's essentially a 1-1 relationship, and I've prepopulated TRIP_ASSIGNMENT with all current and possible future TRIP_IDs.

Any help would be appreciated!

Thanks,

Edward

Answer 1

Hi Edward, Subforms are not really possible in continuous forms but you might be able to use a sub datasheet.

Answer 2

You don't need a subform for this you can base a form, in continuous forms view on the Trip_Assignment table, and bind one combo box to the Trip_ID column and another to an EmployeeID column which references the primary key of a Employees table.  As you say that you have pre-populated the Trip_ID column in Trip_Assignment with all possible values, the form will show all trips, whether or not an employee has been assigned.

For the RowSource property of the Trip_ID column, return not only the primary key Trip_ID column, but whatever other columns from TCA_Trip you wish to show in the form.  Set the combo box's ColumnCount and ColumnWidths properties appropriately, so that whichever column you wish to select a trip by is the first column of non-zero width.  The RowSource query should be ordered by this column.  You can then reference the other columns in unbound text box controls in the form with:

=cboTrip_ID.Column(1)
=cboTrip_ID.Column(2)
=cboTrip_ID.Column(3)
=cboTrip_ID.Column(4)
etc

As the Column property is zero based these will reference the second, third, fourth and fifth columns respectively.  When you select a trip in the combo box these controls will be populated automatically with the corresponding values for the selected trip.

The EmployeeID column in the table should be of long integer number data type, referencing an autonumber EmployeeID primary key of an Employees table.  Names can legitimately be duplicated, so are unsuitable as keys.  Such a combo box would normally be set up as follows:

ControlSource:   EmployeeID

RowSource:     SELECT EmployeeID, FirstName & " " & LastName FROM Employees ORDER BY LastName, FirstName;

BoundColumn:   1
ColumnCount:    2
ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

Alternatively, you can concatenate the names so that the last name is first, which would be better with a large list of names as it allows the user to enter the initial characters of the last name and progressively go to the first match as each character is entered:

RowSource:     SELECT EmployeeID, LastName & ", " & FirstName FROM Employees ORDER BY LastName, FirstName;

To keep the Trip_Assignment table up to date if new rows have been  inserted into TCA_Trip it would be a simple task to automatically execute the following SQL statement at start-up:

INSERT INTO Trip_Assignment(Trip_ID)
SELECT Trip_D
FROM TCA_Trip
WHERE NOT EXISTS
    (SELECT *
     FROM Trip_Assignment
     WHERE Trip_Assignment.Trip_ID = TCA_Trip.Trip_ID);

This should be done before the form is opened.  If done after it has been opened it would be necessary to Requery the form to include the new rows in its recordset.

---

Ken Sheridan, Stafford, England