Theodore-
OfferID is a number, right? Then blow away the quotes:
Set rstI = db.OpenRecordset("SELECT * " & _
"FROM tblOffers " & _
"WHERE OfferID=" & Me.OfferID & ";")
Fix your assignment of strSQL also.
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
> -----Original Message-----
> From: [email protected]
> [mailto:[EMAIL PROTECTED] On Behalf Of tyki9799
> Sent: Thursday, June 23, 2005 6:47 AM
> To: [email protected]
> Subject: [ms_access] Re: Moving records between tables
>
>
> 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
>
>
>
>
>
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/
