Solved

Get ID of Recently Insterted Record

Posted on 2004-08-31
18
219 Views
Last Modified: 2012-06-21
Hi,

I have a table called 'payment' which has it's own ID pay_ID. I also have a table called 'payment_cash' which also holds the pay_ID and referential integrity is enforced with the 'payment' table. I insert a record into the payment table, and then I need to insert a record into the 'payment_cash' table. I need to get the pay_ID (which is an automunber) of the record i just inserted into the 'payment' table so that i can use it to insert into the 'payment_cash' table.

Hope that made sense

Thanks,

James
0
Comment
Question by:jamoparisi
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11947436
How do you insert the record into the payment table? Manually, or via code?
0
 

Author Comment

by:jamoparisi
ID: 11947570
Via Code
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11947587

       on a textbox
Me.Textbox=DMax("pay_ID","Payment")
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11947590
Can you post the code that you are using to add the record?
0
 
LVL 5

Expert Comment

by:Emanon_Consulting
ID: 11947594
So if your table structure is like this...

tblpayment
pay_ID----1---              tblpayment_cash
                   |              Payment_cashID
                    _____oo_Pay_ID

You only need to create two forms - one for each table.

frmpayment  would be the Parent Form
sfrmpayment_cash  would be the Child Form

you could make sfrmpayment_cash a subform on frmpayment.
then you would link master and child records with the pay_ID field.
This would create the link/relationship

You can also use a Tab control on your form to keep things pretty if need be.


Is this what you were looking for?

Cheers
M
0
 

Author Comment

by:jamoparisi
ID: 11947661
capricorn - this db is a multiple user - i know the odds are slim that some one else would get in there, but i was hoping there was another way

The code i'm using to insert the record is nothing special -I've just created a recordset and done the INSERTs by creating SQL strings.

The way the data is setup makes it difficult to use two forms for the insert. Is there someway to get back the record that was inserted? So way of tracking how the insert affected the table?

Thanks
0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 80 total points
ID: 11947698
Hmm - if you're using an INSERT, it's going to be difficult to do. However, you can do it this way:

Dim rst As DAO.Recordset
Dim lngID As Long

Set rst = CurrentDb.OpenRecordset("MyTable")
rst.AddNew
....blah blah...
rst.Update
rst.Bookmark=rst.LastModified     '<------- this line navigates to the record that has just been added
lngID=rst!ID

0
 

Author Comment

by:jamoparisi
ID: 11947726
looks like it will work, thanks

I still have the concern that someone else could modify the table and i'll grab the wrong ID. If this is the only way to do it - I guess I'm stuck
0
 
LVL 5

Expert Comment

by:Emanon_Consulting
ID: 11947764
>>shanesuebsahakarn
RE: Is there someway to get back the record that was inserted? So way of tracking how the insert affected the table?

Could you not store the last ID in a global variable for use on the next update?
Do you update/insert using the ID from the global variable...
then once the update was successful get rid of the variable being stored and send a message that it was successful?

???


0
 

Author Comment

by:jamoparisi
ID: 11947789
yes i definitely could - i know it's probably crazy, but I don't like the idea that someone else could enter a record and screw up the last ID

Can a single user lock and then unlock the table? - just to ensure that two people can't edit the table at the same time
0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 80 total points
ID: 11947820
Yes, you could do it that way but I'd only do that under circumstances where the ID needs to mean something. I *always* use surrogate keys, so the ID has no meaning other than being unique - so if I need to know the ID of the inserted record, this is the technique I use as it's the fastest (and in my experience) the most reliable method in a multiuser environment.

Even with storing the next/last ID, you still have potential issues when multiple users are trying to insert records, plus the additional overhead of manually keeping the ID up to date. You'd still have to open the recordset to retrieve the ID value and hold that recordset opened and locked so that other users don't update it while you're adding your record and doing stuff with it. With this method, you're leaving the absolute minimum amount of time between the record being added and the ID being retrieved. I've never seen any record conflicts personally.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11947890
jamoparisi, my comment above was directed to Emanon_Consulting, just in case there's any confusion :)
0
 
LVL 5

Expert Comment

by:Emanon_Consulting
ID: 11948986
>>shanesuebsahakarn - I wasn't suggesting you were wrong - just inquiring about options...
Thanks for the note
;-D
Cheers
M
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11948996
I wasn't suggesting that you were suggesting I was wrong :-) Just explaining why I'd choose to do it this way, is all.
0
 
LVL 5

Expert Comment

by:Emanon_Consulting
ID: 11949092
Didn't I read somewhere that you sleep???
<grin>
I'm going there now...Catch you tomorrow! (or at least I'll try)
;-D
Cheers
M
0
 

Author Comment

by:jamoparisi
ID: 11963663
Hey guys I came up with this solution - let me know what you think

Dim Db As Database
Dim recPay As Recordset

Dim strDiscount As String
Dim strAmount As String

Dim tempPayID As Integer

Set Db = CurrentDb


Set recPay = Db.OpenRecordset("payment")

'the function AddNew created a new record with the default value and stores it in a buffer, it does not save it to disk until the function update is called

recPay.AddNew

tempPayID = recPay("pay_ID")

recPay("personal_ID") = Forms!frm_main.personal_ID
recPay("reg_ID") = Forms!payMain.comboReg.Column(2)
recPay("status") = "Completed"
recPay("payment_type") = "Discount"

recPay.Update

strDiscount = "INSERT INTO pay_discount (type, amount, staff, pay_ID, notes) VALUES ('"
strDiscount = strDiscount & Me.txtType & "', " & Me.txtAmount & ", '" & Forms!payNewDiscount.comboStaff & "', "
strDiscount = strDiscount & tempPayID & ", '" & Me.txtNotes & "')"


Db.Execute (strDiscount)


Do you see any problems with this solution?

Thanks,

James
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11963791
Looks okay to me if it's working for you :) Can't think of any problems off the top of my head.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 20 total points
ID: 11964134
i would suggest of adding dbfailonerror option

 Db.Execute (strDiscount), dbFailOnError    'Rolls back updates if an error occurs
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question