Form Control Referencing in VBA (1 Viewer)

[ajetrumpet]

I have seen quite a few questions about referencing form controls in VBA, and I'll be the first to admit that it makes absolutely no sense. However, there are general rules you can follow that will make life a whole lot easier when dealing with this issue. Here is a list of the different syntax blocks I have used, and the data types that they are compatible with:


Direct Outside Reference (syntax inside quotation marks)

"... = Forms!FormName!ControlName"

Compatible Data Types: Text, Number, Date/Time, Currency, Boolean, Hyperlink
------------------------------------------------------------------------------------------------

Direct Inside Reference (syntax inside quotation marks)

"... = Me!ControlName"

Compatible Data Types: NONE
------------------------------------------------------------------------------------------------

Indirect Outside Reference (syntax outside quotation marks)

"... = " & Forms!FormName!ControlName

Compatible Data Types: Number, Currency, Boolean
------------------------------------------------------------------------------------------------

Indirect Inside Reference (syntax outside quotation marks)

"... = " & Me!ControlName

Compatible Data Types: Number, Currency, Boolean
------------------------------------------------------------------------------------------------

Indirect Inside Reference (syntax outside quotation marks)

"... = " & Me.ControlName

Compatible Data Types: Number, Currency, Boolean
------------------------------------------------------------------------------------------------

Direct Outside String Reference (syntax concatenated inside quotation marks)

"... = '" & Forms!FormName!ControlName & "'"

Compatible Data Types: Text
------------------------------------------------------------------------------------------------

Direct Inside String Reference (syntax concatenated inside quotation marks)

"... = '" & Me.ControlName & "'"

Compatible Data Types: Text
------------------------------------------------------------------------------------------------


The above syntax lines were tested using Access 2007. It is quite likely that the compatibility for each syntax block will change with different versions of the program (I can almost guarantee this). There is nothing we can do about that, but hopefully this helps you figure out what works and what doesn't with your specific version.

In the attached sample, the data types of the combo boxes are valid references for the syntax used if the listbox populates the value. If there is no listbox value or a parameter popup appears, it is invalid. The bottom line here is: Reference syntax depends entirely on what data type is contained within the control.

I hope this thread has helped simplify this (unnecessarily) complicated issue!