referring to a field in a subform in a function

  • Thread starter Thread starter Ben Armfield
  • Start date Start date
B

Ben Armfield

I have a form with multiple subforms. Want to perform the
same procedure on fields in the various subforms, so am
trying to set up a function to do that to avoid repetitive
code. I cannot get the function to pick up the field. Get
error message of "variable not defined" or "type mismatch."
Have tried using the argument Field, Control, and TextBox,
all with the same results.

Any help would be greatly appreciated. Thanks.
 
You should post the codes of your attempts.

However, it should be something like:

Forms!MainForm!SubformCONTROL.Form.ControlName

Note that the SubformCONTROL name may be different from the name of the Form
being used as the Subform (or more technically correct, being used as the
SourceObject of the Subform Control).
 
Mr Dink and Mr Vrolyk,

Thanks very much for your responses to my inquiry. Below is
the description of a simple,nonsense database that
demonstrates my problem. It's probably something simple,
but I can't see it. Again, thanks for your help. - Ben Armfield

- Create a table with one field, fieldA. Enter one record,
"Alpha".
- Create a form, subFormA, linked to the table and with one
textbox for and named after fieldA.
- Create a form, formA, with one subForm for and named
after subFormA, and one command button.
- Create the following function (as a local function in
formA, or as a public function):

Function funcTestB(formX As Form, subFormX As SubForm,
fieldX As TextBox, valueX As String)

If Forms!formX!subFormX.Form.fieldX = valueX Then
MsgBox valueX
End If

End Function

- Create the following code for the command button's event
Click.

Private Sub buttonA_Click()

funcTest Me, subFormA, fieldA, "alpha"

End Sub

RESULT - When I click the command button, I get the error
message "Variable not defined." This occurs if the function
is local or public. It also occurs whether the function
argument fieldX is defined as field, textbox, or control.
 
I forgot to say that the error message "Variable not
defined" is linked to "fieldA" in the button code.
 
Function funcTestB(formX As Form, subFormX As SubForm,
fieldX As TextBox, valueX As String)

If Forms!formX!subFormX.Form.fieldX = valueX Then
MsgBox valueX
End If

End Function

- Create the following code for the command button's event
Click.

Private Sub buttonA_Click()

funcTest Me, subFormA, fieldA, "alpha"

End Sub
Click to expand...


Your function's reference has things all out of whack. The
arguments are all declared as an object, but you use them as
if they were known names and even then the syntax would be
wrong.

This is the easiest way to do what the function appears to
want:

Function funcTestB(fieldX As TextBox, valueX As String)
IF fieldX = valueX Then
MsgBox valueX
End If
End Function

Private Sub buttonA_Click()
funcTest fieldA, "alpha"
End Sub

Since fieldX is being passed as a text box object, you
should not (and can not) qualify it with references to other
objects.

If you were thinking of (unnecessarily) passing the names of
the objects (not the objects themselves), then the code
would look like this:

Function funcTestB(formX As String, subFormX As String,
fieldX As String, valueX As String)
If Forms(formX).Controls(subFormX).Form.Controls(fieldX) =
valueX Then
MsgBox valueX
End If
End Function

Private Sub buttonA_Click()
funcTest Me.Name, "subFormA"," fieldA", "alpha"
End Sub

Note your example is a little hokey in that you declare a
function, but call it as if it were a Sub procedure.
 
Wow far better than I could have said it if I would have event known what to
say.

Tony
 
Mr Dinh,

Sorry about my typing error in your name last time.

I still cannot get a function referring to a field in a
subform to work. I had already tried using reference to the
field alone, without any form qualifiers, as Mr Barton
suggests, but couldn't get that to work, either. I am going
to want to use the same function across several forms with
subforms, so it really needs to be a public function.

The function will be examining all the values of the field
displayed in the subform via a loop. I am wondering if
using a recordset, instead, would not be a better route,
but would still like to figure out why I can't get the
function to connect with the field directly.

Any observations you might have would be appreciated.
Thanks. Ben Armfield
 
Ben said:
I still cannot get a function referring to a field in a
subform to work. I had already tried using reference to the
field alone, without any form qualifiers, as Mr Barton
suggests, but couldn't get that to work, either. I am going
to want to use the same function across several forms with
subforms, so it really needs to be a public function.

The function will be examining all the values of the field
displayed in the subform via a loop. I am wondering if
using a recordset, instead, would not be a better route,
but would still like to figure out why I can't get the
function to connect with the field directly.
Click to expand...


I seem to have missed the point about the function call
being in a main form procedure and the referenced control in
a subform.

Instead of just using the name of the control (as you could
do for a control in the same form as the control), you have
to reference it through the subform control:

Private Sub buttonA_Click()
funcTest subFormA.Form.fieldA, "alpha"
End Sub
 
Back
Top