Need help with automatically added date

[113hans]

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]

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:

=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]

on the click event of your button:

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]

Thanks guys but I encountered this error

 

[plog]

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:

Add an AutoNumber field as a primary key - Microsoft Support

By making the primary key field an AutoNumber data type, you can ensure every value in the field is unique.

support.microsoft.com

 

[moke123]

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]

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]

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".

 

[113hans]

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
Thank for your kindness of the detailed instruction.

 

[arnelgp]

you're welcome

 

[Isaac]

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]

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.

 

[arnelgp]

try your search.

 

[113hans]

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.

 

[arnelgp]

there is no need for Extra combobox.
see my Note on "NhaplieuBlue" form.

 

[113hans]

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]

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

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

Did I miss something?

 

[arnelgp]

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.

 

[moke123]

Just for kix I added a different type of search to your form.
Its a "search as you type" with a listbox. Double click in the listbox to filter your calling form.

 

[113hans]

"Search as you type", never cross my mind, I tried it for purpose of diversifying my option on some day, but when I increased the column count to 4, it still only showed the content of Hoten field, what happened here?