jamoparisi
asked on
Get ID of Recently Insterted Record
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
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
How do you insert the record into the payment table? Manually, or via code?
ASKER
Via Code
on a textbox
Me.Textbox=DMax("pay_ID","
Can you post the code that you are using to add the record?
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
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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
>>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?
???
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?
???
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
jamoparisi, my comment above was directed to Emanon_Consulting, just in case there's any confusion :)
>>shanesuebsahakarn - I wasn't suggesting you were wrong - just inquiring about options...
Thanks for the note
;-D
Cheers
M
Thanks for the note
;-D
Cheers
M
I wasn't suggesting that you were suggesting I was wrong :-) Just explaining why I'd choose to do it this way, is all.
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
<grin>
I'm going there now...Catch you tomorrow! (or at least I'll try)
;-D
Cheers
M
ASKER
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.Col umn(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.combo Staff & "', "
strDiscount = strDiscount & tempPayID & ", '" & Me.txtNotes & "')"
Db.Execute (strDiscount)
Do you see any problems with this solution?
Thanks,
James
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.Col
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.combo
strDiscount = strDiscount & tempPayID & ", '" & Me.txtNotes & "')"
Db.Execute (strDiscount)
Do you see any problems with this solution?
Thanks,
James
Looks okay to me if it's working for you :) Can't think of any problems off the top of my head.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.