UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Insert Issue    
 
   
jamborrs
post Jul 20 2007, 10:07 AM
Post #1

UtterAccess Addict
Posts: 131
From: Toledo, OH
Good Morning,
I have an unbound form which I collect data in and a command button that uses an INSERT statement to insert a record into tblSpecimens.
What I would like to do is after inserting it, also insert a record in two other tables related to the new specimen created. So basically, is there a way to return the autonumber that access creates for my new specimen so I can use that to insert it as the foreign key in the related tables? I thought about using the DMax function, which would work I think, but it is a mulitple user app. I know it would be virtually impossible for two users to insert at the exact same time, but I suppose in theory it is still possible. Any other suggestions?
Thanks,
Rich
Go to the top of the page
 
+
theDBguy
post Jul 20 2007, 10:15 AM
Post #2

Access Wiki and Forums Moderator
Posts: 48,021
From: SoCal, USA
That is a good question, and I'd be happy to find out the answer as well.

In the meantime, instead of inserting the new data, you could probably try creating a blank record first, search for it, then get the ID number, then update that record with the new data. Now, you can use the ID to insert data to the other two tables.

HTH.
Go to the top of the page
 
+
LPurvis
post Jul 20 2007, 10:42 AM
Post #3

UtterAccess Editor
Posts: 13,753
From: England (North East / South Yorks)
How are you doing the insert?
DAO or ADO?

Using SQL you'll need to grab the @@Identity value through DAO or ADO
e.g. DAO

CODE
    With CurrentDb
        .Execute "INSERT INTO tblEmployee (Employee) VALUES ('Donny Osmond')"
        With .OpenRecordset("SELECT @@Identity")
            Msgbox "New ID is " & .Fields(0)
            .Close
        End With
    End With
Go to the top of the page
 
+
theDBguy
post Jul 20 2007, 11:22 AM
Post #4

Access Wiki and Forums Moderator
Posts: 48,021
From: SoCal, USA
That's great! Thanks, Leigh.
Go to the top of the page
 
+
jamborrs
post Jul 20 2007, 12:37 PM
Post #5

UtterAccess Addict
Posts: 131
From: Toledo, OH
hmmm...I'm not sure I understand what is going on there in that code. Is there any way you could elaborate on that?
Go to the top of the page
 
+
LPurvis
post Jul 20 2007, 12:56 PM
Post #6

UtterAccess Editor
Posts: 13,753
From: England (North East / South Yorks)
Hi

Well - you haven't answered my questions to you yet, so it's hard for me to give a directly applicable example.
If you are performing your insert through VBA then if you're using DAO you'd open a DAO recordset after your execution of the Insert statement - and retrieve the @@Identity function value.
Similarly if your insert is using ADO to execute it you'd open an ADO recordset.

I just didn't use an explicit recordset object in the above example.
However you could have it more traditionally
CODE
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim lngNewID as Long

    Set db = CurrentDb
    db.Execute "INSERT INTO tblEmployee (Employee) VALUES ('Jemima Puddleduck')"
    Set rst = db.OpenRecordset("SELECT @@Identity")
    
    lngNewID = rst.Fields(0)
    Msgbox "New ID is " & lngNewID

    rst .Close
    Set rst = Nothing
    Set db = Nothing


You'd use the value in lngNewID to insert into related tables instead.
Go to the top of the page
 
+
jamborrs
post Jul 20 2007, 01:15 PM
Post #7

UtterAccess Addict
Posts: 131
From: Toledo, OH
OK...yes, I am performing the insert in VBA. Sorry, I am not very clear on ADO vs DAO. But I think I understand what is going on in the code anyway. Thanks for your help today, I really appreciate it.
Go to the top of the page
 
+
LPurvis
post Jul 20 2007, 02:01 PM
Post #8

UtterAccess Editor
Posts: 13,753
From: England (North East / South Yorks)
So does that make sense now then?
I didn't think to clarify the DAO/ADO concepts - but it's more likely you're using DAO (i.e code like the above).

It should hopefully be pretty much straight forward to implement from there. i.e. copy it and change to your inserts statements ;-)
Just shout out with any problems.

It's worth mentioning to any lurkers using Access 97 that the Identity method is only available for Jet4 db's.
(i.e. Access 2000 onwards). Earlier and that method will fail.
Including using a newer front end *linked* to data in a 97- format MDB.
Go to the top of the page
 
+
jamborrs
post Jul 23 2007, 08:53 AM
Post #9

UtterAccess Addict
Posts: 131
From: Toledo, OH
Just wanted to let you know that the code works great and I appreciate your help! Incredible.
Rich
Go to the top of the page
 
+
LPurvis
post Jul 23 2007, 10:12 AM
Post #10

UtterAccess Editor
Posts: 13,753
From: England (North East / South Yorks)
No worries - you're welcome.
Go to the top of the page
 
+
« Next Oldest · Access Forms · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 23rd May 2013 - 06:56 AM