Hi John! Here the code so far. Everything looks Ok but I get an
'Run-time error '3464': Data type mismatch in criteria expression'. It
points on the first SQL expression:
Set rstI = db.OpenRecordset("SELECT * " & _
"FROM tblOffers " & _
"WHERE OfferID='" & Me.OfferID & "';")
I tried to use a variant type for OfferID like this:
Dim varOfferID As Variant
varOfferID = Me.OfferID
and finally "WHERE OfferID='" & varOfferID & "';") but to no avail.
The whole of the code looks like this:
Dim db As DAO.Database, rstI As DAO.Recordset, rstO As DAO.Recordset
Dim lngOrderID As Long
Dim strSQL As String
Set db = DBEngine(0)(0)
' Get the record to copy
Set rstI = db.OpenRecordset("SELECT * " & _
"FROM tblOffers " & _
"WHERE OfferID='" & Me.OfferID & "';")
' Open a second recordset to append a row
Set rstO = db.OpenRecordset("SELECT * FROM tblOrders", _
dbOpenDynaset, dbAppendOnly)
' Start a new record
rstO.AddNew
' Copy the values (but not the Autonumber key)
rstO!CstID = rstI!CstID
rstO!SalespersonID = rstI!SalespersonID
' Save the new autonumber
lngOrderID = rstO!OrderID
' Write the new Order
rstO.Update
' Close the recordsets
rstI.Close
rstO.Close
Set rstI = Nothing
Set rstO = Nothing
' Now do your copy Insert here using lngOrderID to
' relate the copied tblOfferDetails records to the new offer
strSQL = "INSERT INTO tblOrderDetails " & _
"(OrderID, ItemID, ItemQuantity, ItemDiscount) " & _
"(SELECT " & lngOrderID & " As OrderID, " & _
"ItemID, ItemQuantity, ItemDiscount " & _
"FROM tblOfferDetails " & _
"WHERE OfferID='" & Me.OfferID & ")';"
DoCmd.RunSQL strSQL
Then I'd have do write two DELETE queries to remove data coppied to
tblOrders and tblOrderDetails from tables tblOffers and
tblOfferDetails respectively.
Take care,
Theodore
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
