Increment number for each new entry, reset number at first entry of each new month

[camerontaylor]

I am very very new to VBA, so I apologize if I am going about this completely wrong.

I need to create a form which can Create, Update, and Delete records from a table. Each record will have a title field, a description field, a user field, a start date field, an estimated end date field, an actual end date field, and a project status field. I am currently working on the Create portion, and am not worrying about the other ones.

What I need to do, is on the click of the create, I need to create a new record. I want to open a form, have a user fill some of the fields listed above, and then save it to the record. I then need to look at the data just entered, and extract the date it was created. I also need to determine which number entry it is in the month (i.e. first entry, second entry, etc.), and whether it was created in the same month as the last entry (if it was created in a new month from the last entry, then restart the monthly counter at 1)(it also can't just reset on the first day of the month, because there's no guarantee that an entry will be made on the first day of the month).

From the date, and the monthly counter, I need to create a specific project number, which has the format "EPYYMM-##" where the ## is the monthly count (eg. the third project created in May 2021 would be EP2105-03). We don't expect to have more than 99 new projects per month, so number of digits at the end isn't a concern.

I could really use some help in designing my VBA code for this. I have written one Sub so far (attached below), but it isn't working, so any help is greatly appreciated.

Option Compare Database


Private Sub createNewProject_Click()
incMonthNum
End Sub
'-------------------------------------------------------------------------
'This will increment the monthly count by 1 for each new project entry,
'and will reset the count to 1 if the new entry is created in a new month
'to the previous entry
'-------------------------------------------------------------------------
Private Sub incMonthNum()
Dim d As Database, r As Recordset, monthNum As Integer, lastMonth As Field
Set d = CurrentDb()
Set r = d.OpenRecordset("Project List")

Set lastMonth = DLast("r.Fields('Month')", "r")

If lastMonth = month(Date) Then
    Set monthNum = DLast("r.Fields('Month Count')", "r") + 1 'this is where im getting an error right now (says "Object Required")
Else
    Set monthNum = 1
End If

DoCmd.RunSQL ("INSERT INTO [Project List] ([Month Count],Month) VALUES (" & monthNum & Date & ")")

r.Close

End Sub

 

[The_Doc_Man]

1) With DLast, you cannot count on "Last" to mean what you think it means. Access is based on SET THEORY for which there is no inherent order in the records of a table. The "last" record in a table might have been originally written a week ago if any update occurred in it more recently.
2) What do you do when you have more than a year worth of data in the table? If you use only the month, you will start to "double dip" on last year's months and your numbering will be skewed by your historical data.
3) The syntax of a domain aggregate doesn't work the way you specified. The first item needs to be the field name and what you have is technically a function - r.Fields('field name') - that is not going to work so well. Nor is "r" in the second item, which has to be a named domain such as a table or stored query. In your case, "r" is a recordset.
4) I cannot tell whether you have a full date field in your "Project List" item but if you don't, you should - to take into account my comment #2
5) Your INSERT INTO syntax isn't going to do very well either.
5.1) The parentheses enclosing the SQL is going to confuse matters.
5.2) Your number of targeted fields in parentheses is TWO but the number of VALUES as shown is ONE - so that would be a mismatch too.
6) As a general suggestion, run your names together so that you will be able to save yourself some typing. Every table, query, field, or form that has a space in its name will forever require use of bracketing. Which is easier to type? [Project List] or ProjectList? [Month Count] or MonthCount?

IF you had a full date field in your ProjectList then you might derive your month number based on a query and a DCount.

Query1: SELECT some-entry-data, Format( EntryDate, "yyyymm" ) As YM From ProjectList ;

MonthNum = 1 + DCount( "*", "Query1", "[YM] = '" & Format( Now(), "yyyymm" ) & "'" )

The idea here is that with the date, you can generate a unique six-character string for year and month, then count every record that has that year/month combination. Note that in this case, if you ever DELETE a record from the current month in the ProjectList table, you mess up the count. There are ways around that. The point is that you have to do this in stages. First, define a way to do the count. Then count the records. If you have none, your MonthNum comes back as 1. If you have more than one, you get the next number in sequence for that month.

 

[plog]

