Need help to use DMax to generate next number in a sequence

[veharness]

NOTE: I'm an architectural historian. Coding and Access are not in my traditional wheelhouse. Therefore, my base of knowledge is very small. Your patience is appreciated.

I am trying to get a form to generate the next ID number (NOT an autonumber, this is not a primary key field), with mostly failed results. I have had limited success in a new test database with a few sample entries using this code in the BeforeInsert event of the form:

CounterID = Nz(DMax("CounterID", "Table1")) + 1

This worked! For a day. However, today, when I closed the database and reopened it, the form would no longer generate the next number in the sequence for a new record. I'm baffled.

This is only the tip of the iceberg, as what I am really trying to do is get this code to work in a large, already existing database where for many years the identification number for the records has been entered manually. These records are for determinations of eligibility (DoE) related to specific properties. I split the table into two, properties and DoE, and made a new property form with a subform for determinations (one property may have multiple determinations). I added a Property ID, which is the primary key, tying each property to its determinations. The problem is, in the form you can't see what the previous DoE ID number is. I wanted to use the DMax function to solve this problem, but could not get it to work, which is why I went to trying it in a new, test database with only a few entries. Per the above, the results there were initially promising, but have since become mixed. I had thought for a while that my naming conventions in the existing database were a problem, and changed them to eliminate spaces and potentially problematic name duplication, but that didn't solve anything and the difficulties I've had with the test database suggests there is a different (or at least, an additional) problem.

[ranman256]

see this is why you should use Autonum. NO programming, No counting.

[veharness]

I already have a different field in the same table that uses autonumber and is my primary key linking my properties and determinations. It is my understanding that I can't use autonumber twice in the same table.

[aytee111]

Where is that line of code?

[ssanfu]

It is my understanding that I can't use autonumber twice in the same table.

Correct.....

Not sure what the rest of your code looks like, or even if you have code. Is "CounterID" a control on a form? (you have CounterID = .....)


I threw this dB together. Is it close to what you are looking for? (enter a couple of names)

[Bulzie]

Tested and your code below and worked for me. Check the datatype of CounterID, is it Number/Interger?
CounterID = Nz(DMax("CounterID", "Table1")) + 1

[veharness]

UPDATE: Okay, my test database problem is resolved (some content was disabled and when I enabled it, everything started working again. Sorry guys...warned you I am in way over my head). I did discover that in my test database that when I tried to change the name of the field (for instance, from CounterID to DoEID), my ID generated from the DMax stopped working again (yes, I also changed it in the code builder). I made yet another new test database and gave that field the name DoE_ID from the get-go, and it worked fine. I then made another test database and tried DoE ID (the original name of the field in the existing database that I ultimately want to apply this to) and it DID NOT work. I assume because of the space. I didn't realize the space would be such a big problem when I named the table in the original database.

To answer some questions above:

1) I am going to design view in the form, properties, picking form from the drop down, then going to BeforeInsert and placing the code in the code builder there. This has worked in the test databases (except when the field was renamed or had a space). This is the full extent of the code I can see (as taken from one of the working test databases):

 Private Sub Form_BeforeInsert(Cancel As Integer)

DoE_ID = Nz(DMax("DoE_ID", "Table1")) + 1


End Sub

2) I don't know what a control is. The database you sent is sort of what I'm looking for. I've attached screenshots of the test database and the real one for reference.

I guess my question now is, is there a way to apply this code to a database with objects that were not named appropriately to be compatible with code? Especially since renaming the fields seems to be a no-go?

P.S. Realized that the Property ID is the primary key in my Property table, but not the DoE table. HOWEVER, there are over 9,000 existing entries in the DoE ID field that need to be retained, which I can't do if I use autonumber.

[ssanfu]

I made yet another new test database and gave that field the name DoE_ID from the get-go, and it worked fine. I then made another test database and tried DoE ID (the original name of the field in the existing database that I ultimately want to apply this to) and it DID NOT work. I assume because of the space. I didn't realize the space would be such a big problem when I named the table in the original database.

Yes, it was probably due to the space. Part of the naming convention I follow is: Object names in Access are only letters, numbers and the underscore. NO spaces, punctuation or special characters. Object names do not begin with a number. Object names are fields, controls, tables, forms, queries, reports and modules.

