AutoID Populate

I have a form called “Scheduling-Add”. When the user wants to add a new “Study” that is not in the system they click the “Studies” button on the form which opens another form called “Studies-Add” . When the user is filling the details on this form for the new studies, the record is automatically populated with a “AutoID” number in the upper right field of the form next to the “Close” button. I want this new “AutoID” created by the system to be automatically populated in the “Protocol Nbr” field. Example if the new record about to be created has a new “AutoID” of 12355. This same ID “12355” should be populated in the “Protocol Nbr” field. How can I accomplish this goal. Attached is a copy of my database with the form. Thanks in advance.
Invoice-7-7-2001-91611.accdb
Chrisjack001Asked:
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.

LucasMS Dynamics DeveloperCommented:
I can't find the ProtocolNbr field so i'll assume it should be in the studies table.  

1.  If you're going to have the same number as primary key and this field name why not just make ProtocolNbr as the primary key?

2.  On the study-add after insert event, you can have this code:
Private Sub Form_AfterInsert()
'populate the ProtocolNbr with autonumber
Me.txtProtocolNbr = Me.StudyAutoNbr
End Sub

Each time the record is saved, the id is assigned as the primary key.

I've attached your db with changes.

Invoice-7-7-2001-91611.accdb
0
Chrisjack001Author Commented:
I have attached the database with the updated form. Sorry about that
Invoice-7-7-2001-91611.accdb
0
Chrisjack001Author Commented:
The StudyAutoNbr is the primary key. This was discussed in our meeting and we came to an agreement that this should be the primary key
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.

als315Commented:
If you like to edit this field ProtocolNBR (PIID Field?), add this code to after updat event for every requred field in your form (some name, date etc.):

If IsNull(Me.PIID) And IsNull(Me.Text30) = False Then Me.PIID = Me.Text30

Field will be filled only once and you can edit it.
0
Chrisjack001Author Commented:
I just want the new auto number generated to be automatically populated in the protocol number field. Will this step do that
0
Chrisjack001Author Commented:
I dont know if you dont understand my question. This is just about updating 1 field "ProtocolNbr" field with whatever number that is generated in the "studyautonbr" field when a new record is generated.
0
LucasMS Dynamics DeveloperCommented:
Rename that text field to txtProtocolNbr then:

Private Sub Form_Load()
Me.txtProtocolNbr = Me.StudyAutoNbr
End Sub
0

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
als315Commented:
Why you like to have 2 same fields in one table?
0
Chrisjack001Author Commented:
That is how my boss wants it done.
0
als315Commented:
May be you can show one field on a form twice? Use unbounded control and set it's value equal to ID
Invoice-7-7-2001-91611--1-.accdb
0
Chrisjack001Author Commented:
Thanks
0
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.

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.