Access control from public function with variables ? 1

[Grounded]

This should be simple:

I double click in a text box on a form. The event triggers a public function in a separate module and passes the current form with ME. I want to take the value in the box convert it from feet to meters and return it to the box.

Here's the function:


Public Function Converter(frmCurrentForm As Form)

Dim ctlCurrentControl As Control
Dim strControlName As String
Dim strFormName As String
Dim intControlValue As Integer

strFormName = frmCurrentForm.Name

Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name
intControlValue = CInt(Screen.ActiveControl)

MsgBox strFormName ' for test - works
MsgBox "current control" & strControlName ' for test - works

intControlValue = intControlValue * 0.3048


Forms!sfrmMDPoint.Controls!Text22.Text = intControlValue ' works
'Forms!strFormName.Controls!strControlName.Text = 99 'doesn't work
'Forms!frmCurrentForm.Controls!ctlCurrentControl.Text = 100 'doesn't work

So i successfully pass the form name, control name and value into the function.
What i can't seem to do is to use these (stored in variables) to access the control - which is key since i'll be calling this from many fields. If i hard code it in it works fine.

 

[formerTexan]

Pass the form name and control name into your function as follows.

Public Function Converter(strCurrentFormName As string, strCtlName as string)

Yourvariable = Forms("strCurrentFormName").form("strCtlName").value

Cheers,
Bill

 

[Grounded]

Thanks for the advice but i still get the same error.

"Database can't find the form 'strFormName' referred to in macro expression or Visual Basic Code."

Code used:

(Where strFormName and strControlName have successfully been populated)

strtest = Forms("strformname").Form("strControlName").Value

iz

 

[MajP]

ODE
strtest = Forms(strformname).Form(strControlName).Value

 

[Grounded]

Exactly!

Thanks....

iz

 

[Eupher]

Grounded,

Another way... Since you've passed the entire form object to the function as an argument, and are declaring a control on the form as a variable, this should also work:

ctlCurrentControl.Text = intControlValue

HTH,

Ken S.

 

[Eupher]

p.s.
Depending on the level of precision you want with your conversion, you may want to declare your numeric variable as Single, Double, or Variant, and use the CSng(), CDbl(), or CDec() function for your conversion.

Just curious, why are you passing the entire form as an argument? If you're just interested in converting the value, you don't need to pass anything to the function:

Public Sub Converter()
Dim MyControl As Control
Set MyControl = Screen.ActiveControl
MyControl.Text = CSng(MyControl.Text) * 0.3048
End Sub

Then this to call the function from the double-click event of the textbox:

Call Converter

HTH,

Ken S.

 

[Grounded]

I guess you go with the solution that shows the most promise soonest.

In the end though it worked out. Turns out that this works great – until the converter is called from a subform. Then screen.activeform returns the active parent form and the form passed in contains the subform. This proved to be an easy way to determine if the converter was being called from a subform – which required different code for refreshing and for returning a value to the active control.

This is the final version. I used an enumeration to set up ConversionType so there are predefined choices depending on which field it's called from.

Public Sub Converter(ConversionType As ConversionType, frmCurrentForm As Form)
'place in double click event - when user double clicks in a field then this will ask
'for a value and convert to the correct units
'Easy to enter ME for form when calling this function
'code detects if it is called from a subform and adapts accordingly

On Error GoTo Converter_err:

Dim ctlCurrentControl As Control
Dim strControlName As String
Dim strFormName As String
Dim strControlValue As String
Dim strTest As String
Dim strInputValue As String
Dim dblInputValue As Double
Dim strPrompt As String
Dim strTitle As String
Dim dblConvFactor As Double
Dim strActiveForm As String

strFormName = frmCurrentForm.Name 'the name of the form that contains the control

Set ctlCurrentControl = Screen.ActiveControl 'get the control that has the focus
strControlName = ctlCurrentControl.Name 'get the name of the control that has the focus

strActiveForm = Screen.ActiveForm.Name 'get the active form (different from above if control on subform)

'Refresh so that a change just before double click gets registered
'if the passed form name and the active form are different then the passed
'form is now a subform and needs to be handled differently
If strActiveForm = strFormName Then
    Forms(strFormName).Refresh
Else
    Forms(strActiveForm)(strFormName).Form.Refresh
End If


strPrompt = "Enter value to convert"
strTitle = "Convert from "

'bring in current control value and test
If IsNull(Screen.ActiveControl) Then
    strInputValue = ""
Else
    strInputValue = Screen.ActiveControl
End If

'confirm discard of current value
If strInputValue <> "" Then
    If MsgBox("Discard the current value of " & strInputValue & " and enter a new one?", vbOKCancel) = 2 Then
        End
    End If
    
End If

'check to see what conversion is needed and make appropriate changes
 Select Case CStr(ConversionType)
 Case 1
    strTitle = strTitle & "Feet to Meters"
    dblConvFactor = 0.3048
 
 Case 2
    strTitle = strTitle & "Meters to Feet"
    dblConvFactor = 3.28084
    
 Case 3
    strTitle = strTitle & "Cubic Yards to Cubic Meters"
    dblConvFactor = 0.76455
    
 Case 4
    strTitle = strTitle & "Gallons to Liters"
    dblConvFactor = 3.78541
 Case 5
    strTitle = strTitle & "Inches to Centimeters"
    dblConvFactor = 2.54
    
    
 End Select
 
 
'Get new input value
 strInputValue = InputBox(strPrompt, strTitle)
 
 'test new value
 If strInputValue = "" Then
    MsgBox "You did not enter a value or chose cancel."
    End
 ElseIf Not IsNumeric(strInputValue) Then
    MsgBox "You did not enter a number."
    End
 Else
    dblInputValue = CDbl(strInputValue) * dblConvFactor
 End If

'put the converted value back in the active control
'again - handle differently if on subform
If strActiveForm = strFormName Then
    Forms(strFormName).Form(strControlName).Value = dblInputValue
Else
    Forms(strActiveForm)(strFormName).Form.ActiveControl.Text = dblInputValue
End If


Converter_exit:

    
    Set ctlCurrentControl = Nothing
    Set frmCurrentForm = Nothing
    
Exit Sub

Converter_err:
MsgBox "error"

End Sub