increment number for related records, restarting at 0 for each parent

[ths]

Hi,

I have two tables, Articles and Versions, related 1 to many. In my Versions table I want to set up a field that will start at 0 for the first version record related to an article record, and as version records are added in relation to that same article record I want it to increment by 1, no gaps. It ideally would be editable. It will not likely count above 5.

The result would be

article1   version0
article1   version1
article1   version2
article2   version0
article2   version1
article2   version2
article2   version3

What's the best way to accomplish this?

Many thanks.
Tim

 

[AccessMSSQL]

You probably need to use some VBA code or DCount.
Try this: (not tested)

Public Function GetVersionCount(ArticleID as integer) as string
Dim intCount as integer
Dim strVersion as String
intcount = 0
intCount = DCount("VersionID","[Versions]","[ArticleID] = " & ArticleID)
intCount = intCount + 1
strVersion = "version" & intCount
GetVersionCount = strVersion
End Function

 

[ths]

Thanks a lot. glad to know about DCount. I'll give it a shot. I actually only need the integer value, not the concatenated string, but I can modify what you provided. Thanks again.

 

[ths]

Got it working by combining it with some VBA on the after update event property of another field on the form where records are created. Thanks again!

here are the two bits, in case it's useful to others or if it could be improved:

modified version of your function:

Public Function GetVersionCount(articleID As Integer) As Integer
Dim intCount As Integer
intCount = 0
intCount = DCount("ID", "[tblVersions]", "[ArticleID] = " & articleID)
GetVersionCount = intCount
End Function

after update event:

Private Sub level_AfterUpdate()
If Me.NewRecord Then
    versionNum = GetVersionCount([articleID])
End If
End Sub