need to convert variant to double... somehow

[n8kindt]

i've got a brainteaser for you guys. let's say i have a form called "Form1"...
now, i have a lot of fields on Form1 and get tired of typing out forms!form1.fillintheblank.value because they are referenced quite a bit. so i want to create a function that will fill in the blank and return the value that resides in that location. the problem is, once it is converted to string (in order to use concatenation) i can't seem to convert it back to a true variant. it always retains the form of a string. take this for example:

Function Permissions(Root As String)
Dim strRoot As String
Dim varRoot As Variant
strRoot = "[Forms]![Form1].[" & Root & "].value"
varRoot = (CVar(strRoot)
If CurrentProject.AllForms("Form1").IsLoaded Then
Permissions = Nz(varRoot, 0)
Else: Permissions = 0
End If
Debug.Print Permissions
End Function

the output of Permissions("He llo") is:
[Forms]![Form1].[Hello].value

i would much rather have the output be whatever value is in that field :)
is there any possible way around this? i've thrown the kitchen sink at this problem and i've got nothing. any suggestions? thanks guys.
--nate

[ADezii]

This is absolutely no need to always use the Fully Qualified Reference for a Form Field, use the Shortcut Version:

'to refer to the [txtLastName] Field on Form1
Dim frm As Form
 
Set frm = Forms![Form1]
Debug.Print frm![txtLastName]

[n8kindt]

This is absolutely no need to always use the Fully Qualified Reference for a Form Field, use the Shortcut Version:

'to refer to the [txtLastName] Field on Form1
Dim frm As Form
 
Set frm = Forms![Form1]
Debug.Print frm![txtLastName]

i'm not sure what that does for me. i added this to my code it still returned the value as a string--not the value in the form:

Function Permissions(Root As Variant)
Dim strRoot As String
Dim varRoot As Variant
Dim CvarRoot As Variant
Dim frm As Form
 
'to refer to frm!Root as defined in function
Set frm = Forms![Form1]
strRoot = "frm!" & Root
CvarRoot = CVar(strRoot)
varRoot = CvarRoot
 
If CurrentProject.AllForms("Form1").IsLoaded Then
Permissions1 = Nz(varRoot, 0)
Else: Permissions = 0
End If
Debug.Print Permissions
End Function

somehow i need to concatenate 2 variants--WITHOUT converting it to a string. and i have no idea if this is possible or how it would be done.

[Stewart Ross]

Hi Nate. I think there is some confusion over what you are trying to achieve; if you want to return the value of a control you don't need the various variable assignments in your function, and as ADezii has pointed out you can refer to a field directly.

There are several ways to do so. I use the following general function, supplying as arguments the name of the form and the name of the field whose value is to be returned as strings in each case:

Public Function FormFieldValue(FormName As String, FieldName As String)
    FormFieldValue = Forms(FormName).Controls(FieldName)
End Function

The function is untyped (it returns a variant value).

I use this function in complex Access queries to replace direct form field references, which the Jet Database Engine often cannot recognise.

-Stewart

[NeoPa]

For controls within the current form, called from within the form's module, you need even less.
For clarity, most use Me. in front of control references, but that, and the .Value, are actually defaulted anyway, so a reference to Me.ControlName. Value, can actually be expressed in the code simply as ControlName.

X = ControlName

[n8kindt]

For controls within the current form, called from within the form's module, you need even less.
For clarity, most use Me. in front of control references, but that, and the .Value, are actually defaulted anyway, so a reference to Me.ControlName. Value, can actually be expressed in the code simply as ControlName.

X = ControlName

alright, i didn't know that. that is good to know. thanks, NeoPa

[n8kindt]

Hi Nate. I think there is some confusion over what you are trying to achieve; if you want to return the value of a control you don't need the various variable assignments in your function, and as ADezii has pointed out you can refer to a field directly.

There are several ways to do so. I use the following general function, supplying as arguments the name of the form and the name of the field whose value is to be returned as strings in each case:

Public Function FormFieldValue(FormName As String, FieldName As String)
    FormFieldValue = Forms(FormName).Controls(FieldName)
End Function

The function is untyped (it returns a variant value).

I use this function in complex Access queries to replace direct form field references, which the Jet Database Engine often cannot recognise.

-Stewart

PERFECT! yes, that is exactly what i was trying to achieve. sorry for explaining it so poorly. i will try this out but i'm sure it will work. thank you so much!