Dear tyki9799 - (Do you have a real name?)
You can find out the "new" number if you insert the new tblOffers record
using DAO code:
Dim db As DAO.Database, rstI As DAO.Recordset, rstO As DAO.Recordset
Dim lngNumber As Long
Set db = DBEngine(0)(0)
' Get the record to copy
Set rstI = db.OpenRecordset("SELECT * " & _
"FROM tblOffers " & _
"WHERE ... <predicate to select the previous record >")
' Open a second recordset to append a row
Set rstO = db.OpenRecordset("SELECT * FROM tblOffers", _
dbOpenDynaset, dbAppendOnly)
' Start a new record
rstO.Addnew
' Copy the values (but not the Autonumber key)
rstO!SomeField = rstI!SomeField
' ... do them all here
' Save the new autonumber
lngNumber = rstO!OfferID
' Write the new Offer
rstO.Update
' Close the recordsets
rstI.Close
rstO.Close
Set rstI = Nothing
Set rstO = Nothing
' Now do your copy Insert here using lngNumber to
' relate the copied tblOfferDetails records to the new offer
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: Friday, June 17, 2005 10:32 PM
> To: [email protected]
> Subject: [ms_access] Moving records between tables
>
>
> Hi to all!
>
> I have two sets of two tables. The first set is made of tables
> tblOrders and tblOrderDetails (almost as in Northwind sample
> database). The second set of made of tables tblOffers and
> tblOfferDetails. The 'Details' tables carry the items ordered and
> offered.
>
> I'd like to add an Offer record to tblOrders when the offer is
> accepted and so it is turned into an order. So far so good, as I could
> do that with an INSERT query in VBA. The problem arises when I want to
> add the corresponding items that where first offered and now ordered
> and originally been kept in table tblOfferDetails. How Access will
> know what is the number (in this case an autonumber) of this new Order
> in tblOrders so that the new Order will correlate to the right ordered
> items and insert them in table tblOrders?
>
> So in other words, I'd like to move a record from tblOffers to
> tblOffers and the corresponding records in tblOfferDetails to
> tblOffersDetails. The problem is that I cannot make the program know
> the value of the primary key (it's an autonumber) of the new record in
> tblOrders so to move the corresponding records from tblOfferDetails to
> tblOffersDetails.
>
> Thanks in advance,
>
> tyki9799
>
>
>
>
>
>
> 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/
