Generate number increments by date in form

[Buns1976]

Hi Y'all,



We generate several invoices for products weekly. In my filtered form. In my code below I'm trying to get my invoice field to generate consecutive invoice numbers based on the first date
in the first record "BY DATE". In other words in the InvoiceNumber field in the first record we type 20191501 then the next record would return 20191502, etc. on the date of 2/15/19.
Then on the next date of 2/16/19, we would start over with 20191601, 20191602 etc. Everything in the code works until we change to the next date and can't see to get it correct? Very new to all of this
so some simple help would be appreciated.

Thanks,

Dim ID As LongID = DMax("ProductID", "ProductT")
DoCmd.GoToRecord , , acNewRec
ProductName = DLookup("ProductName", "ProductT", "ProductID=" & ID)
SalePrice = DLookup("SalePrice", "ProductT", "ProductID=" & ID)
DateEntered = DLookup("DateEntered", "ProductT", "ProductID=" & ID)
InvoiceNumber.SetFocus
If Me.NewRecord = True Then
        Me.Slot = Nz(DMax("InvoiceNumber", "ProductT"), 1) + 1
    End If

[kd2017]

I'm smelling a few different problems here...

But first, where are getting the date from? Is [By Date] a field name in the ProductT table? Should the date not just be the current date? What is the data type of the invoice number field?

[Buns1976]

On the date yes its a field in the table. Todays date would probably work just the same. Data type is number!

[June7]

What you show is a structure of yyyyddXX. That doesn't really make sense. Every month has a day 15.

[Buns1976]

June7 what exactly are you referring to? What doesn't make sense?

[June7]

in the first record we type 20191501 then the next record would return 20191502, etc. on the date of 2/15/19.
Then on the next date of 2/16/19, we would start over with 20191601, 20191602 etc.

2019 15 01 = year 2019, day 15, sequence 01
2019 15 02 = year 2019, day 15, sequence 02
2019 16 01 = year 2019, day 16, sequence 01
2019 16 02 = year 2019, day 16, sequence 02

I presume you already went through that sequence in January. So what would you expect to happen in following months?

[Buns1976]

June7 I understand what you're saying. Do you have a solution? The numbers are just numbers that we have used in the past in another program. I'm not opposed to something that would make sense.
As I mentioned in my first post, I'm very new to this application so trying to learn.

Thanks.

[June7]

Generating custom unique identifier is a common topic. Review https://www.accessforums.net/showthread.php?t=23329

[ArviLaanemets]

I use an ID like this in our ID device registry.

The PK for table tblDevices is DeviceID, which has format "CCYYYYMMDD000", where "CC" is device group and subgroup, "YYYYMMDD" is device registering date (the format for date part is selected to allow to order devices by age), and "000" is the number for device of this type on registering day, preceeded with "0"'s.

The form fDevices is source for subform sfDevices of fMain, where the user selects device type. Device group links main form and subform.

On fDevices are control txtRegDate (It has "Date()" as default value, and I included it into table instead having an unbound control for it - to make script easier.), and a button comRegisterDevice.

The Current event of fDevices sets visibility/enabled for controls on form, following rules:
1. txtRegDate is enabled, when txtDeviceID Is Null (it is a new record), and disabled otherwise;
2. comRegisterDevice is visible, when i txtDeviceID Is Null, and txtRegDate has some value (you need a Change event for txtRegDate to control this), and not visible otherwise;
3. All other controls are disabled, when txtDeviceID is Null, otherwise enabled/disabled on usual conditions.

BeforeUpdate event of fDevices controls, is the device registered whenever an attempt to move to another record is made. Whenever it finds txtDeviceID is Null, updating of record is canceled.

On form are 2 unbound always not visible controls, txtCalcDeviceID1 and txtCalcDeviceid2, with source as:

txtCalcDeviceID1=[txtParentDevGroup] & Format([txtRegDate],"yyyymmdd")
txtCalcDeviceID2=CInt(Nz(Right(DMax("DeviceID","tblDevices","LEFT(DeviceID,10)='" & [txtCalcDeviceID1] & "'"),3),0))

The OnClick event of button comRegisterDevice calculates DeviceID value for new record, writes it into txtDeviceID, saves the record, and resets visible/enabled properties for all controls in form. The new DeviceID is calculated as:

Private Sub comRegisterDevice_Click()
    Me.txtDeviceID = Me.txtCalcDeviceID1 & Format((Me.txtCalcDeviceID2 + 1), "000")
   ...