Need help with automatically added date (1 Viewer)

113hans

Member
Local time
Today, 16:19
Joined
Mar 31, 2021
Messages
47
Each of my records starts with a number, e.g. 21033185, 21 is the current year (2021), 03 is the current month (March), 31 is the current date (today) and 85 is the ordinal number of the buyer (the 85th buyer).
My problem is that I have to fill this serial number manually everyday. Therefore, I thought about making a command button which automatically adds the serial number based on the current time of PC system.
For example, when I get to work on 31/ 3/ 2021, I launch Access, click on that command button, and the button will create a new record with a serial number like 21033101. And when I finish with the first buyer of the day, I click that button again and it will generate the ID 21033102 for the next shopper, and go on till the last person.
In general, I'm finding a code for a new record button which can also automatically add a serial number regarding the current time of a workday.
I appreciate all your concerns.
 

bob fitz

AWF VIP
Local time
Today, 09:19
Joined
May 23, 2011
Messages
4,717
Each of my records starts with a number, e.g. 21033185, 21 is the current year (2021), 03 is the current month (March), 31 is the current date (today) and 85 is the ordinal number of the buyer (the 85th buyer).
My problem is that I have to fill this serial number manually everyday. Therefore, I thought about making a command button which automatically adds the serial number based on the current time of PC system.
For example, when I get to work on 31/ 3/ 2021, I launch Access, click on that command button, and the button will create a new record with a serial number like 21033101. And when I finish with the first buyer of the day, I click that button again and it will generate the ID 21033102 for the next shopper, and go on till the last person.
In general, I'm finding a code for a new record button which can also automatically add a serial number regarding the current time of a workday.
I appreciate all your concerns.
You could use something like:
Code:
=IIf(Left(DMax("FieldName", "TableName"), 8) = Format(Date, "yyyymmdd"), DMax("FieldName", "TableName") + 1, Format(Date, "yyyymmdd")) & "01"
You would need to substitute the "FieldName" and "TableName" with your own but it might be better to store the actual date and the record number seperately and then use an expression to concatente them
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:19
Joined
May 7, 2009
Messages
19,169
on the click event of your button:
Code:
private sub btn_click()
dim s as string
dim r as string
s=Format$(Date, "yymmdd")
r = nz(dmax("theSerialField", "yourTable", "theSerialField Like '" & s & "*'"), s & "00")
r=replace$(r, s, "")
[theSerialTextbox] = s & Format$(val(r) + 1, "00")
end sub
 

113hans

Member
Local time
Today, 16:19
Joined
Mar 31, 2021
Messages
47
Thanks guys but I encountered this error

Untitleda.png

Untitled.png
 

plog

Banishment Pending
Local time
Today, 04:19
Joined
May 11, 2011
Messages
11,611
Each of my records starts with a number, e.g. 21033185


Why? What's so magical about that string of characters? Does it serve an actual function that only that string of characters can achieve or is it window dressing?

Access has a great built in way to assign unique values to records--they are called autonumbers:

 

moke123

AWF VIP
Local time
Today, 05:19
Joined
Jan 11, 2013
Messages
3,849
I agree with Plog about window dressing.
I'm sure your storing the date of the transaction so you could always generate that sequence with Year(Date()) & Month(Date()) & Day(Date())
If keeping them in sequence ie. 1,2 3, 4, etc for the day, you could store date and time, as in now(), of the transction and sort by Date/Time.
 

Cronk

Registered User.
Local time
Today, 20:19
Joined
Jul 4, 2013
Messages
2,770
Typical case of a paper based system being upgraded trying to emulate the old indexing system. My father at one time was managing warehouse operations for a large oil refining company. A 2 inch 8 gauge round head screw had the index name SR20008.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:19
Joined
May 7, 2009
Messages
19,169
see this demo.
view the structure of Table1 (specially the "SerialNo" field).
open Table1 form.
view the code on the Click event of the "button".
 

