Solved Sequential Numbering

[tmyers]

I am not sure what subforum this belong under, so if I got it wrong, I apologize.
On my form, I have a control displaying an items row number (textbox) and for the life of me, I cant figure out how to make the control use the dmax function (if that even is the correct function to use) so for a given ID, the number will always start at 1 and go up. So for JobID 2, you can have rows 1-5 and for JobID 6, you can have rows 1-50. The rows are not related to each other from JobID to JobID, I would just like a nice numbering system for identification.

 

[jdraw]

I recommend you post a screen shot and/or mock up of what want.
If you have some code you are using/working with, then please post that as well.

Is this what you are referring to?

 

[Auntiejack56]

You could also try something like this:

And your SQL in qryItems to produce the ordered item numbers would be this:

SELECT t.JobID, t.ItemID, t.ItemName, DCount("*","tblItems","JobID = " & [JobID] & " and ItemID<" & [ItemID])+1 AS ItemNumber
FROM tblItems AS t
ORDER BY t.JobID, t.ItemID, DCount("*","tblItems","JobID = " & [JobID] & " and ItemID<" & [ItemID])+1;

 

[arnelgp]

you can use Query as in post #3.
or you can use a custom VBA function to generate the Sequence.

 

[tmyers]

Sorry for the late reply!
JDraw, the article you shared seems to be on the right track but not quite what I am after.

Edit for JDraw:
The example the article provided is correct however. Different test that have questions that always start at 1.
Edit #2:
Rereading the article as I dont think I followed it right.

Auntie, same thing. To provide a bit more detail, I am after more or less line numbering. While this isn't a sales system, I more or less am after line numbers. So order 287 has 5 lines numbered 1-5, order 7836 has 20 lines numbered 1-20. More or less just a nice visual numbering system to show how many lines a given job has verse the users inputting the number and messing it up and making it ugly.

Pat, I downloaded the sample and will review in just a moment and report back.

Arnelgp, same as Pat.

Thank you everyone!

 

[Auntiejack56]

Hmm. The example I gave has 3 jobs (orders), with lines numbered (each starting from 1) in the Item Number column on the extreme right. Seems ok to me.
Jack

 

[tmyers]

Hmm. The example I gave has 3 jobs (orders), with lines numbered (each starting from 1) in the Item Number column on the extreme right. Seems ok to me.
Jack

I may have misunderstood it like I did the article JDraw shared. It is early and I havent yet finished my cup of coffee . I will take a second look.

 

[arnelgp]

i change the function.

 

[tmyers]

I finished my coffee (which was in my Deadpool mug Arnel ) and Auntie, your query is what I am after. I just apparently had not had enough coffee for full cognitive reading ability to realize that.

 

[Gasman]

Didn't see this then?

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

 

[tmyers]

Didn't see this then?

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

I did, but I will be honest, I did not understand it lol.
Auntie's example was the easiest one for me to follow

 

[arnelgp]

just a fair warning. Autonumber fields are long integer.
there was a case when one of the OP here experience having -negative values on them.
it is system generated (therefore we do not have control on what value may come up).