Mike:
You need to build and execute an SQL statement to insert the rows from the
OrderDetails for the other. Start with the command button on the frmOrder
form and in its Click event procedure open an unbound dialogue form which
contains a combo box to select the other order. Then having inserted the
rows via the dialogue form, requery the subform control on the frmOrder
form.
So the button's code will be like this:
' open select order form in dialog mode
DoCmd.OpenForm "frmSelectOrder", _
WindowMode:=acDialog
' requery subform control to show new rcords
Me.sfcOrderDetails.Requery
where frmSelectOrder is the name of the dialogue form, and sfcOrderDetails
is
the name of the subform control in frmOrder, i.e. the control which houses
the subform. Opening a form in dialogue mode pauses code execution until
the
dialogue form is closed, so the subform won't be requeried until the new
rows
have been inserted.
In the dialogue form the combo box, lets call it cboOrder, needs to have
the
primary key of the Orders table as its bound column, but as this isn't
very
good for identifying an order, also lists other data. For this example
I'll
assume that Orders has a LocationID foreign key column which references
the
key of a Locations table which contains a Location column, and that Orders
also has an OrderDate column; so the Location and OrderDate can be used
to
identify the order. Its RowSource property would thus be something like
this:
SELECT OrderNumber, Location, OrderDate
FROM Orders INNER JOIN Locations
ON Orders.LocationID = Locations.LocationID
ORDER BY Location, OrderDate DESC;
Its BoundColumn property will be 1, its ColumnCount property will be 3,
and
its ColumnWidths property something like 2cm;4cm;2cm. Access will convert
these to inches if you are not using metric units. Experiment with the
dimensions to get the best fit.
This will list the orders by Location and by descending date order per
Location.
Lets assume the OrderDetails table has columns OrderID, PartID, Quantity.
We
need to insert the value of the current order back in frmOrder, along with
each of the PartID and Quantity values for the previous order selected in
the
combo box, so the code needs to build and execute an SQL statement to do
this.
I'd suggest having two buttons on the form, one to confirm the selected
order,
the other to cancel. The cancel button just needs to close the dialogue
form
with:
DoCmd.Close acForm, Me.Name
The code for the confirm button does the real work with:
Const MESSAGETEXT = "No previous order selected."
Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria AS String
Dim lngOrderID As Long
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
If Not IsNull Me.cboOrder Then
lngOrderID = Forms("frmOrder").OrderID
strCriteria = "OrderID = " & Me.cboOrder
' build and execute SQL statement to
' insert rows into OrderDetails table
strSQL = "INSERT INTO OrderDetails( " & _
"OrderID, PartID, Quantity) " & _
"SELECT " & lngOrderID & _
", PartID, Quantity FROM OrderDetails " & _
"WHERE " & strCriteria
cmd.CommandText = strSQL
cmd.Execute
'close dialog form
DoCmd.Close acForm, Me.Name
Else
' no previous order selected so chastise user
MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
End If
I've assume in the above that the OrderID and PartID key columns are of
number data type rather than text, and so don't need wrapping in quote
characters when building the SQL statement. Remember also that any table
or
column names which include spaces or other special characters must be
wrapped
in square brackets in the SQL or VBA code, e.g. [My Table] or [My Field].
Ken Sheridan
Stafford, England
Mike said:
Hi Group,
Win xp, Access 2007
This is an estimating form for an electrical contractor.
We have the db set up with a main form for entering info like contractor
name, estimate control number, job location etc.
The main form has a continuous subform for entering the parts needed for a
particular project.
frmOrder. sfrmOrderDetails.
What happens sometimes is that there will be a property with identical
requirements as a previously entered property. There can be 100 or so
different parts for a job.
If we've entered all the info for the job at Maryvale subdivision lot 12
and
it turns out that Maryvale subdivision lot 21 has the identical, or close,
requirements how can we just say use the same order details as Maryvale
subdivision lot 12 on this order?
I envision something like a command button "Duplicate a previous order".
this would open a combo box to select the previous order to duplicate.
Then
the order details would fill in with the details from the selected
previous
order.
At that point I become clueless.
As always any thoughts, comments or suggestions are welcome.
Best regards,
Mike
