Wednesday 18 March 2009

Dynamically get an object from a form or report in Access

Today I was working on a private project in Access 2007. To fill some labels dynamically, I needed a function that searches in the control collection of a report to get my label of interest. Based on parameters I gave. As you may know, returning values from a function is in Access a bit old fashion, you cannot use 'return...'. It looks like this:

private function Test() as string
Dim strDemo as string
'Do something...
Test = strDemo
end function

Ok, that's fine. But I was looking for a dynamic way of filling some labels. So I needed to get that label from the report control collection to change the caption. This will not work using that 'FunctionName = ControlOfInterest' way. In the code below I show the way you can get your control of interest back using a function.

Private Function GetTargetLabel(i As Integer) As Label
Dim ctl As Control
Dim lblName As String

lblName = "lblVisitDate" & CStr(i)

For Each ctl In Me.Details.Controls
If ctl.ControlType = acLabel Then
If ctl.Name = lblName Then
Set GetTargetLabel = ctl
Exit For
End If
End If
End Function

Note that you need to use the syntax 'Set FunctionName = ControlOfInterest'. Stupid, but that's the way it works. You can do the same trick for form controls as well.
You can customize this function dependent of your interest.
In an other function/sub I can just do the following:

GetTargetLabel(i).caption = "value of interest"

No comments: