Append rows from a table into another table using a list box

[tommu]

Hi, I am creating work to lists from a list of standard activities.

Each activity has a time column that may be changed but the other columns in
the row are standard.

I have created a table with the standard activity data eg.
ActivityID(Primary key), ActivityNo, Description, StdHrs. I do not need all
of these activities for every job and need to select only those that I need
and add them to my list of activities in my job table.

When I create a new job, I click on the list box to select an ActivityNo and
want all three fields (ActivityNo, Decription, and StdHrs) to be appended to
my job table.

The job table has the fields JobID(Primary key), JobNo, ActivityNo,
Description, PlannedHrs. I then change the planned hours if necessary go on
to select the next ActivityNo that I want to include in the job.

Thanks

 

[JP]

It's not clear from your post what your question is, but in general there
are two ways to append rows from one table into another table using a list
box.

1. Bring into your listbox all of the columns that you want to append into
the other table. The individual columns can be made visible in the listbox
by giving each one a non-zero width in the listbox properties, or individual
columns can be made non-visible by giving the a 0 (zero) width in the
listbox properties. Then, once a row of the listbox is selected, you can
use code to append the individual columns from that row into the other
table. The individual columns of a selected row in a listbox are accessible
in code as

me.listbox1.column(0)
me.listbox1.column(1)
me.listbox1.column(2)

etc.

The column contents are accessible via code even for those columns that have
0 column width.

2. Store in the listbox just the key information needed to retrieve the
selected record from the "from" table and append it to the "to" table.
Again, you would access the listbox columns in code once a row has been
selected to find out what has been selected.

 

[tommu]

Thanks JP,
Perhaps I should have said I'm new to all this...
I can bring all of the fields from the table containing my standard data
into the list box and all are displayed when I open it.
I need to understand how/where I put the code so that these fields are
appended into the table I'm appending.
Thanks

 

[Ken Sheridan]

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