Sequential numbering Dcount Dmax composite primary key

[judyjacobse]

Hi,

I have a question concerning the automatically sequential numbering of a primary key as part of a composite primary key. I know there have been previous threads about this topic, but i just can't figure it out... Hopefully someone else can. This is what my data look like:

Tbl_treatment:
ID=numeric field (also in tbl_pt and in tbl_tumor)
Tumornr=numeric field (also in tbl_tumor)
Treatmentnr=numeric field

[ID] and [tumornr] are fixed and i would like to automatically number [treatmentnr] per [ID] AND [tumornr] in a way that the output will look like this:

1001 1 1
1001 1 2
1001 1 3
1001 2 1
1001 2 2
1001 3 1, etcetera.

However, when i use the SQL-formula below my output looks like this. It seems as if the function is not properly taking the composite primary key of [ID] AND [tumornr] into account or not finding the true max value:

1001 1 1
1001 1 2
1001 1 3
1001 2 2
1001 2 3
1001 3 3, etcetera.

If (DCount("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) = 0 Then Me.treatmentnr = fRowNum(False)
Else
Me.treatmentnr = (DMax("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) + 1

Thank you for reading and i'll be happy to provide additonal information if needed!

Judy

 

[DavidAtWork]

wouldn't this work

If DCount("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")> 0 Then
    Me.treatmentnr = DMax("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "") + 1
    Else
        Me.treatmentnr =  1
End If

David

 

[judyjacobse]

Hi David!


Thank you so much for your interest and help! I checked the VBA code and it worked! This is the solution I found and used with succes this morning:


Me.treatmentnr = Nz(DMax("[treatmentnr]", "Tbl_treatment", "[ID]&[tumornr]= " & Me.IDr & Me.tumornr), 0) + 1

Judy

 

[DavidAtWork]

yes that is even better ... nice solution

David