Result from INSERT INTO

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I presently have a booking system which is Access 2000 DB and I want to add
some new functionality to allow the user to copy a booking to a new date for
repeat bookings. The problem I have is that after selecting the requested
record from a table and copying it back with a new date I now need to know
the auto number ID that has been assigned to this booking so that I can copy
the other linked tables that form this booking. The algorithm should be
something along the lines of

1) Select (field list) from event_table where event_id = selected_id;
2) Insert into event_table (values from above, except event_id which is an
auto number type, and new date);

Then for each of the linked tables.

3) Select (field list) from beverage_table where event_id = selected_id
4) Insert into beverage_table (values from above)

But how do I get the new event_id that would have been generated in step 1
so that the new entries in the second table link to the new booking and not
the old one?

Thanks
 
With access you will always have to do this in 2 steps

Either you open the the recordset with actual data and open a recordset with
an new record and then you itterate through the fields to copy the data from
the source record to the destination record.

Easier way is to add an extra column to your table where you can hold an
unique value (e.g. GUID). So what you do is generate an unique value before
inserting the record and make sure that you include the unique value with the
newly inserted record. Then you can query the table with this unique value
and so retrieve the event_id.
 
JaRa said:
With access you will always have to do this in 2 steps

Either you open the the recordset with actual data and open a
recordset with an new record and then you itterate through the fields
to copy the data from the source record to the destination record.

Easier way is to add an extra column to your table where you can hold
an unique value (e.g. GUID). So what you do is generate an unique
value before inserting the record and make sure that you include the
unique value with the newly inserted record. Then you can query the
table with this unique value and so retrieve the event_id.
Click to expand...

With the coming of Jet 4.0 there's another way to add to these. It's
only useful if you have a single-user database, or can be reasonably
confident that there won't be two users adding records at the same time.
That's probably enough to take it out of the running for a practical
multiuser application, but I thought it worth mentioning for
completeness.

The SQL SELECT @@IDENTITY statement will return the last autonumber
value that was created. So you can use an INSERT statement to add a
record, and then use SELECT @@IDENTITY to get the autonumber that was
generated for it -- *if* you can be sure that no one generated another
autonumber, in any table, between the first action and the second. The
code would look something like this:

'----- start of example code -----
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngKey As Long

Set db = CurrentDb

db.Execute "INSERT INTO Table1 (Field1, Field2) " & _
"VALUES ('ABCDE', 1)", _
dbFailOnError

Set rs = db.OpenRecordset("SELECT @@IDENTITY")
lngKey = rs(0)

rs.Close
Set rs = Nothing
Set db = Nothing

Debug.Print "You just added a record with autonumber "; lngKey
'----- end of example code -----

Too bad Jet isn't like SQL Server, with a SCOPE_IDENTITY() function and
multi-statement procedures.
 
How familar are you with VBA programing? If I use the term DAO, do you
know what I'm referring to? There is a way to do this using code,
however if you're not familar with VBA and DAO, you'll need an
introduction to both.
 
I have some VBA ADO experience, but I normally programme RT embedded C/C++
and more recently C# apps for a change. If you can give me an out line of
what to do I should be able to fill in the gaps from MSDN.

Thanks

David
 
Well since you have some experience that makes things a whole lot
easier. I can't speak as to the finer differences between DAO and ADO as
I never made the transition - something that I'm glad for. DAO is older
than ADO and should not be that difficult for you to pick up. The code
below is a Sub that I wrote that will basically do what you're looking
for and YES! it was written for a reservation database so adapting it
shouldn't be that difficult. It is all DAO. I've added comments to
identify whats happing where. Basically, there are two steps. The first
step copies the single record that represents the reservation. The
second step reaches out and grabs the guests on the first reservation
and then duplicates the records to attach the guests to the second
reservation.

Sub duplicateTransfer()

Dim sourceQDF As DAO.QueryDef
Dim sourceRS As DAO.Recordset
Dim targetRS As DAO.Recordset

