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.

 

[pbaldy]

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