By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,881 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Using DMax to assign a unique request number in a subform

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert(Cancel As Integer)
  2.     MyCounter = Nz(DMax("MyCounter", "SubmissionTable", "ClientID=" & Forms!frmSubmitterLibrary!tbxClientID)) + 1
  3. End Sub
I am missing some crucial code, but I can't figure out on my own. Any help would be very much appreciated.
Jan 14 '10 #1
Share this Question
Share on Google+
10 Replies


nico5038
Expert 2.5K+
P: 3,072
Did you try Me.MyCounter as I assume the field is named MyCounter and resides on your form with that name.

Nic;o)
Jan 14 '10 #2

NeoPa
Expert Mod 15k+
P: 31,494
@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.
Jan 14 '10 #3

P: 4
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
Jan 26 '10 #4

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert(Cancel As Integer)
  2.     MyCounter = Me.tbxclientID & "-" &  _
  3.                 Val(Mid(Nz(DMax("MyCounter", _ 
  4.                                 "SubmissionTable", _
  5.                                 "ClientID='" & Me.tbxClientID) & "-*'", _
  6.                            "XX-0"), 4, 9)) + 1
  7. End Sub
Jan 27 '10 #5

P: 4
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.
Jan 27 '10 #6

NeoPa
Expert Mod 15k+
P: 31,494
@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.
Jan 27 '10 #7

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. SubID  ClientID  MyCounter
  2. AEC-1     AEC        1
  3. MTW-1     MTW        1
  4. MTW-2     MTW        2
  5. AEC-2     AEC        2
  6. MTW-3     MTW        3
  7. 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.
Jan 28 '10 #8

NeoPa
Expert Mod 15k+
P: 31,494
You certainly have.

I will look at this later when I have a little time but it's fundamentally quite straightforward.
Jan 28 '10 #9

nico5038
Expert 2.5K+
P: 3,072
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
Expand|Select|Wrap|Line Numbers
  1. =ClientID & "-" & MyCounter
This formula can also be used in a query.

Nic;o)
Jan 28 '10 #10

NeoPa
Expert Mod 15k+
P: 31,494
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert(Cancel As Integer)
  2.     MyCounter = Nz(DMax("[MyCounter]", _ 
  3.                         "[SubmissionTable]", _
  4.                         "[ClientID]='" & Me.tbxClientID) & "'"), 0) + 1
  5. End Sub
If only [SubID] left in the record (after Normalisation update), and assuming all else equal, then :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert(Cancel As Integer)
  2.     MyCounter = Val(Mid(Nz(DMax("[SubID]", _ 
  3.                                 "[SubmissionTable]", _
  4.                                 "[SubID] Like '" & Me.tbxClientID) & "-*'", _
  5.                            "XXX-0"), 5, 9)) + 1
  6. End Sub
NB. Previous SQL code (post #5) was incorrect. Line #5 should have used Like not =.
Jan 29 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.