generate the next sequential number

[kelvin_9]

Hello All,
Previously, I use this code to start record my customer inquiry daily, and now I want to amend it and easier for analysis monthly records if my superior needs. However I get stuck on sequential number generation.

Sub Macro1()

Range("A1000").End(xlUp).Offset(1, 0).Select
Selection.Value = Selection.Offset(-1, 0).Value + 1

End Sub

I created a calendar in VBA at first, select the inquiry date when someone inquired, and secondly, I wonder can it be generate the next new sequential number automatic like below table.

Book1
	A		C
1	BASIC INFORMATION
2	DATE (MM-DD-YYYY)		CASE #
3	Apr-05-2020		1
4	Apr-05-2020		2
5	Apr-06-2020		1
6	Apr-06-2020		2
7	Apr-06-2020		3
8	Apr-06-2020		4
9	Apr-06-2020		5
10	Apr-06-2020		6
11	Apr-07-2020		1
12	Apr-07-2020		2
13	Apr-07-2020		3
14	Apr-07-2020		4
15	Apr-07-2020		5
16	Apr-07-2020		6
17	Apr-07-2020		7
18	Apr-07-2020		8
19	Apr-08-2020		1
20	Apr-08-2020		2
21	Apr-08-2020		3
22	Apr-08-2020		4
DATABASE

remarks:
case# in column C should be using 001, 002, 003, 004...... instead of single numbers 1, 2, 3, 4
(I curious why the outcome was single. I'm sorry)

Thanks All for the great guidance.
kelvin

 

[kelvin_9]

The users needs to supply the date at first. Just like they received email/call-in from guest, they start to open this VBA form and record it...
They have to select current date(e.g. APR 17), and case# text box will be provide a new case# for this case, so it can be easier for admin/guest if they need to follow up, just quoting this reference#.

Private Sub CMD_CREATE_Click()
    Dim nextrow As Range
    'error handler
    'On Error GoTo errHandler:
    'set the next row in the database
    Set nextrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

    'number of controls to loop through
    cNum = 22
    'add the data to the database
    For X = 1 To cNum
        nextrow = Me.Controls("Reg" & X).Value
        Set nextrow = nextrow.Offset(0, 1)
    Next
    'clear the controls
    For X = 1 To cNum
        Me.Controls("Reg" & X).Value = ""
    Next
    'sort the database
    'Sortit
    'error block
    On Error GoTo 0
    Exit Sub
End Sub

Here is my form template and code on "CREATE" button
Step 1: select current date
Step 2: source from a pull down menu(e.g. email, call-in...etc)
Step 3: the next sequential number populate
ps. please ignore the fourth text box "ws reference"

In contrast, I'm also planning to add "search" function in my template, so that when guest second call-in want a follow up, admin can easily locate it by that reference#(i.e. 2004-001, 2004-002... etc)

 

[Joe4]

The users needs to supply the date at first. Just like they received email/call-in from guest, they start to open this VBA form and record it...
They have to select current date(e.g. APR 17)

Would they ever be entering a date that is NOT the current date?
Would they ever be entering a date that is earlier than the latest date already in the table (for example, the latest currently in the table is APR 19 and they are selecing APR 17)?

What I am trying to figure out here is whether the data needs to be re-sorted when they make an entry, or will it always be in the correct order, so there is no need to sort?

 

[kelvin_9]

Both not.
Admin can just select the current date while they are recording a on-time cases so they go for current. Secondly, this form is planning to use on coming Jun 1 2020, if they need to locate any prior of this date

Would they ever be entering a date that is NOT the current date?
Would they ever be entering a date that is earlier than the latest date already in the table (for example, the latest currently in the table is APR 19 and they are selecing APR 17)?

What I am trying to figure out here is whether the data needs to be re-sorted when they make an entry, or will it always be in the correct order, so there is no need to sort?

, they simply open old workbook and ctrl F.

Just in case, another code for button/textboxes

Private Sub CMD_VIEW_Click()
    Sheet2.Select
End Sub
Private Sub CMD_EDIT_Click()

End Sub
Private Sub CMD_RESET_Click()
'clear the Reg controls
cNum = 22
    For X = 1 To cNum
        Me.Controls("Reg" & X).Value = ""
    Next
    'enable adding new staff
    Me.CMDCREATE.Enabled = True
    'enable adding new payroll number
    'Me.reg4.Enabled = True
    'clear the listbox
    'lstlookup.Clear
    'clear the textbox
    'Me.txtlookup.Value = ""
End Sub

Private Sub CMD_DELETE_Click()

End Sub
Private Sub CMD_CLOSE_Click()
    Unload Me
End Sub

Private Sub Frame2_Click()

End Sub

Private Sub Image1_Click()
Call CALENDAR.SelectedDate(Me.REG1)
End Sub

Private Sub Image2_Click()
Call CALENDAR.SelectedDate(Me.REG10)
End Sub

Private Sub Image3_Click()
Call CALENDAR.SelectedDate(Me.REG12)
End Sub

Private Sub REG1_Change()
If IsDate(Me.REG1.Text) Then
Me.REG1.Text = Format(Me.REG1.Text, "MMM-DD-YYYY")
End If
End Sub

Private Sub REG10_Change()
If IsDate(Me.REG10.Text) Then
Me.REG10.Text = Format(Me.REG10.Text, "MMM-DD-YYYY")
End If
End Sub

Private Sub REG12_Change()
If IsDate(Me.REG12.Text) Then
Me.REG12.Text = Format(Me.REG12.Text, "MMM-DD-YYYY")
End If
End Sub

Private Sub REG18_Change()

  Dim f As Range
  
  REG19 = ""
  REG20 = ""
  If REG18.Value = "" Then Exit Sub
  
  Set f = Sheet4.Range("A:A").Find(REG18, , xlValues, xlWhole)
  If Not f Is Nothing Then
    REG19 = f.Offset(, 1)
    REG20 = f.Offset(, 2)
  End If
  
Me.REG20.Text = Format(Me.REG20.Text, "#HK$##,##.00")

End Sub

 

[Joe4]

OK, here is basically what you want to do.

You will want to have code that finds the last record in your database (last cell in column A) with data. You can find that row number like this:
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

You will then want to grab the values in column A and column C, like this:
LastDate = Cells(LastRow,"A")
LastSeq = Cells(LastRow, "C").Value

Then, you will want to check to see if the "LastDate" is today, i.e.
If LastDate = Date() Then
and if it is, add one to LastSeq and populate the sequence number on your form with this,
otherwise, populate it with 1.

You will probably want to put all this code either in:
- the VBA code that is opening Form
- VBA code that automatically gets run once they populate the date on the Form

That should give you a good roadmap of what you need to do.

 

[kelvin_9]

Thank You Very Much, Joe.

 

[Joe4]

You are welcome.