Using DMax to assign a unique request number in a subform

[wilhelmmt]

My database tracks submission requests from customers. I have a parent form (frmSubmitterLibrary) and a subform (frmSubmissionRequest) which is based on a table (SubmissionTable). The ClientID links the two forms. I would like the "MyCounter" field in the SubmissionTable to sequentially assign a number to each submission that is specific to the customer. EX: CustomerA submits 3 requests; CustomerB submits 2 requests; and CustomerC submits 5 requests all on the same day at different times. I would like the SubID to look like: CA-1, CA-2, CA-3, CB-1, CB-2, CC-1, CC-2...CC-5.

Following is the code that I am using:

Private Sub Form_BeforeInsert(Cancel As Integer)
    MyCounter = Nz(DMax("MyCounter", "SubmissionTable", "ClientID=" & Forms!frmSubmitterLibrary!tbxClientID)) + 1
End Sub

I am missing some crucial code, but I can't figure out on my own. Any help would be very much appreciated.

[nico5038]

Did you try Me.MyCounter as I assume the field is named MyCounter and resides on your form with that name.

Nic;o)

[NeoPa]

@wilhelmmt
Indeed, and some logic, but let's see if we can't fill in the gaps a little. BTW, the concept is absolutely the right way to go about this problem.

Let me first say that it's generally a good idea to use a single term for an item when talking technically. Customer and Client are synonymous I assume. I'm working on that basis.

The result of your DMax() function call, when not Null, will be a string value including both the Client identification prefix, as well as the numerical part. You are currently treating this as a purely numerical value it seems. You need to separate and extract the numerical part as well as the Client prefix (Mid()), Increment the numerical value, Add it back to the same Client prefix.

[wilhelmmt]

NeoPa,
Thank you for pointing out the need to use a single term, and yes Customer and Client are synonymous.
I am having difficulty using the Clidnt prefix (Mid()) that you suggested. Could you please show me how it would be used in code?
Thanks again for your help.
Maura

[NeoPa]

Assuming that the form the code is in, is actually called frmSubmitterLibrary, that tbxClientID contains the client code without the "-" and that each Client code is 2 characters exactly then :

Private Sub Form_BeforeInsert(Cancel As Integer)
    MyCounter = Me.tbxclientID & "-" &  _
                Val(Mid(Nz(DMax("MyCounter", _ 
                                "SubmissionTable", _
                                "ClientID='" & Me.tbxClientID) & "-*'", _
                           "XX-0"), 4, 9)) + 1
End Sub

[wilhelmmt]

The code is actually in a subform called frmSubmissionRequest. The parent form is called frmSubmitterLibrary.

The textbox tbxClientID is located on the subform, with the Control Source of =DLookUp("[ClientID]","SubmitterLib","[ClientID] = Forms![frmSubmitterLibrary]![ClientID]").

Each Client code is exactly 3 characters, not 2, and each Client on the Parent form will have multiple submissions entered on the subform.

Does this change the code you offered?
Thanks again for your help.

[NeoPa]

@wilhelmmt
That makes your original code a little weird, but that's probably a simple misunderstanding I guess.
@wilhelmmt
This needs some further explanation. Probably some examples that match what you are saying would help. Of tbxClientID values as well as the MyCounter values please. An explanation of why the parent form seems to be dealing with numeric ClientIDs while the subform, and your explanations so far, deal only in strings.
@wilhelmmt
Yes, but not fundamentally. Unless the whole ClientID thing is stranger than I anticipate. I await your response to know fully.

[wilhelmmt]

Thanks so much for your patience.

The ClientID is simply 3 characters representing the client's full name, ex: AEC would be the ClientID for "Ann E. Customer" and MTW would be the ClientID for "Maura T. Wilhelm".

MyCounter is the number of submissions a specific ClientID has made to the database. Ex: AEC has made 2 submissions thus far, and is about to make a third. MTW has made 4 submissions thus far, and will make a 5th.
The SubmissionTable reads as follows:

SubID  ClientID  MyCounter
AEC-1     AEC        1
MTW-1     MTW        1
MTW-2     MTW        2
AEC-2     AEC        2
MTW-3     MTW        3
MTW-4     MTW        4

When AEC opens the submission form, the code should look at the table and see what the highest MyCounter number is for the ClientID=AEC (which is 2) and add 1 to AEC's MyCounter field, thus writing back to the table:

AEC-3 AEC 3

The SubID is just a combination of the ClientID and that client's MyCounter field.

Please let me know if I have explained things any better.

[NeoPa]

You certainly have.

I will look at this later when I have a little time but it's fundamentally quite straightforward.

[nico5038]

From a normalization point of view the SubID shouldn´t be stored in a table as it´s deductable. I would use on the form an unbound field that´s filled with

=ClientID & "-" & MyCounter

This formula can also be used in a query.

Nic;o)

[NeoPa]

I have to say I agree with Nico.

At the moment though, there are two ways to do this. I'll include both in case you decide to normalise your database (recommended) and lose one of the sets of fields in the process.

Private Sub Form_BeforeInsert(Cancel As Integer)
    MyCounter = Nz(DMax("[MyCounter]", _ 
                        "[SubmissionTable]", _
                        "[ClientID]='" & Me.tbxClientID) & "'"), 0) + 1
End Sub

If only [SubID] left in the record (after Normalisation update), and assuming all else equal, then :

Private Sub Form_BeforeInsert(Cancel As Integer)
    MyCounter = Val(Mid(Nz(DMax("[SubID]", _ 
                                "[SubmissionTable]", _
                                "[SubID] Like '" & Me.tbxClientID) & "-*'", _
                           "XXX-0"), 5, 9)) + 1
End Sub

NB. Previous SQL code (post #5) was incorrect. Line #5 should have used Like not =.