Code that Refers to a Field (Control) in a Subform

[Sa'El]

Hi,

I am not new to Access but I am new to VBA programming starting this weekend, but have made decent progress. Simultaneously I would like to hide/unhide a subform based on a checkbox. Additionally, if there were values in the subform, when it is hidden again I want the values in the subform to be null. For the record, I have been successful with the visible/invisible part and to make values null on the main form, but I haven't been able to reference the subform controls to make null.

Here's the code

Private Sub KCCQ_AfterUpdate()
If Me.KCCQ = -1 Then
Me.D_1_KCCQ_12_Form.Visible = True
Else
Me.D_1_KCCQ_12_Form.Visible = False
'***Insert Code Here*** = Null


End If
End Sub


Also, "D_1_KCCQ_12_Form" is the name of the subform container, not the subform name in the navigation pane.


Again, the above code the astrerisk row works fine. I have tried "Me.D_1_KCCQ_12_Form.Form._____ = Null", but it's not right, and not surprisingly the "Complete Word" in the VBA editor doesn't list the control names to choose from (but other objects).

Any help would be appreciated. Thank you.

[June7]

What do you mean by 'make null'? - move to a new record row? Is this an UNBOUND form?

Me.subformcontainername.controlname = Null

An alternative I use is to put the code to set each control value in a Sub procedure behind the subform then call the Sub from the main form:

Call Me.subformcontrolname.Form.Subname

Say the subform container control is named ctrDetails and the Sub procedure is named SetNull:

Call Me.ctrDetails.Form.SetNull

[Sa'El]

Thanks for the reply, June.

Make Null as in clear the values in the subform, to keep the over-arching logic intact. Essentially, if the subform is hidden/not enabled, there shouldn't be any values there. The "____ = Null" covers the instance where the user initially enters data in the subfrom by revealing it, but for whatever reason, changes their mind.

When I use the "Me.subformcontainername.controlname = Null", it doesn't work. After the "." before the controlname I see properties like "add colon" and none of the controls are visible. Do you or anyone know why this is the case?


As for the alternative, although I understand logically what your doing, I don't know enough to execute that except with a lot of trial and error (unless I am pointed to/provided an example). I am of course willing to learn (which is why I am doing VBA in the first place, I am curious as to why this isn't working. I am thinking that this is a really simple thing I'm missing (syntax issue).

[June7]

But is this an UNBOUND form/subform? If BOUND, why would you want to 'clear' (delete data) a record?

My alternative is quite simple. Code in a Sub procedure of the form that is used as subform:

Sub SetNull()
Me.controlname1 = Null
Me.controlname2 = Null
End Sub

Then call the Sub from main form as described.

The VBA intellisense won't see the subform's controls/fields from the main form module, just the way it is.

[RuralGuy]

Here's a good link to bookmark: http://access.mvps.org/access/forms/frm0031.htm

[Sa'El]

RuralGuy,

I've had that bookmarked, and in fact was using that reference before posting this thread, so thanks.


The form is Bound as far as I understand it (Parent/Child linked fields are set), but I may have to explain the logic behind the form itself:

• Users will enter this record multiple times (important)
• Say user enters the record and marks the Subform as visible (by clicking the check box) and enters the data and closes the form
• Then let's say the user changes his/her mind about the data entered in subform (or was placed in error), thus the subform data should be purged
• The user reenters the form and unchecks the field that is responsible for the subform being visible
• Logically, if the subform is not visible, then there shouldn't be data there, but since the data is already there due to prior entry and is being corrected, I needed to add the =Null code to ensure that there is no data there as the user may not clear the data manually. I would have used the "enabled" property, but for some reason when a subform is not enabled it doesn't have a greyed-out look, which is an important visual component that I use in my database

I hope that makes sense.

If the intellisense can't call to a subform's controls from the main form, then this simply can't work with the method I was using as the syntax was correct then.



I just attempted to implement your method, and it worked (and rather beautifully, I might add!). Thanks for taking the time to write it out. Even more so, thanks for expanding my knowledge base of VBA programming. This is a nifty feature I'll be utilizing, June.

Thanks.

[June7]

Even though intellisense won't list the subform's controls within main form module, the syntax will work. Just have to know the control/field names and type them out correctly. I was originally doing this in my code and changed to the alternate approach. Glad it worked for you.

[Sa'El]

I'm not sure if you'll read this, June, but it now occurred to me this morning why the "= Null" for the subform may be confusing. The subform's table is a one-one relationship (to normalize), so for each record there will only be one associating record in the subform.