I was introduced, by fellow MVP Ben Clothier, to a really nice way to code when defining a Form/Report object when referring to an existing form/report so as to gain intellisense for that database object. You’re probably saying to yourself: “What is he talking about”, but bare with me for just a moment because it will be worth it!
Say you have a form name Form1 with various controls on it, with various properties, …
Up until relatively recently, when I was coding this with reference to a form such a s this I might use code along the lines of:
Public Sub SomeFunctionName()
Dim frm As Access.Form
Set frm = Forms![Form1].Form
With frm
'...
End With
Set frm = Nothing
End Sub
Now the above example works just fine, but one has to go back and forth between the actual form and the VBE to copy/paste control names, … to code.
Where it becomes truly fun is that with a very small change in code, one can gain the benfit of intellisense for the designated database object. Observe the following code:
Public Sub SomeFunctionName()
Dim frm As Form_Form1
Set frm = Forms![Form1].Form
With frm
'... Now, frm possesses Intellisense relative to Form1, coding just became much easier!!!
End With
Set frm = Nothing
End Sub
by declaring the variable frm as the specific form (As Form_YourFormName) you intend to work with instead of a generic Access.Form, you now gain Intellisense in the VBE for the designated form. Now control names, events, … are a keystroke away. This greatly facilitates coding, reduces typos, …
Similarily for reports you can do something along the lines of:
Public Sub SomeFunctionName()
Dim rpt As Report_Report1
Set rpt = Reports![Report1].Report
With rpt
'... Now, rpt possesses Intellisense relative to Report1
End With
Set rpt = Nothing
End Sub
Try it out and see for yourself how it makes your coding life a little bit easier!