7. Month is a reserved word and shouldn't be used as a field name.

8. Use an autonumber and avoid this mess. In the big picture this accomplishes nothing . Do people really need to be able to look at a project number and know its order within the month? Is that a bit of information that needs to be encoded? No. If the 3rd project of the month is assigned EP2105-2785 where the last 4 digits mean nothing it loses nothing over EP2015-03 which encodes that data. Set an autonumber ID, and append its value to the end of the EPYYMM.

9.

We don't expect to have more than 99 new projects per month...

This will never come back to bite you ever. Nor will you ever have to "insert" a project in a past month, nor create a project for a future month, nor move a project from 1 month to another.

 

[camerontaylor]

1) With DLast, you cannot count on "Last" to mean what you think it means. Access is based on SET THEORY for which there is no inherent order in the records of a table. The "last" record in a table might have been originally written a week ago if any update occurred in it more recently.
2) What do you do when you have more than a year worth of data in the table? If you use only the month, you will start to "double dip" on last year's months and your numbering will be skewed by your historical data.
3) The syntax of a domain aggregate doesn't work the way you specified. The first item needs to be the field name and what you have is technically a function - r.Fields('field name') - that is not going to work so well. Nor is "r" in the second item, which has to be a named domain such as a table or stored query. In your case, "r" is a recordset.
4) I cannot tell whether you have a full date field in your "Project List" item but if you don't, you should - to take into account my comment #2
5) Your INSERT INTO syntax isn't going to do very well either.
5.1) The parentheses enclosing the SQL is going to confuse matters.
5.2) Your number of targeted fields in parentheses is TWO but the number of VALUES as shown is ONE - so that would be a mismatch too.
6) As a general suggestion, run your names together so that you will be able to save yourself some typing. Every table, query, field, or form that has a space in its name will forever require use of bracketing. Which is easier to type? [Project List] or ProjectList? [Month Count] or MonthCount?

IF you had a full date field in your ProjectList then you might derive your month number based on a query and a DCount.

Query1: SELECT some-entry-data, Format( EntryDate, "yyyymm" ) As YM From ProjectList ;

MonthNum = 1 + DCount( "*", "Query1", "[YM] = '" & Format( Now(), "yyyymm" ) & "'" )

The idea here is that with the date, you can generate a unique six-character string for year and month, then count every record that has that year/month combination. Note that in this case, if you ever DELETE a record from the current month in the ProjectList table, you mess up the count. There are ways around that. The point is that you have to do this in stages. First, define a way to do the count. Then count the records. If you have none, your MonthNum comes back as 1. If you have more than one, you get the next number in sequence for that month.

Thanks for your detailed reply @The_Doc_Man
Very helpful and great advice, and thanks for your patience with my lack of VBA and SQL knowledge. I'll be sure to make another comment when I come across more problems. Have you got any recommendations for any "How to get started in VBA" type documents or books that would be useful to me?

 

[camerontaylor]

7. Month is a reserved word and shouldn't be used as a field name.

8. Use an autonumber and avoid this mess. In the big picture this accomplishes nothing . Do people really need to be able to look at a project number and know its order within the month? Is that a bit of information that needs to be encoded? No. If the 3rd project of the month is assigned EP2105-2785 where the last 4 digits mean nothing it loses nothing over EP2015-03 which encodes that data. Set an autonumber ID, and append its value to the end of the EPYYMM.

9.


This will never come back to bite you ever. Nor will you ever have to "insert" a project in a past month, nor create a project for a future month, nor move a project from 1 month to another.

With regards to the autonumber situation - as much as I would love to do this and make it more simple, I unfortunately don't make the rules for how the company wants the numbers formatted. I think the general gist is to have the project list be more organized, because with an autonumber, it would likely get much more difficult to have a grasp of how many projects were created in the month than a specific numbering system that resets. It's a great suggestion, but not practical for my particular application.

 

[plog]

Ugh. Pushing off poor decisions onto others is one thing, but making poor arguments you think support them is another. Especially when they support my position.

There is no way to look at any of your project numbers and know how many projects were created in a month. EP2104-17, with just that information tell me how many projects were created in April.