'Grab the reservation to be copied, this could be streamlined by using
'Set sourceRS = CurrentDb.OpenRecordset([SQL STATEMENT]) where the
SQL Statement
'is to the effect of SELECT * FROM tblReservations WHERE
lngReservationNumber = 2384927
Set sourceQDF = CurrentDb.QueryDefs("qrySelectTransferByTransferId")
sourceQDF.Parameters(0) = [Forms]![frmReservations]![lngTransportID]
Set sourceRS = sourceQDF.OpenRecordset(dbOpenSnapshot, dbForwardOnly)

'Accesses the table to which we're going to add records
Set targetRS = CurrentDb.OpenRecordset("tblTransports")

'Add a new record
targetRS.AddNew

'Copy the fields as-is from the source to the target. This code is
actually all that you need to dup
'the record. The If...Then skips copying the value in the field
which contains the Autonumber. If we
'copied this information Access SHOULD throw an error since the
value already exists in the table.
'By not copying it, Access will function properly and increment the
field.
For i = 0 To sourceRS.Fields.Count - 1
If sourceRS.Fields(i).Name <> "lngTransportId" Then
targetRS.Fields(sourceRS.Fields(i).Name).Value =
sourceRS.Fields(i).Value
End If
Next i

'Some of the field values need to be overridden and not copied
as-is. These lines change the field
'values that were set above to the values that they should be
targetRS.Fields("txtOutlookEntryId") = Null
targetRS.Fields("txtOrigination") = sourceRS.Fields("txtDestination")
targetRS.Fields("txtDestination") = sourceRS.Fields("txtOrigination")
targetRS.Fields("intOriginationZone") =
sourceRS.Fields("intDestinationZone")
targetRS.Fields("intDestinationZone") =
sourceRS.Fields("intOriginationZone")
targetRS.Fields("lngOrgKey") = sourceRS.Fields("lngDestKey")
targetRS.Fields("lngDestKey") = sourceRS.Fields("lngOrgKey")
targetRS.Fields("dteLastModified") = Now()
targetRS.Fields("dblImportBatchNumber") =
getImportBatchNumberForReservation()

'Grab the new Autonumber field ***IMPORTANT***
targetTransportId = targetRS.Fields("lngTransportId")

'Save the record and close the objects. The record has been duplicated
targetRS.Update
targetRS.Close
sourceRS.Close

'Now, grab the guests attached to the Original reservation using a
query def
'Depening on how the tables are set-up, you could use a SQL
Statement here just like
'I outlined above. The following code performs the same basic
function as the section above.
Set sourceQDF =
CurrentDb.QueryDefs("qrySelectGuestsAttachedToTransfer")
sourceQDF.Parameters(0) = [Forms]![frmReservations]![lngTransportID]
Set sourceRS = sourceQDF.OpenRecordset(dbOpenSnapshot, dbForwardOnly)

Set targetRS = CurrentDb.OpenRecordset("tblTransferGuests")

'Loop through the recordset and copy SPECIFIC fields to the new
record. Given the structure of
'the table that we're working here, only three fields needed to be
copied. Otherwise I could use
'the same techinque that I used above to copy ALL of the fields
using a For...Next loop
While Not sourceRS.EOF
targetRS.AddNew
'This is where capturing the Autonumber of the new reservation
comes in to play and allows us to associate the new records with the new
reservation
targetRS.Fields("lngTransportID") = targetTransportId
targetRS.Fields("lngClientID") = sourceRS.Fields("lngClientId")
targetRS.Fields("ynPrimaryPassenger") =
sourceRS.Fields("ynPrimaryPassenger")
targetRS.Update
sourceRS.MoveNext
Wend
targetRS.Close
sourceRS.Close

Set targetRS = Nothing
Set sourceRS = Nothing
Set sourceQDF = Nothing

'DB specific code
DoCmd.Echo False
[Forms]![frmReservations].FilterOn = False
[Forms]![frmReservations].Filter = "[lngTransportId] = " &
targetTransportId
[Forms]![frmReservations].FilterOn = True
DoCmd.Echo True
Debug.Print "---COMPLETED---"

End Sub
 
The nice thing about using the code that I just posted is that it should
work nicely in a multiple user environment.
 
Back
Top