Attachments

  • sampleSerial.accdb
    412 KB · Views: 548

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:19
Joined
Feb 19, 2002
Messages
42,970
I've long ago stopped trying to convince people not to do this. But if you are going to do it, the best way is:
1. Add an autonumber as your PK.
2. Use the autonumber pk for ALL relationships. The userCode is just data. It is NOT the primary key
3. Keep the date as a date field
4. Keep the sequence number as an Integer data type
5. Add a compound index (you will need to use the index dialog to do this) to create a unique index that includes BOTH the date field and the sequence number field.
6 To generate a new userCode, do the calculation in the Form's BeforeUpdate event. Do not do it in ANY other place. Make it the last line of code in the procedure so it is the last line of code to execute before the record gets saved. Unless you have a very busy application, this should be good enough to prevent duplicates but if you find yourself getting "duplicate" error messages, you will need to create a loop to generate the next sequencnce number. If you generate the code ANY OTHER PLACE you run a high risk of generating duplicates. Say user1 starts the process and generates his sequencenumber but takes a phone call. UserB starts the process and generates a sequence number and saves the record. He will generate the exact sequence number that UserA just generated but has not yet committed. UserA comes back. Saves the form and gets a duplicate error.
7. The code to generate the correct userCode =
Code:
If  Me.NewRecord = True Then
    Me.seqnum = Nz(dMax("seqnum", "yourtable", "yourDate =Date()) ,0) +1
    Me.userCode = Format(Me.yourdate, "yymmdd") & me.seqnum
End If
The Nz function care of the situation when there had not been a userCode generated for today yet. The dMax() will return null, the Nz() turns the null to a 0 and the final part of the expression adds 1 to the rexult so it will either be 0 +1 or highestpriorseq +1.
8. If you want the userCode to sort numerically, change the statement above to be
Me.userCode = Format(Me.yourdate, "yymmdd") & Format(me.seqnum,"0000")
This will produce a constant length of 4 for the sequence number. if 9999 isn't enough for a single day, use 5 zeros.
9. Although saving calculated fields is not recommended, if you NEVER, EVER update the two base fields -myDate and seqnum, you won't get in trouble and your form won't have to keep formatting the userCode to do searches. The user can just type in the value for searching.

Once you become more comfortable with Access, you'll realize the power of being able to search by anything you want eliminates the need for this kind of crutch and is probably more useful and you'll realize that you simply could have used a straight sequence number for your external code.

The code does not belong in the click event of any button or in any othercontrol event and you need the If statement because you certainly don't want to change the userCode for an existing record.
 
Last edited:

113hans

Member
Local time
Today, 16:19
Joined
Mar 31, 2021
Messages
47
see this demo.
view the structure of Table1 (specially the "SerialNo" field).
open Table1 form.
view the code on the Click event of the "button".
It works perfectly (y)
Thank for your kindness of the detailed instruction.
 

Isaac

Lifelong Learner
Local time
Today, 02:19
Joined
Mar 14, 2017
Messages
8,738
Your situation may well warrant a balanced approach. Both an Autonumber column type which should be the primary key, used in joins, relationships and the like (not known or visible to the end user or even the business partners). In addition, if the business wants window dressing because they like the look of something they want to call a 'key' that has embedded business meaning, then there is nothing wrong with giving them that - IN ADDITION to the real primary key, a topic on which it is not appropriate for them to have any input whatsoever.

Some basic help please | Access World Forums (access-programmers.co.uk)
 

113hans

Member
Local time
Today, 16:19
Joined
Mar 31, 2021
Messages
47
you're welcome :)
Hi, I have an issue with the search engine, but it isn’t big enough to create a new thread, so I place the question in the sample db, can you sometimes take a look at that, thank for the time.
 

Attachments

  • Search problem.accdb
    800 KB · Views: 544

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:19
Joined
May 7, 2009
Messages
19,169
try your search.
 

Attachments

  • Search problem.accdb
    616 KB · Views: 464

113hans

Member
Local time
Today, 16:19
Joined
Mar 31, 2021
Messages
47
Tried it, there are some errors.
I can't put ID in the combo box pointed by red arrow and when I tried harder, I get that error message.

Untitled.png



Untitledd.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:19
Joined
May 7, 2009
Messages
19,169
there is no need for Extra combobox.
see my Note on "NhaplieuBlue" form.
 

Attachments

  • Search problem.accdb
    620 KB · Views: 531

113hans

Member
Local time
Today, 16:19
Joined
Mar 31, 2021
Messages
47
there is no need for Extra combobox.
see my Note on "NhaplieuBlue" form.
I've just found out another method to do the search and intending to tell you that, but your timely help kept me staying the old way.
Thank for the superb support, I really appreciate it.
 

113hans

Member
Local time
Today, 16:19
Joined
Mar 31, 2021
Messages
47
there is no need for Extra combobox.
see my Note on "NhaplieuBlue" form.
I made the same changes to my db as you did, everything went well but one
In your file, when I select a record, it goes blue

Untitled.png


In my file, when I pick a record, it goes white. Actually, I mean I don't know it goes white or it just doesn't change the colour at all :unsure:

s.png


Did I miss something?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:19
Joined
May 7, 2009
Messages
19,169
it uses Conditional Format (Ribbon->Format->Conditional Formatting).
on the search form i modified, open in Design View, select all the textboxes on the Detail
section of the form.
Go to ribbon and see the Conditional Formatting.
 

Users who are viewing this thread

Top Bottom