How to use a variable in place of actual control name?

[Guest]

I would like to declare a "short" variable and assign a "lengthy" value to it
then use the variable in an expression. The intention is to shorten the final
expression by using the "short" variable (X) instead of (Reserving User Name
Control 1) without using the actual "lengthy" control name. The problem is
that I keep getting errors and cannot use the variable as a reference to the
control name properly. Any suggestions?

Example:
Private Function Check_If_IsNULL() As Boolean

Dim X As String
X = "Reserving User Name Control 1"

If IsNULL(X.Value) Then 'This line errors with syntax and object errors

Check_If_IsNULL = False

Else

Check_If_IsNULL = True

End If

EndFunction

P.S. I also had the same problem when trying to pass a table name into a
function as a variable and then use it to open (populate) a recordset for
that table.
Example: myRecordSet.Open strTable, ,adOpenKeySet, adLockBatchOptimistic

I keep getting an error that mentions an invalid SQL statment.

 

[George Nicholson]

For X.Value to return the value you want, X would have to be declared as an
object with a Value property (i.e., a control or textbox), not a string.
(Strings are variables & don't have a Value property).
......
Dim X As Control
Set X = me.controls("Reserving User Name Control 1")
If IsNULL(X.Value) Then
......

If you'd rather work with the control name (i.e. a string), then:
......
Dim X As String
X = "Reserving User Name Control 1"
If IsNULL(me.controls(X).Value) Then
.....

Example: myRecordSet.Open strTable, ,adOpenKeySet, adLockBatchOptimistic

The only thing I see wrong here is that your 2nd argument (connection) is
blank. If strTable is the name of a table within your already opened
connection object (and you supply the connection argument), it should work.

HTH,

 

[Guest]

Mac,

It is possible to use the short variable just as you desire. Your
declarations and assignments are correct, but your "if" statement would need
to be like:

If IsNULL(me.controls(X).Value) Then
'your other code here
End If

 

[Guest]

In regards to the control name variable thank you for your help. That is just
what I was looking for.

In regards to the Recordset.Open and the blank second parameter I declared
the connection in a prior expression such as "Set cnn1 =
CurrentProject.Connection" and "myRecordSet.ActiveConnection = cnn1" to
establish a connection. Then I used the statment as I entered in the earlier
as "myRecordSet.Open strTable, ,adOpenKeySet, adLockBatchOptimistic". It
mentioned and error related to a SQL statement. I don't beleive my method of
opening the conneection is faulty because It works in otherr pieces of code.
I still think the syntax of the strTable or the way I am using it is
incorrect. Any suggestions? Thanks for your help.