Dim Subform as Control -- not working

[painterz]

Hi All,

This worked:

Dim frmNeighborhood As Form
Dim sfrmNeighbors As Control

Set frmNeighborhood = Forms("Copy of Neighborhood Input Form")
Set sfrmNeighbors = frmNeighborhood![Neighbors]

This doesn't:

Dim frmNeighborhood As Form
Dim sfrmFamily As Control
Dim sfrmNeighbors As Control

Set frmNeighborhood = Forms("Copy of Neighborhood Input Form")
Set sfrmFamily = frmNeighborhood![family subform]
Set sfrmNeighbors = frmNeighborhood!sfrmFamily![Neighbors]

Neighbors is the subform of Family Subform which is the subform of Copy of Neighborhood Input Form.

I've been working on this so long with so many variations I've fried my brain.

Where am I going wrong?

Thanks!

 

[gemma-the-husky]

FORM
set frm = forms!formname

(I think your usage
set frm = forms("formname") is equally valid)


SUBFORM
the problem is that a subform is a control on a form.
You need an extra bit of syntax to address the form.
the bit in red below

set subfrm = forms!formname!subformname.form

eg, see this article
http://access.mvps.org/access/forms/frm0031.htm

 

[painterz]

Hi Dave,

The article makes sense if I'm directly referencing the controls but since I'm defining them as objects (I know there's a better term for this), I don't really know how to apply it.

I would think I could substitute my variable names into the reference string. Since frmNeighborhood is already defined as a form, I tried:

frmNeighborhood!sfrmFamily.Form![Neighbors]

but I got an error saying Access can't find a field named 'sfrmFamily' (which is defined as a control).

I also defined sfrmFamily as a form and tried:

Set sfrmNeighbors = frmNeighborhood!sfrmFamily.Form![Neighbors]

but got an error saying Access can't find the 'family subform'...

sfrmFamily is confusing because I know it's a control on the main form, however it's a form to the Neighbors subform (control). Yeah, I'm not sure if that made any sense at all.

This is clear as mud.

Thanks

 

[painterz]

I think this article, http://btabdevelopment.com/easy-way-to-remember-how-to-refer-to-subforms/, gets me closer to the solution. I'm still trying to get it to work. Now I'm getting error messages that my fields aren't being recognized.

 

[gemma-the-husky]

I assume sfrmFamily is the name of the form that is the subform.

The name you want to use is the name of the control on the main form.

so I think this is correct

set sfrmNeighbors = forms!frmNeighborhood!nameofsubformcontrol.Form
set MyControl = Forms!frmNeighborhood!nameofsubformcontrol.Form!somecontrol


so nameofsubformcontrol which may be the same as the formname, but the standard drag and drop does not name it in this way. It might be called something like [neighbourhood subform], although you can rename it.

then .form indicates you want to address the form

and !neighbors is a control on that form.

----------------
in your attempt
forms!frmNeighborhood!sfrmFamily.Form![Neighbors]

this would look for a control on the form frmneighborhood called sfrmfamily, that control being a subform.
it would then refer to that subform, and look for a control on the subform called neighbors.

This may or may not be what you actually have.

If the first subform has a further subform of its own, then you probably need this to refer down the chain

set subsubform = forms!frmNeighborhood!sfrmFamily.Form!Neighbors.form


I hope that makes sense.

 

[painterz]

Hi Dave,

Thanks for hanging in there with me.

Yes, my subform control and subform have the same name. I've shortened it to 'Family' rather than 'Family Subform'.

And yes, I am trying to reference the subform Neighbors that is a control on the subform Family that is a control on the form 'Copy of Neighborhood Input Form'.

I didn't quite follow what you were saying. I tried combining the main form and the first subform together into one object:

Set frmNeighborhood = Forms![Copy of Neighborhood Input Form]![Family].Form

Then set the control:

Set sfrmNeighbors = frmNeighborhood!Neighbors

This seems to work until it gets to the next line:

If sfrmNeighbors!PKRelationship.Value = 3 Then

Access can't find the field PKRelationship--so I think it's looking at the wrong form. Like I said in the beginning, this was working until I brought the Neighbors form into the Family subform. If I can get my references right, then the rest will fall in line.

Thanks

 

[MarkK]

Who is Mike?

 

[MarkK]

Oh, looks like you caught that, cheers,

 

[painterz]

Sorry, fried brain. I made the corrections.

I don't see an embarrassed emoji! That would go here.

 

[painterz]

FYI--I used 'MsgBox sfrmNeighbors.Name' to see what form I'm actually on and it's the Neighbors subform which is where all of my fields are--not sure why my field PKRelationship isn't being recognized.

 

[gemma-the-husky]

maybe it's the .value that isn't needed
also, if it's null, it might fail

try either of these
If nz(sfrmNeighbors!PKRelationship,0)= 3 Then
if nz(sfrmNeighbors.controls("PKRelationship"),0)= 3 then

 

[Galaxiom]

maybe it's the .value that isn't needed

Value is the default Property of a control. When a reference does not specify a Property or Method, that is what is returned. So it doesn't matter if Value is specified or not.

 

[painterz]

Hello Dave and Galaxiom,

This worked before I added the new subform, so I think it's still a reference to the form where the fields are located. I think it has something to do with the setting the variables to objects and then using the object refs in the IF statement. Can you offer any help? Do I need to set the form to an object in order to simplify my IF statement? My IF statement hasn't changed.

Thanks

 

[Galaxiom]

Do I need to set the form to an object in order to simplify my IF statement?

Have you considered using a With construct to simplify the If statement?

 

[painterz]

I haven't tried a WITH construct but I don't think it will take care of my reference problem. Wouldn't a WITH be used in a SQL statement? I'm not familiar with WITH. This is a simple IF THEN ELSE statement showing and hiding fields on a form.

 

[painterz]

I looked up the WITH statement and it is cleaner, however I think I'm still going to have the reference problem. Unless I don't need to declare the form-subform-subsubform.

 

[Galaxiom]

With is used in VBA like this.

With Forms!formname.subformcontrolname.Form
        .somecontrol = whatever
        .anothercontrol = something
 End With

 

[painterz]

WITH doesn't seem to be able to handle an ELSE. So if my criteria isn't one, then it's the other. How do I handle that?

 

[Galaxiom]

When using If blocks the With block must be entirely outside of it.

With whatever
    If ... Then
    Else
        .....
    End If
End With

Could something similar be the problem with setting your references? If you set it in the If section the Else section won't know about it.

 

[painterz]

I'll give this a go in the morning with fresh eyes (and brain). Thanks!