custom identifiers in an ID field (table masks vs. code)

[neuroman9999]

can anyone tell me if what I've done in this code (on a DIRTY event behind a form) can be transformed into a field mask in an actual table? I don't see it.

If Me.NewRecord Then
    Dim strLastNum As String
    Dim strYear As String
    Dim strNewNum As String
    Dim strLastIdent As String
    Dim strNewIdent As String
    Dim strOldNum As String
        If DCount("KapilaraID", "tblKapilare") = 0 Then
            Me.KapilaraID = "C" & CStr(Year(Date)) & "-0001"
        Else
            'check to see if the last record in the parent table has a year that is different than today's year.
            'if it is, we need to reset the identifier's mask format
            strLastIdent = DLookup("KapilaraID", "qryMaxID")
            strLastNum = Right(strLastIdent, 4)
            strOldNum = IIf(Left(strLastNum, 3) = "000", Right(strLastNum, 1), _
                        IIf(Left(strLastNum, 2) = "00", Right(strLastNum, 2), _
                        IIf(Left(strLastNum, 1) = "0", Right(strLastNum, 3), _
                        Right(strLastNum, 4))))
            strNewNum = IIf(Len(strOldNum) = 1, IIf(strOldNum = "9", "10", CStr(CLng(strOldNum) + 1)), _
                        IIf(Len(strOldNum) = 2, IIf(strOldNum = "99", "100", CStr(CLng(strOldNum) + 1)), _
                        IIf(Len(strOldNum) = 3, IIf(strOldNum = "999", "1000", CStr(CLng(strOldNum) + 1)), _
                        CStr(CLng(strOldNum) + 1))))
            strNewNum = Format(strNewNum, "0000")
            strNewIdent = Left(strLastIdent, 6) & strNewNum
            strYear = Mid(strNewIdent, 2, 4)
            strNewIdent = IIf(strYear = CStr(Year(Date)), strNewIdent, Replace(strNewIdent, strYear, CStr(CLng(strYear) + 1)))
            strNewIdent = IIf(strYear = CStr(Year(Date)), strNewIdent, Replace(strNewIdent, Right(strNewIdent, 5), "-0001"))
            Me.KapilaraID = strNewIdent
        End If
End If

KapilaraID ends up being the following if it's the first record in the table:
C2020-0001

KapilaraID ends up being the following if it's the 10TH record in the table:
C2020-0010

and KapilaraID ends up being the following if it's the 100TH record in the table:
C2020-0100

and so on....until it reaches 9999.

 

[pbaldy]

That's a lot more complicated than it needs to be. I'd store the incrementing portion separately (as a number), at which you have a simple DMax() + 1 formula. You format it with the leading zeros when you present it to the user. Even if you stored it like this, all the testing of length is unnecessary. Convert to a number, add one, format it back to text with leading zeros.

I'm sorry, but I don't understand what you mean by "transformed into a field mask". I'd also caution against using the dirty event. You run the risk of 2 users getting the same value. Typically this is done in the before update event so the new number is retrieved and saved at basically the same time.

 

[neuroman9999]

you have a simple DMax() + 1 formula.

there is no DMAX function in my code Paul.

all the testing of length is unnecessary.

why?

I'm sorry, but I don't understand what you mean by "transformed into a field mask".

what I mean is using a field mask. do you know what a mask is? I assume so, right? it's a feature of access tables. see here:

Control data entry formats with input masks - Microsoft Support

Learn how to add input masks in Access to table fields, queries, and to form and report controls to help people entering data.

support.microsoft.com

I'd also caution against using the dirty event. You run the risk of 2 users getting the same value.

even if record locking is put in place? that doesn't seem right to me. if one user can be locked in, what does it matter what even is used?

 

[pbaldy]

I didn't say you had a DMax, I said "at which <point> you have...". Admittedly I forgot the "point".

I said why in the next sentence. Converty your value to a number, add 1, convert back. Note the change in data type:

intLastNum = Right(strLastIdent, 4)
strNewNum = Format(intLastNum + 1, "0000")

Your own quote shows the actual name is "input" mask, not "field" mask. That would let you type in a value in a certain way, I don't see how it could help you here. A default value could help you, but I don't know that it could handle the incrementing.

Record locking would prevent someone from editing the same record. You look up the last number:

strLastIdent = DLookup("KapilaraID", "qryMaxID")

Since you haven't saved this record with the "+1" yet, another user adding a new record will get the same number this user did when that code runs, add 1 and you have two people with the same number.

 

[pbaldy]

To clarify regarding the DMax, my point was that if the incrementing value was in a separate field, you could use a DMax and your code would be a lot simpler.

 

[neuroman9999]

so record locks don't apply to new record entries? it doesn't look like it, according to this:

