Access frontend with a SQL Server backend

I have an MS Access frontend with a SQL Server backend. On several of my forms, I have a textbox bound to the primary key of the table. When the backend was solely in MS Access, the primary key would show up as soon as you start making the record. But this is not the case now that the backend is in SQL. The primary key value doesn't show up until you move off the record and comer back to it.

My problem is that I make use of the primary key value before the user gets off the record, so since it is NULL now - my code is crashing. Is there a solution to this.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jadedataMS Access Systems CreatorCommented:
Hey tyy8!

 You will have to modify your code somehow to work with this change in MO by the two different databases.
 Is there any other way to identify the record recently entered to return to IT specifically?
tyy8Author Commented:
I need to send the primary key into some other tables, so I need to set that up before the user leaves the record and goes to view those other tables.
jadedataMS Access Systems CreatorCommented:
Set up an SQL trigger on the table to Stick this number into the other table as soon as its available.  The Access code that does this now would then become obsolete because SQL Server would handle it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

tyy8Author Commented:
But I also need to send that users ID (for tracking the user's activity), and some other stuff that the trigger would never have available to it.
jadedataMS Access Systems CreatorCommented:
I guess I just don't understand all that is in play here...
if me.dirty then me.dirty=false

now the record is saved and you have your key

now you can do what you need to with it.

or as stated above

Create and AFTER INSERT trigger

--D0 you your thing using the new key @PK
This question has been abandoned and needs to be finalized.
 You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
  If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)


Cleanup Volunteer

jadedataMS Access Systems CreatorCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: jadedata {http:#9789733} & thorkyl {http:#9798989}

Please leave any comments here within the next seven days.

EE Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.