Partially Populate Form Field With Portion of Another Field (1 Viewer)

JJFernandez

Registered User.
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
18
Hi all,

I am trying to automatically populate the first part of an Incident_ID field with the date of the incident.

The kicker is that the date is entered DD/MM/YYYY, and I'd like the ID to start with YYYYDDMM_ (reversed and no slashes with underscore added) and then have the user add a unique number and their initials to it (make update of the field required).

An example:

Incident Date: 15/08/2018
Incident ID: 20180815_1_JO

Any ideas are greatly appreciated!

Jeremy
 

plog

Banishment Pending
Local time
Today, 03:23
Joined
May 11, 2011
Messages
11,611
You got a lot going wrong with that idea. My favorite is:

then have the user add a unique number

How do you expect users to know which numbers are going to be unique? Better yet, have you met users? Put a big neon sign on the form telling them to enter a number, send an email every day for a week, stick posters all around the office declaring they must do this and I guarantee you that within 200 records those idiots will have input non-numeric values. Never trust or expect anything from a user.

More importantly, why? Why all of this? What purpose is it to serve? Why not just use Access's autonumbers assign a value that is guaranteed to be unique and be done with it?
 

JJFernandez

Registered User.
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
18
It seems that I should have included in the description that the user-added identifier refers to an identifier in an enforcement officer's (pre-numbered) incident entry book.
 

plog

Banishment Pending
Local time
Today, 03:23
Joined
May 11, 2011
Messages
11,611
You should probably add context to whatever it is you just posted. I have no idea what you are talking about.

And even if I did, my initial main question remains--why not use autonumbers?
 

JJFernandez

Registered User.
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
18
Can I ask if you have ideas on how to accomplish what I'm trying to do? I'm not sure the why I want to do it should matter this much.

I have an autonumber that is hidden from the user.

This Incident_ID is for internal use, it is meaningful.

An enforcement officer will have pre-numbered paper forms in the field that they will manually fill out. They will use these paper forms to populate the database. I'd like to enter the ID of the paper form as an addition to the date. That's all.

I hope this is clearer.
 

plog

Banishment Pending
Local time
Today, 03:23
Joined
May 11, 2011
Messages
11,611
In a database each discrete piece of data should be stored discretely. That means even though you see your Incident_ID as 1 piece of data, it is actually 3 pieces which requires 3 fields in a table. Do not store this all together.

That doesn't mean you can't display it all together. On reports or forms you can use built in functions (like Format() https://www.techonthenet.com/access/functions/date/format.php) to display exaclty what you want to show for Incident_ID.

Since you initially asked about forms, I would do this: set up the table to store this approrpiately--a field for the date, a field for the initials and a field for the reference number. The form would operate in the same manner--one input for each of these that the user can put data into. On the form you would also have a 4th area which would display the Incident_ID but not be editable by the userr. Instead it would update itself to format the data from the other 3 fields as you wanted.
 

JJFernandez

Registered User.
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
18
Thank you for your reply. That will likely cut down on potential entry errors.

The part that I'm getting stuck on is the reversing the date and removing the slashes.

If that's not possible, I suppose I could simply have separate input boxes for day, month, and year and then combine everything...
 

plog

Banishment Pending
Local time
Today, 03:23
Joined
May 11, 2011
Messages
11,611
Check out the link in my last post. A date is a date is a date. The format function will allow you to display the value of that date in a variety of manners. So let the user input it however they want, then use Format to display it like you want.
 

JJFernandez

Registered User.
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
18
That looks promising, I'll have a go at that.

Thanks for the assist.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:23
Joined
Sep 21, 2011
Messages
14,037
I have to ask.
What happens when you have two officers with the same initials?
 

JJFernandez

Registered User.
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
18
I guess we'll start using middle initials as well? The paper form ID number is always unique so the Incident_ID will never be duplicated.

Right now there are only 5 officers so we're good!
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:23
Joined
Sep 21, 2011
Messages
14,037
Well the initials you should be able to get from their login?
If all they have to enter is the date and incident number, the you are good to go.;)
 

JJFernandez

Registered User.
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
18
That's good thinking! I ended up just creating a combobox with a values list as there are so few possibilities.

Just as an update I ended up splitting up 3 separate boxes and concatenating them into a locked Incident_ID field in the AfterUpdate of the Initials field.

Private Sub Initials_AfterUpdate()

If Not IsNull(Me![Initials]) Then
Me![DOE_Incident_ID] = Format([Date_of_Response], "yyyymmdd") & "_" & [FieldBookID] & "_" & [Initials]
Me.Form.Refresh
End If

End Sub


Thanks for the assistance :D
 

Users who are viewing this thread

Top Bottom