• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

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
0
Chrisjack001
Asked:
Chrisjack001
  • 6
  • 3
  • 2
1 Solution
 
LucasCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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
 
LucasCommented:
Rename that text field to txtProtocolNbr then:

Private Sub Form_Load()
Me.txtProtocolNbr = Me.StudyAutoNbr
End Sub
0
 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now