Assigning multiple sequential numbers to orders

[jree3000]

I have a pretty complex database that tracks product creation through sales. My product is separated into Batches. Each Batch has roughly 400 Units of varying size for sale. I need to be able to auto assign sequential numbers for each Unit ordered from each Batch. Orders will often have multiple Units purchased. Each Batch needs to have it's own set of sequential numbers though.

Example:
Product Batch A
Product Batch B




Order 1:
3 Units of Batch A
Auto assign numbers 001, 002, 003.

Order 2:
5 Units of Batch B
Auto assign numbers 001, 002, 003, 004, 005.

Order 3:
4 Units of Batch A
Auto assign numbers 004, 005, 006, 007.

Etc,.


The orders are placed using an Order Form that is based on the Order Table. The Order Table relates to the Processing Table by Batch ID. I'm not sure how I can include these sequential numbers in the orders. I don't know if I need to make another table and join it with the Order Table somehow? I'm really at a loss as to how I can accomplish this. Any ideas would be greatly appreciated!!

[June7]

Generating custom unique identifier is a common topic. Here is a recent thread: https://www.accessforums.net/program...orm-46475.html

[jree3000]

Thank you. I have read through it but I'm having difficulty applying that to my specific situation. Let me break this down to the basics. I have two pertinent fields within a table. One field, named "DIN" is a combo box field that draws batch numbers from another table. The other field, "DoseNumber", is the field that I want to sequentially number (3 digits) starting at 001 and restarting for each different DIN value. I figure I need to place the code in After Update on the DoseNumber field but I'm really not sure how to structure that code. Ideas?

[June7]

Is DoseNumber a number type field?

Try something like:

Me!DoseNumber = Nz(DMax("DosNumber", "tablename", "DIN=" & Me!DIN), 0) + 1

[jree3000]

No luck, I'm afraid. The DoseNumber field is a number type field. This is what I typed:

Me!DoseNumber = Nz(DMax("DoseNumber", "Dose_Tracker", "DIN=" & Me!DIN), 0) + 1

"Dose_Tracker" is the name of the table.

Value of the DoseNumber field stayed at 0 for all entries.

[June7]

What is this form arrangement - form/subform? Is DIN on main form and DoseNumber on subform?

A better approach might be to use the subform AfterUpdate event and set DefaultValue property of the DoseNumber textbox. BTW, is this textbox set as Locked Yes, TabStop No?

Are there multiple users of this db? Will multiple users be entering data for the same DIN at the same time?

If you want to provide db for analysis, follow instructions at bottom of my post.

[jree3000]

Both DIN and DoseNumber are on a subform of a main form.

I tried using the code in DefaultValue property but it didn't fare any better, still just zeros. Textbox is set at Locked No, TabStop Yes.

At the present time there is only one user in db. Eventually there may be more but only one user will be entering data for the same DIN at a time.

I'd rather not submit the db if it can be avoided as it is quite complex and filled with tons of sensitive info that will be pretty time consuming to eliminate. I guess if it comes down to it, that's what I'll have to do but I'd rather not if it could be figured out without resorting to that.

[ssanfu]

I took a different approach.
You said

"DoseNumber", is the field that I want to sequentially number (3 digits) starting at 001 and restarting for each different DIN value.

Numbers are not left padded with zeros. So in the table I set a format. In the table, 1 is displayed as 001. (This is easily removed)

Then you had

Order 1:
3 Units
of Batch A

Result: 001, 002, 003

I created a form with 3 controls and a button.
Enter as per the example:

Order	Number of Doses	Batch
1	3	A
2	5	B
3	4	A

Open the table "Dose_Tracker" to view the results.



This is an just an example of adding custom unique identifiers.

I don't know your process/idea of entering the dosages into dB.
I envision a data entry form, then other forms to view the data. There might be other fields that need data entered (date/time, Dr. name,etc)

[June7]

If both are in the subform and user inputs DIN on each subform record, the code should work. Post your attempted code.

You might find this of interest http://access.mvps.org/access/forms/frm0012.htm

[jree3000]

June7, my attempted code is:
Me!DoseNumber = Nz(DMax("DoseNumber", "Dose_Tracker", "DIN=" & Me!DIN), 0) + 1

ssanfu, I tried your example and it works very nicely. Integrating into my existing setup may prove to be a bit more tricky though. I'm going to play with it and see what I can do.

Thank you both for all your help up to this point.

[June7]

Using the AfterUpdate event of DIN? DIN is a number type field? User inputs DIN into each record of subform?

[jree3000]

June7, some progress!

I was using AferUpdate event of DoseNumber, not DIN. I realized how foolish that was the instant I read your post. I switched it and for the 1st entry I got a "1" in DoseNumber instead of a "0". Progress. However when I went to add another one I got VB error "Run-time error "3464": Data type mismatch in criteria expression.".

DIN is not a number field (values are alpha-numeric). It is actually a combo box that gets it's values from a different table. Therefore user does not input DIN, but rather selects DIN from dropdown.

[June7]

Parameters for text fields need apostrophe delimiters. I am guessing the first entry worked because the DMax returned Null.

Try:

Me!DoseNumber = Nz(DMax("DoseNumber", "Dose_Tracker", "DIN='" & Me!DIN & "'"), 0) + 1

Selected from dropdown or typed in, shouldn't matter.

[jree3000]

That did it! You the man! Is there a way to format the DoseNumber field so it is always 3 digits (001, 002, 003, etc)?

[jree3000]

Nevermind, I figured it out by typing 000 in the Format. Thanks again for all your help!