Submitting a form name to a function?

[AccessChap]

Morning all! Yes, I'm afraid it's me again although I have been away for an encouraging length of time haven't I?

Got what I suspect is a simple question of syntax so I'm hoping a kind soul will be able to point out the correct notation for my command and away I'll go.....

What I'm trying to do is reference a field on a subform from a function that is call when I click a button on the parent form. The problem is that depending on a combo box value on the parent form I may be using one of two subforms. So I need the function to inspect the field on the relevant subform only. I start off with my call to the function thusly:

Dim strFranchise as string

strFranchise = Me.lstFranchise

myFunction(strFranchise)

All well and good so far and as an example the strFranchise will contain "PEU" or "CIT" at this moment (Peugeot or Citroen manufacturers)

Inside myFunction is this line:

myVariable = Me.frm_Subform.Controls(abc)

Don't worry about the abc variable, that's just a counter for a loop as there are lots of checkboxes to be vetted on the subform. The part that I can't code correctly is the frm_Subform. Obviously if I replace this with the subform's literal name then all is well but I can't handle two or more subforms

What I want to be able to do is replace the frm_Subform automatically with the relevant form name. All the subforms have a common naming convention starting with ftm_stub_ and then ending with a three letter franchise designator i.e. "PEU" or "CIT". I suppose what I want is a command like:

myVariable = Me.frm_stub & strFranchise & .Controls(abc)

but that produces a compile error. I clearly don't have the correct syntax Anyone able to correct my syntax?

Any help gratefully received,

Andy

 

[DCrake]

If you are calling this function from the main form you need something to tell the function which subform is the control on. Taking a step back this is dictated by the selection form a combo box. If you send the value from the combo box to the function then in your function use the same logic that you used to decide which subform is required to determine which control on which subform need querying. I Think.

David

 

[AccessChap]

Hi David,

I was hoping to achieve this but unfortunately I am still getting an error. My line of code that errors is this:

If Forms!frm_Job_Selection_CIT.Controls(cbx) = True Then
...

So I have temporarily suspended my quest for automated code and supplied the literal subform name. Alas I now get an run-time error 2450 stating that MS Access cannot find the form 'frm_Job_Selection_CTI'

This is confusing because by definition the subform is open. We are able to edit controls on the subform before the above code is triggered. Have you come across this before? It has all the hallmarks of a Microsoft quirk rather than an error. Naturally I have checked the spelling of the subform and control name and they are correct...

 

[wazz]

how about
myVariable = Me.frm_stub_ & strFranchise & .Form.Controls(abc)

 

[AccessChap]

Thanks but that gives me an error of "Invalid use of Me keyword"

 

[wazz]

is your function in with the form's code or a separate module?

 

[AccessChap]

To be honest I'm not sure. When I break out into the code the function is in a module called functions. If I open the code for the button then the focus jumps from the modules part of the window up to the Access part of the window. Does that make sense? If not I have attached a snapshot of where the cursor jumps to when I go back to the form code...

 

[HiTechCoach]

Try changing this:

myVariable = Me.frm_stub & strFranchise & .Controls(abc)

to

myVariable = forms("frm_stub" & strFranchise ).Controls(abc)

Note: This syntax will only work for a parent form.

 

[AccessChap]

Try changing this:

myVariable = Me.frm_stub & strFranchise & .Controls(abc)

to

myVariable = forms("frm_stub" & strFranchise ).Controls(abc)

Note: This syntax will only work for a parent form.

Thanks but I'm working with a subform at the moment

 

[HiTechCoach]

The problem it that you are using the syntax for a form object when a sub form is a control object.

I am not sure they programming logic you are trying to use will work with sub forms. I will see if I can find an example of how to reference controls on a sub form in a generic manner that will help you.

 

[SOS]

For subforms:

If Forms(variable).Controls(sfrmContainerVariable).Form.Controls(ctlOnSubformVariable).Value = True

etc.

 

[wazz]

also note that the function is in a separate module but part of the code uses 'Me' (myVariable = Me.frm_stub & strFranchise & .Controls(abc)) which refers to a form. the function doesn't know what Me is. the function either needs to be with the form (and use Me) or the function needs to be told which form it is dealing with (and not use Me).

 

[SOS]

also note that the function is in a separate module but part of the code uses 'Me' (myVariable = Me.frm_stub & strFranchise & .Controls(abc)) which refers to a form. the function doesn't know what Me is. the function either needs to be with the form or the function needs to be told which form it is dealing with (and not use Me).

Definitely true.

you could PASS ME along to the function but you can't use ME within the function:

Sample:

Function Sample(frm As Form, sfrm As Control, ctl As Control) As Integer
   Sample = Forms(frm.Name).Controls(sfrm.Name).Form.ctl.Value * variableName
End Function

Calling

Me.MyTextBox = Sample(Me, Me.subformContainer, Me.SubformContainer.Form.ControlName)

But unless I have to manipulate something like the recordsetclone, etc. I will usually use the NAMES instead of the objects themselves:

Function Sample2(strfrm As String, strSfrm As String, strCtl As String) As Integer
   Sample2 = Forms(strfrm).Controls(strSfrm).Form.Controls(strCtl).Value * variableName
End Function

Calling

Me.MyTextBox = Sample(Me.Name, "subformContainerName", "ControlName")

 

[AccessChap]

Thanks for the effort guys! Applying your syntax I have avoided passing variables and just used literals to get the code working asap. My line now looks like this:

If Forms(frm_User_Create).Controls(frm_Job_Selection_CIT).Form.Controls(cbx).Value = True Then
...

This seems to be acceptable syntax because I no longer get the old error or complaints about a form name. Unfortunately I do get a new error on the same line:

Run-time error '438': Object doesn't support this property or method

Damn! So close... Can anyone shed any light on this for me please? I'm examining a checkbox so the .Value property should exist as far as I'm concerned

Thanks,

Andy

 

[AccessChap]

OK, I've got it. Using your line:

If Forms(frm_User_Create).Controls(frm_Job_Selection_ CIT).Form.Controls(cbx).Value = True Then

I changed it to incorporate quotes around the form names and all is well:

If Forms("frm_User_Create").Controls("frm_Job_Selection_ CIT").Form.Controls(cbx).Value = True Then


I guess it was trying to parse the line as if those form names were variables and not literals. Adding the quotes seems to have clarified things for the compiler. I'll now go and parameterise this lot and see what blows up

Thank you to everyone who contributed, without you guys I'd still be stuck on the syntax

 

[wazz]

good news.