copy info from a form to another form

[Sean]

I have af form setup for our project managers to create a work order to the
shop. Once a job# is created in the Job List a work order is started for the
job. The project manager using the work order form picks the job# they are
working on and begins filling in instructions for the job. Each instruction
is a seperate record in the form. Often we'll have two or more jobs that
have the same list of instructions and right now they have to fill in the
form for each job manually. While in the new work order I would like to be
able to pull up a previous work order and select specific instructions to
import into the new work order.

I'm thinking I could create a form that pops asking which work order they
are copying from based on job#. The form would come up with a list of all
the instrucitions used in that previous work order. Using a check box in
each record they could click which instructions they wish to copy. This part
is easy. The next part tough is how do I tell it to copy those particular
records?

Thanks.

 

[tina]

well, it's not difficult, because data is only *displayed* in a form - it is
*stored* in a table. once you've identified the records *in the table* that
you want to copy, you can use an Append query to write them back to the
table as new records with the appropriate foreign key value linking them to
the new job record.

hth

 

[Ken Sheridan]

In the dialogue form I'd suggest you include three unbound controls:

1. A combo box, cboJobs, which lists all previous job numbers from which
you can select one to list its instructions, so its RowSource would be along
these lines:

SELECT [Job#] FROM Jobs WHERE [Job#] <> Forms!frmWorkOrder!cboJobs ORDER BY
[Job#].

2. A multi-select list box, lstInstructions, which lists all instructions
associated with the job selected in cboJobsbox. Set the list box control's
MultiSelect property to 'simple' or 'extended' as preferred.

3. A button to insert rows determined by the selection in lstInstructions
into the table.

The RowSource for lstInstructions would reference cboJobs . e.g.

SELECT Instruction FROM Instructions WHERE [Job#] = Form!cboJobs ORDER BY
Instruction;

Note that you as both controls are in the same form can use the Form
property in the SQL statement rather than a full reference to the control.

In cboJobs AfterUpdate event procedure requery lstInstructions with:

Me.lstInstructions.Requery

The code for the button's Click event procedure would be along these lines:

Dim varItem As Variant
Dim strSQL As String
Dim ctrl As Control
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set ctrl = Me.lstInstructions

' loop through the list box's ItemsSelected collection and insert
' a row into Instructions table for selected instruction
For Each varItem In ctrl.ItemsSelected

strSQL = "INSERT INTO Instructions ([Job#], Instruction)" & _
"VALUES(" & Forms ("frmWorkOrder").cboJobs & ",""" & _
ctrl.ItemData(varItem) & """)"

cmd.CommandText = strSQL
cmd.Execute

Next varItem

' requery the bound form to show new rows:
Forms ("frmWorkOrder").Requery

This assumes that the Job# column is a number data type and the Instruction
column a text data type. "frmWorkOrder" is the name of the bound form from
which the dialogue form is opened. The code also assumes that this form
contains a control cboJobs in which the Job# for which the work order is
being created is selected.

Ken Sheridan
Stafford, England

 

[richard]

hi