Solved Create Manual Reference Number

[smtazulislam]

Hello,
I have a table like:
Data - DataType
VSID : PK
VSNumbers: Numbers
Name : Short Text
Date : Date/Time
Ref : Short Text

I want to create a Ref Number which Format is Like : Manual Insert "VS" / " [Name] field First 3 Digits " / "[Date] Format([Date],"yyyymmdd") " / "Day Start Record = 01"
E.G.
1) Result LIKE : VS/App/20210425/01
2) Result LIKE : VS/Org/20210425/02
3) Result LIKE : VS/Ban/20210425/03
Last Record Entry : 23:59:59

Then Next Day:
1) Result LIKE : VS/Pea/20210426/01
2) Result LIKE : VS/Ban/20210426/02
3) Result LIKE : VS/Bla/20210426/03

 

[MajP]

I hope you are not really name fields Name and Date. That would be a horrible choice.

I assume you know how to concatenate the rest and your question is how to get the counter.
I will rename your date time stamp for the example and pass a current time stamp in

Public Function GetDayCounter(dtmDate As Date) As Integer
  GetDayCounter = DCount("*", "tblOne", "int([VSdtmStamp]) = #" & Format(dtmDate, "MM,dd,yyyy" & "#")) + 1
End Function

Since your time stamp is date and time and you may (or may not) pass in a date or date time

 

[smtazulislam]

I hope you are not really name fields Name and Date. That would be a horrible choice.

Thank you so much for your reply. No Sir, Name Fields = Company Name.
My boss like to added First 3 Digit from each company.
I will try to your adviced and code. And give you reply as soon

 

[MajP]

Good, but make sure your Date field is not called date.

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

docs.microsoft.com

The only trick here is that you store a date time and you want to find how many for that date. When you want to search for how many records exist for that date but the values include time, then you have to convert the date and time to just the date in the search.

 

[smtazulislam]

I tried it like: But not working...

Public Function GetRef() As String
Dim StrText As String
Dim strDate As Date
Dim StrName As String


StrText = VS
StrName = Left$(Me.VSName, 3)
strDate = Format(Me.VSDate, "yyyymmdd")

Me.VSRef = (StrText & StrName & strDate) + GetDayCount
Debug.Print

End Function

Then Call it After_Event() in the VSRef field.

 

[MajP]

What you show makes no sense, and is not what I did. My functions needs to be passed a date or a date and time. That should not even compile if you used my function, since my function has a date argument. I would expect to see
+GetDayCounter(me.VSDate).

 

[MajP]

One more thing. You need to add code to ensure that VSRef is null before updating. You do not want to update after it has been entered. The VSRef field should be locked and/or not enabled. You do not want people to messed it up once entered.

I would call this function in the after update event of the VSName and VSDate.

The function should have and IF check

if not isNull(vsname) and not Isnull(vsDate) and isnull(VSRef) then
your code
end if

The isssue here is if you make a mistake on the name and date and want to change it. If you lock down VSRef to be un editable you cannot change it. So now you may want a button to allow the user to unlock and clear it, then update the date and name fields. This will provide some level of keeping mistakes being made.

 

[Pat Hartman]

Add a sequence number to the table. Then use dMax() to get the highest value for the rest of the string.

Me.SeqNum = Nz(dMax("SeqNum","yourtable", "Left(CustName,2) ='" & Left(Me.CustName,2) & " AND YourDate = Date()"), 0) +1

I've attached a sample database with two examples.

 

[smtazulislam]

Add a sequence number to the table. Then use dMax() to get the highest value for the rest of the string.

Me.SeqNum = Nz(dMax("SeqNum","yourtable", "Left(CustName,2) ='" & Left(Me.CustName,2) & " AND YourDate = Date()"), 0) +1

I've attached a sample database with two examples.

Thank you so much nice job ! Yes, I need like that.
I know Excel Sequence Number called this funsction, But Access also same name great !

Here have some change please...
I need to add a) add "Date" Fields b) Add day serial number Start "001" c) All part separated by "/ " LIKE : VS/20210425/app/001
Same days should have SN number starting from "001" then end number create Before day is time 11:59:59.
See my example posted #01.
Hope you're get me well what I want to explain you.

NOTE : Actually this reference number insert in the company letter head....

I apologize, I took your sample what I need and remove others.

 

[smtazulislam]

One more thing. You need to add code to ensure that VSRef is null before updating. You do not want to update after it has been entered. The VSRef field should be locked and/or not enabled. You do not want people to messed it up once entered.

I would call this function in the after update event of the VSName and VSDate.

The function should have and IF check

if not isNull(vsname) and not Isnull(vsDate) and isnull(VSRef) then
your code
end if

The isssue here is if you make a mistake on the name and date and want to change it. If you lock down VSRef to be un editable you cannot change it. So now you may want a button to allow the user to unlock and clear it, then update the date and name fields. This will provide some level of keeping mistakes being made.

Thank you. Yes, VSRef number is locked.
An unbound text in the form to call this code numbers then showed, But problem is the date, I dont understand how to insert next SN number when a day finished.Because I want to Starting day with new SN.

Thanks for your adviced.

 

[MajP]

Private Sub VSDate_AfterUpdate()
  UpdateRef
End Sub

Private Sub VSName_AfterUpdate()
  UpdateRef
End Sub

Public Function GetDayCounter(dtmDate As Date) As Integer
  GetDayCounter = DCount("*", "tblOne", "int([VSDate]) = #" & Format(dtmDate, "MM/dd/yyyy" & "#")) + 1
End Function


Public Sub UpdateRef()
   If Not IsNull(Me.VSDate) And Not IsNull(Me.VSName) And IsNull(Me.VSRef) Then
     VSRef = "VS/" & Format(VSDate, "yyyymmdd") & "/" & Left(VSName, 3) & "/" & Format(GetDayCounter(Me.VSDate), "000")
  End If
End Sub

I am assuming that since you enter a VSdate you do not want to default the value to today's date. Therefore you have to pass in the vsdate.

 

[smtazulislam]

Private Sub VSDate_AfterUpdate()
  UpdateRef
End Sub

Private Sub VSName_AfterUpdate()
  UpdateRef
End Sub

Public Function GetDayCounter(dtmDate As Date) As Integer
  GetDayCounter = DCount("*", "tblOne", "int([VSDate]) = #" & Format(dtmDate, "MM/dd/yyyy" & "#")) + 1
End Function


Public Sub UpdateRef()
  If Not IsNull(Me.VSDate) And Not IsNull(Me.VSName) Then
    VSRef = "VS-" & Left(VSName, 3) & "-" & Format(VSDate, "yyyymmdd") & "-" & GetDayCounter(Me.VSDate)
  End If
End Sub

I am assuming that since you enter a VSdate you do not want to default the value to today's date. Therefore you have to pass in the vsdate.

Thank you so much. I appreciated.
Its exactly what I wanting....many many thanks. Allah blessing you InShaaAllah..

 

[MajP]

I updated before you posted with the correct format for the string. Make sure to look at the update.
VSRef = "VS/" & Format(VSDate, "yyyymmdd") & "/" & Left(VSName, 3) & "/" & Format(GetDayCounter(Me.VSDate), "000"

 

[smtazulislam]

I updated before you posted with the correct format for the string. Make sure to look at the update.
VSRef = "VS/" & Format(VSDate, "yyyymmdd") & "/" & Left(VSName, 3) & "/" & Format(GetDayCounter(Me.VSDate), "000"

I already updated and tried it working...