Numbering Continuous Forms with Labels

[clickingwires]

As it stands right now I have a form with a sub form within it. This sub form is a continuous form that I want to have numbered i.e. Element 1, Element 2 ... and so on. I can implement this with Two labels one for "Element " part and one for the number. I've tried several different ways all of which proved little. Is there anyway that I can do this?

[FishVal]

Hi, there.

Well, it requires some definite table design - the table on "many" side of relationship has to have AutoNumber field.


Below is working example.


Tables:

tblMaster
keyMasterID Autonumber(Long), PK
......

tblChild
keyChildID Autonumber(Long, Increment), PK
keyMasterID FK(tblMaster)
.........


Code module:

Public Function EnumRecords(varPK As Variant, varFK As Variant) As Variant
 
    'declares variable which content will be preserved between calls
    Static lngCounter As Long
 
    If IsNull(varPK) Or IsNull(varFK) Then Exit Function
 
    'reset counter if the value of tblChild.keyChildID
    ' is the minimum for given tblChild.keyMasterID
    If DMin("keyChildID", "tblChild", "keyMasterID=" & varFK) = varPK Then _
        lngCounter = 0
 
    lngCounter = lngCounter + 1
    EnumRecords = lngCounter
 
End Function
 

Queries:

qryChild

SELECT tblChild.keyChildID, tblChild.keyMasterID, tblChild.txtChild, "Element " & EnumRecords(tblChild.keyChildID,tblChild.keyMasterID) AS txtElNo
FROM tblChild;
 

Forms:

frmMaster
RecordSource: tblMaster
Controls: frmChild as subform

frmChild
RecordSource: qryChild
Controls: txtElNo as TextBox - > ControlSource: qryChild.txtElNo


Regards,
Fish