Form.RecordLocks property (Access)

Office VBA reference topic

docs.microsoft.com

if this is the case, I may have the change the architecture of the code. although it may not be necessary. still not sure.

 

[neuroman9999]

To clarify regarding the DMax, my point was that if the incrementing value was in a separate field, you could use a DMax and your code would be a lot simpler.

I'm still not sure how DMAX plays into all of this? how is it related to splitting the data into 2 fields? DMAX's purpose would serve the same purpose as COUNT, which I am currently using. DCOUNT is being used now to detect an empty table.

 

[pbaldy]

A record lock could help if you saved this record immediately after incrementing the number. This field would need to be a key or indexed so as to prevent duplicates. Record locks can't help if it's not a record yet, which it isn't until you save it.

The "standard" use of DMax in this situation, which would work if you had two fields:

Nz(DMax("IncrementField", "TableName", "StaticField = 'C" & Year(Date()) & "'"), 0) + 1

which handles the empty table, no records for the current year yet, and the incrementing. No DCount, no max query, no DLookup on the max query, no If/Then block, etc.

 

[The_Doc_Man]

Paul is absolutely correct, but sometimes I have found that adding a different viewpoint helps better with understanding. I'm not trying to confuse the issue, but rather to just show it in a slightly different light so that you might see what is actually going on here.

Adam, the key point which I will emphasize for you is that new records don't exist - until they are saved for the first time and at that moment become "old" records.

Remember that the idea behind a bound form is that you have a recordset that is the basis for all of the bound fields of the form. A form navigation action (First/Next/Previous/Last) reads from the recordset and copies corresponding values to the bound fields on that form.

This copying of fields from recordset to form is the defining moment of the CURRENT event. If you have implemented a Form_Current routine to act on the CURRENT event, it fires immediately after the bound fields are updated following the presumed navigation action and before your mouse cursor becomes active again. Note that whether or not you have a Form_Current routine, the CURRENT event fires for bound forms that experience a navigation action.

However, when you create a new record, you have a slightly different CURRENT event because there is no underlying record. Access creates a new record virtually by applying defaults for all fields (some of which are empty, 0, or null) and then implements the CURRENT event on that record. So, as Paul has indicated, record locking is still pointless because the data didn't originate from the recordset and we are talking about RECORD locking, not form data locking.

 

[neuroman9999]

Adam, the key point which I will emphasize for you is that new records don't exist - until they are saved for the first time and at that moment become "old" records.

very useful. will remember it.

Remember that the idea behind a bound form is that you have a recordset that is the basis for all of the bound fields of the form. A form navigation action (First/Next/Previous/Last) reads from the recordset and copies corresponding values to the bound fields on that form.

This copying of fields from recordset to form is the defining moment of the CURRENT event. If you have implemented a Form_Current routine to act on the CURRENT event, it fires immediately after the bound fields are updated following the presumed navigation action and before your mouse cursor becomes active again. Note that whether or not you have a Form_Current routine, the CURRENT event fires for bound forms that experience a navigation action.

all of that makes me wonder if me and my current employer implemented the solution in the wrong way. but rather, implemented it in sort of a "work-a-around" method that isn't necessarily programmatically correct, simply due to the fact that the employer wanted it ""this way only"", and thus I did what they wanted. do you want to see the entire package? I can upload 4 forms only. 4 forms are involved in this issue.

the data didn't originate from the recordset and we are talking about RECORD locking, not form data locking.

IS THERE such a thing a form data locking? never heard of it.

 

[Pat Hartman]

When you generate a custom ID like this one, the best place to put the code is as the very last statement in the FORM's BeforeUpdate event. You want to generate the sequence number as close to the point of the record save as possible. With this particular scenario, you are not likely to have a conflict because this ID seems to be tied to an individual. For wider scopes, you could have multiple people entering a new record at approximately the same time. If you generate the number in the dirty event, you are generating it as soon as the user enters the first character. What if he takes a phone call or goes to lunch? Surely someone would create a new record in that time span and would have generated the same sequence number and then saved the record. So, when our slowpoke comes back to what he was doing and completes the entry and saves the record, he is going to get an error message because the ID is a duplicate.

So, Generate at the last possible moment AND if your scope is such that others could be generating an ID in the same scope at the same time, add an error trap that identifies the duplicate and tries again. I would limit the try cycle to 3-4 times so you don't end up with an infinite loop.

And as the others have mentioned, your code is significantly more complex than it needs to be. I've attached a sample database that generates a customID so you can see how it is done. The sample generates the sequence number early so the user can follow the logic. In reality, it should be done last in the form's BeforeUpdate event.

 

[pbaldy]

Use of the before update event was recommended in post 2.

 

[Pat Hartman]

I see that now