Recordset.addnew and Linked Oracle table

  • Thread starter Thread starter Corey-g via AccessMonster.com
  • Start date Start date
C

Corey-g via AccessMonster.com

Hi All,

I feel like I'm so close, but somethings missing. I have an unbound data
entry form, and when I try to add in the info (using a recordset from the
linked table) everything seems to work - except getting back the sequence
genereated PK. I have tried this:

set rs = currentdb.openrecordset("myTable")
with rs
.addnew
!field = form_frmReq.myControl
.update
.bookmark = .lastmodified
intLastID = !ThePK
end with

But I get an error saying the record is deleted... Anyone able to offer any
assistance with something like this?

Thanks,

Corey
 
Hi Corey

put
intLastID = !ThePK
before
..update


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Hi Crystal,

I had tried this before, and I tried again because of your post, but this
doesn't work for me. I get an "Invalid use of Null" error. I believe that
this is caused because the trigger that creates the primary key value from
the sequence in Oracle hasn't fired yet (which happens during the '.update')

Thanks for your thoughts!!!

Corey
 
Hi Corey,

then how about adding a field that records the date/time a record was
created to your structure. Record the date/time at the top of your
procedure in a variable and then pull the record with a value >=
StartDateTime after you make the record and get its key value?

OR

if the primary key values are assigned sequentially, you could use dMax
before the operation to determine the max PK value, then use dMax after
the operation (compare both to ensure it is not the same -- once it
works ok, you can probably get rid of the first dMax). Domain aggregate
functions (such as dMax) are slow, so, depending on how big the table
is, you may want to try using a recordset to pull the max.

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top