Access 2010: ODBC Oracle Table with acNewRec - getting the triggered Primary Key

I have an ODBC linked table with a form that has an Add button.
The Table has an Oracle Trigger that generates a unique ID from a sequence IN ORACLE.
This is triggered before the update on the Oracle side.


When the ADD button is clicked, I issue a
"DoCmd.GoToRecord , , acNewRec"
I set a few hidden txt boxes that are bound to the ODBC table.
I then look for the Primary key. It is still null.

This means that the Sequence is not being triggered just yet.
How do I get the  record "forced" into Oracle and still allow my user to input values into the new record?

Or any other ideas?
GNOVAKAsked:
Who is Participating?
 
GNOVAKAuthor Commented:
Yep -
The solution was as I mentioned above. Lots of hoops to go through, but it works.
I just call the sub that does a SQL insert and sends back the ID.  Then I add a filter and life is good.
0
 
Kelvin SparksCommented:
The trigger will have fired. Unlike an Access backend, the text box isn't automatically populated (it will after a while depending on the ODBC refresh interval. I'd suggest that you use a Me.Refresh at the end of the VBA that adds the record. This is also common for ODBC links to SQL Server.

Kelvin
0
 
GNOVAKAuthor Commented:
The fields I have been populating are linked to the fields in the ODBC table but are hidden. These are in the detail section.  The header section has an "Add " button.

I have populated them after the "...acNewRec" line.
I then look for the me.txtRun_ID (key) and it is null.

Here's what I tried so far with no luck:
me.refresh - no effect
me.txtID.refresh - crazy, but no change
filling a visible field that is linked.
Relinking the table making sure it has a primary ID (RUN_ID)
None of the above works.

Since it is null, the code errors (I want it to at this point) and if I stop execution, the record appears in Oracle with an ID!

I'm open to ideas...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GNOVAKAuthor Commented:
I did find out that a SAVE macro button forces the save and refreshes the ID.
I would rather NOT use the save macro button and use VBA.

I suppose I could make it invisble but two questions on this -
How do I call it ?
Will it work if I deactivate menus when I compile the menu for users?

The other idea that may work is to create a function that does a SQL insert, filling a field with garbage, getting the id using a dlookup, deleting the garbage and returning the id.
Seems like a lot of unncessary work though.
0
 
Kelvin SparksCommented:
Ah, I see. The acnewrec moves you to a new record. With linked Oracle and SQL Server databases, the ID is not created until you save the record - so you have to enter data into some of the visible fields and save the record before the trigger will fire and create the ID.

You can do the save in VBA - but whatever you do, you cannot retrieve the ID until after the save as it does not exist until then (unlike a linked Access database that will show you the ID, the moment you start entering the data).

Kelvin
0
 
Kelvin SparksCommented:
No problems
0
 
GNOVAKAuthor Commented:
The only solution that worked
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.