Solved Sequential Numbering (1 Viewer)

[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;

 

[Pat Hartman]

Here's a sample for generating two types of custom sequence numbers.

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

 

[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

 

[Pat Hartman]

That's fine but keep in mind that auntie's solution is not a permanent number. If you add items to the list, the original sequence number might change.

 

[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).

 

[Pat Hartman]

You only get negative numbers when the Autonumber is defined as Random rather than Increment. If you are getting them when the Autonumber is defined as Increment, then your seed is broken and you need to fix the table.