I guess my question now is, is there a way to apply this code to a database with objects that were not named appropriately to be compatible with code? Especially since renaming the fields seems to be a no-go?

While an object name should not have a space in it, if you DO have one or more spaces, that name must be delimited with brackets. For example, "DoE ID" (with a space) could be used if you use delimiting brackets :

Private Sub Form_BeforeInsert(Cancel As Integer)
     Me.[DoE ID] = Nz(DMax("[DoE ID]", "Table1")) + 1  ' the "ME." refers to the form name.
End Sub

You have a field name in a table that is "SHPO #". A space and the hash mark "#", a date delimiter. ("#" is a special character...)
You have a field name in a table that is "Report Title/key words". The field name has spaces and the slash (a special character...).

2) I don't know what a control is.

A control is anything you put on a form. In the last attached picture, look at the ribbon. See the grouping "CONTROLS"? You click on a control, then click in the form, and the control you selected (clicked on) in the ribbon is placed on the form. A text box is a control. The buttons are controls. The check boxes are controls.......


Back to the code. My code was a little different because I used a table to store the max number in a field. Since you are looking at the DoE_ID field in table DoE, here is an updated version of the code:
(No spaces)

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.DoE_ID) Then
        Me.DoE_ID = Nz(DMax("DoE_ID", "DoE"), 0) + 1
    End If
End Sub

(With a space in the name)

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.[DoE ID]) Then
       Me.[DoE ID] = Nz(DMax("[DoE ID]", "DoE"), 0) + 1   'if you have spaces/ special characters in the field name
    End If
End Sub

Note the line "If IsNull(Me.DoE_ID) Then" .
This check is to see if there is already an entry in the field for the current record. If the field is NULL, then enter a number, else don't change the entry.
This is necessary because if you edited the record, the before update code would execute and change the current number to the max+1. Not what you want, I'm thinking...


Note: Access names controls the same name as the field it is bound to. It is better to take the time to rename the control to a different name.
A text box bound to the field "DoE ID" would initially be named "DoE ID" by Access. I might rename it "tbDoE_ID". (tb = text box and the space replaced with the underscore)
This gets rid of the space and isn't the same as the field it is bound to. Less confusing...... and Access does/can get confused!

[orange]

From a brief scan of your attachments:
You should not have embedded spaces in field or object names. Also the use of special characters in names (#@% etc) is
frowned upon. These will cause you syntax errors sooner or later.

Do you have a 5-10 line description of what the business is that this database is intended to support? That would be helpful to you and to readers.
Good luck.

[Micron]

I already have a different field in the same table that uses autonumber and is my primary key linking my properties and determinations. It is my understanding that I can't use autonumber twice in the same table.

IMHO, it's generally accepted that autonumbers should never be used as meaningful data. Access can even create duplicate or negative autonumbers, and if the field doesn't allow dupes, data can be lost when trying to save such a record. Here are several points about autonumbers, many of which apply to your situation and I present this http://www.utteraccess.com/wiki/index.php/Autonumbers to support my statement.

With regards to generating a sequential number during record creation, IF gaps are going to be bad, it is my belief that creation should not happen until there is at least enough data to support a new record. The number generation takes place at the moment the record is created and the form field that holds that value should become locked.

[veharness]

Do you have a 5-10 line description of what the business is that this database is intended to support? That would be helpful to you and to readers.
Good luck.

This is for the State Historic Preservation Office. We're a state government agency. Part of our function is to determine what properties are eligible (or not) for the National Register of Historic Places, and this database has existed in a simple table form for several years as a record of those determinations. We are trying to revamp it to make it more useful, primarily by creating a one-to-many relationship between properties and determinations about those properties (properties may have many determinations of eligibility over time).

[veharness]

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.[DoE ID]) Then
       Me.[DoE ID] = Nz(DMax("[DoE ID]", "DoE"), 0) + 1   'if you have spaces/ special characters in the field name
    End If
End Sub

THIS WORKED! Thank you, thank you, thank you! You have honestly made my day.

[ssanfu]

Happy it worked for you.