Help in Sequential Number that Restart to 1

[napster75]

hi everyone,
I have a problem in sequential number for example:
tr001 consist of 5 records counting is 1,2,3,4,5
tr002 consist of 3 records counting is 1,2,3
and so on..

below is the data:





I try to use DMax + 1 but I did not get the result I want.here is the code I used came from other forum.

If Me.NewRecord = True Then              
Me.[CounterNo] = Nz(DMax("[TransID]", "Drawing"), 0) + 1           
End If

Field where sequential number stored - CounterNo


Field where tr number stored - TransID
Table - Drawing

[CJ_London]

I don't think dmax will work for what you want, you probably want dcount

there are hundreds of threads on this common subject, I'm surprised you haven't found one - look to the bottom of this thread and you will find a number there. Or there is this recent one

https://www.accessforums.net/showthread.php?t=73616


For the future, comments like 'I did not get the result I want' does not help us to help you. What result did you get?

[napster75]

thank you for the reply.. i saw that thread already but i did not get the result i want.

I also try the query but it only count on how many records.

SELECT Drawing.DwgID, Count(Drawing.DwgID) AS CountofSeqNo
FROM Drawing
GROUP BY Drawing.DwgID;

[CJ_London]

thank you for the reply.. i saw that thread already but i did not get the result i want.

hint

For the future, comments like 'I did not get the result I want' does not help us to help you. What result did you get?

And since I know it should do what you want, provide the code you actually used

[napster75]

Actually this is two table that link by TransID

tblTrans
PK TransID
RefNo
others details

tblDrawing
PK DwgID
FK TransID
CounterNo
DwgNo
others details



I have a form trans together with drawing subform
what I did is that when I enter drawing no the CounterNo should start to 1

I add the code in DrawgNo AfterUpdate

Private Sub DrawingNo_AfterUpdate()
    If Me.NewRecord = True Then
        Me.[CounterNo] = Nz(DMax("[TransID]", "Drawing"), 0) + 1
    End If
End Sub

Sorry for the explanation I hope that you will understand what im trying to do.

Thank you

[CJ_London]

best guess

DCount("*", "Drawing","DwgID<=" & DwgID & " AND TransID=" & TransID) + 1

[napster75]

Ajax,

this is amazing how did you do that. I tried so many times

can you explain what happen. For what I understand is that the "*" means it will count all the records in drawing table and then compare to TransID?

thank you very very much.

[CJ_London]

the * just counts records, if you put a field name in, it counts all those where that field is not null - a subtle difference

[napster75]

I see..

Thank you very much for your help.. I will mark this as solved.