Further, with autonumbers you actually would have a better grasp of the monthly total. If I know EP2104-2855 was the last one issued in April and I just had EP2105-2897 assigned, I can get a good idea of how many May has.

However in either case, if that was a piece of information I cared about I would make a report to list Project Counts by Month or have the current total load up to the main menu of my database's form.

 

[The_Doc_Man]

I'll offer a different insight than plog's but not a different result. If there is a need to know how many projects occurred in a given month, my little trick with a Format() function plus an SQL aggregate would tell you that instantly. But in terms of projects starting with EP2105, does it REALLY matter whether the suffix is 001, 002, 003..., or something longer than that?

As to your question on VBA, there are books available out there but my advice on "which one is best" is automatically wrong (unless it happens to be right). Confused? I'll explain. Go to your techie bookstore and look at the books they have on VBA. If all they have is one title, your problem is solved. Get it. But if they have choices, skim through a middle chapter to see if you can understand what it says. The book that speaks to you best is the one you should get. Don't rely on my choice of titles because I might see things differently than you and might like a book for a reason that does not apply to you.

 

[Pat Hartman]

Regardless of what you use for the human facing ID, always use an autonumber internally. The autonumber is the PK and will be used as the FK for any related tables. The human ID is simply data and is only used for searching.

To ensure that your meaningful "key" is unique, add a unique index on the generated key. There are various opinions on whether to store the generated key or not. I lean toward storing it because in most cases, the individual components are not stored separately. For example, in this case the generated number is based on the date a project is created. Unless you just want to store yyyy and mm as the project create date, then you would have a date field that has too much information. CreateDT + seqNum won't be correct as the unique index because CreateDT includes a day. The other advantage to storing the generated ID is that it can be indexed, which in a large database will be important. Storing CreateDT plus separate fields for year and month would solve the unique index problem but duplicates data. If you're going to duplicate data, do it to make your work easier and just store the whole generated ID.

Make sure that you lock the human key field on forms. You do NOT want it to be accidentally updated - which is the real reason for not ever storing it.

 

[Minty]

I'll add my weight to the "Don't get stuck into a 'request' that is meaningless" to the actual functionality of the system.

I have seen this type of thing dozens (if not hundreds) of times before (have a search on here), and 99% of the time it's because "the boss likes the sound of it".
As others have said you can easily determine the quantity of an item by date in a database, and display it on the opening menu if it's THAT important.

50 years ago when these things were filed in a filing cabinet with labels on the top of the file, in paper form, this type of manually created numbering system made a degree of sense. In a digital age, they don't.

#Pet Peeve Rant Over#

 

[Pat Hartman]

I agree with Minty, this is generally a crutch but as long as you don't use it as the PK, I don't object since it makes users happy and is pretty easy to do.

 

[jdraw]

I unfortunately don't make the rules for how the company wants the numbers formatted.?????

You are dealing with some concocted alphanumeric code - actually numerics and space. It's meaningful to your boss by the sound of things, but autonumber is meaningful to the database software.
I'll go along with the others -as Pat says its a crutch-, but you should research normalization and 1 fact, 1 field approach. At some point, you may want to learn more about database concepts and some of the reserved words and best practices of Access.
Good luck with your project.

 

[arnelgp]

Private Sub createNewProject_Click()
Dim incr As Integer
Dim sProjNum As String
incr = incMonthNum

'here is you project number
sProjNum = Format$(Date, "\E\PYYMM-") & Format$(incr, "00")
'Me.ProjectNum = sProjNum
End Sub


'-------------------------------------------------------------------------
'This will increment the monthly count by 1 for each new project entry,
'and will reset the count to 1 if the new entry is created in a new month
'to the previous entry
'-------------------------------------------------------------------------
Private Function incMonthNum() As Integer

Dim dte As Date
Dim incr As Integer

dte = Date
incr = Nz(DMax("[month count]", "[project list]", "Format$([month], 'yyyymm') = '" & Format$(dte, "yyyymm") & "'"), 0) + 1
CurrentDb.Execute "insert into [project list] ( [month count], [month] ) " & _
                "select " & incr & ", " & Format$(dte, "\#mm\/dd\/yyyy\#") & ";"

End Function