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?
-
-
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:
[code=vb]
Public Function EnumRecords(var PK 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.keyChi ldID
' is the minimum for given tblChild.keyMas terID
If DMin("keyChildI D", "tblChild", "keyMasterI D=" & varFK) = varPK Then _
lngCounter = 0
lngCounter = lngCounter + 1
EnumRecords = lngCounter
End Function
[/code]
Queries:
qryChild
[code=sql]
SELECT tblChild.keyChi ldID, tblChild.keyMas terID, tblChild.txtChi ld, "Element " & EnumRecords(tbl Child.keyChildI D,tblChild.keyM asterID) AS txtElNo
FROM tblChild;
[/code]
Forms:
frmMaster
RecordSource: tblMaster
Controls: frmChild as subform
frmChild
RecordSource: qryChild
Controls: txtElNo as TextBox - > ControlSource: qryChild.txtElN o
Regards,
Fish
Comment