show msg if subfrm is not null

[miss031]

I have a form with an unbound subform that is bound to a particular form
depending on which selection in the control group on the main form is chosen.

When the subform is bound to one form, and it loads, I have a form that pops
up as a message box, but I would like this message box to only pop up if a
subform on *that subform* has a record. Confused yet?

Main form: ""add_new_all"
control group on main form: "frame_choose_sub"
option chosen: "btn_contact_info"
subform control on main form: "subf_main_blank"

When the option is chosen, the 'blank subform' control loads with the form
"subf_main_contact_info".

On this form is a subform control: "tbl_short_memos subform"
which has a combo: "cbo_short_memo_message"

The contents of the combo will be displayed in the pop-up message box when
the contact info form is loaded. If the combo is blank, I would like to *not*
display the pop-up.

I can only get it to pop up *every* time the form is loaded, and when the
combo is blank, it just shows a blank message box.

I tried the code in Form_Load and Form_Open, and now I havethe following in
the AfterUpdate of the control group (frame_choose_sub):



Dim StrMsg As String
Dim strWhere As String 'added
strWhere = "contact_ID = " & Me.contact_ID

If Frame191 = 1 Then
Me.subf_main_blank.Visible = True
Me.subf_main_blank.SourceObject = "subf_main_contact_info"
Me.subf_main_blank.LinkMasterFields = "contact_ID"
Me.subf_main_blank.LinkChildFields = "contact_ID"
Me.subf_main_blank.Form![Dummy] = 1

If Not IsNull(Me.subf_main_blank.Form![tbl_cont_short_memos
subform]!cbo_short_memo_message) Then

DoCmd.OpenForm "short_memo_box", acNormal, , strWhere

Else: DoCmd.CancelEvent

End If



Please help! Thanks.

 

[Rod Plastow]

Miss031,

Confused yet? I'm totally bewildered; "... an unbound subform that is bound
....", but I think I see what you mean

May I reiterate what I believe you want so any future help is relevant but
first I'm going to be somewhat pedantic. There is a difference between the
control on the main (parent) form that contains/displays the subform and the
subform itself. It is important to be clear about this distinction. Many
(most?) Access designers will happily refer to either as the subform and this
can lead to confusion, especially with simple souls like me.

You are dynamically assigning different subforms to your subform control
according to selections made (or values present) on your main form by
changing the .SourceObject property. You are then linking or binding the
chosen subform to your main form through the .LinkMasterFields and
..LinkChildFields properties. So far nothing untoward.

One of these subforms itself contains a subform control displaying yet
another subform. This last subform is at level 3 counting the main form as
level 1. I trust the level 3 subform is static and not dynamically
reassigned.

You want to detect when a control on the level 3 subform is non blank such
that a message pops up that includes this non blank value. In other words
you want to retrieve this value and display the message if it is non blank.

Since your code is assiging the level 2 subform you know whether you need to
proceed or not. If it is the level 2 subform of particular interest then
simply interrogate the control on the level 3 subform.

There's more than one way to do this and the way of direct addressing is one
I use least often so forgive me if I get the syntax wrong. It's something
like:

Me.[MySub1].Form![MySub2].Form![MyControlOfInterest]

where [MySub1] the the subform control on your main form and [MySub2] is the
subform control on the level 2 subform. (I think periods will also work
instead of exclamation marks.) Simply assign the above to a local variable
(you don't want to keep typing that string in over and over again), make your
tests on that variable and take the appropriate action.

The only doubt I have concerns timing. If you try to interrogate your
control before Access has completed loading the subforms then you may get an
error or unpredictable results. I don't think this will happen.

I hope this helps.

Rod

PS What are the other ways of solving this requirement? The way I use most
often is to declare form object variables in my main form declarations and
then set (instantiate) these object variables as my subforms load. This
avoids that cumbersome syntax and makes the code more readable. Another way
is to use User Events that requires the level 3 form to broadcast an event as
it loads (or 'On Current') to any object that is listening - in this case the
listener is my main form - but you need to filter out the spurious events
while it synchronises itself with the main and level 2 forms.

 

[miss031]

The only doubt I have concerns timing. If you try to interrogate your
control before Access has completed loading the subforms then you may get an
error or unpredictable results. I don't think this will happen.

PS What are the other ways of solving this requirement? The way I use most
often is to declare form object variables in my main form declarations and
then set (instantiate) these object variables as my subforms load. This
avoids that cumbersome syntax and makes the code more readable. Another way
is to use User Events that requires the level 3 form to broadcast an event as
it loads (or 'On Current') to any object that is listening - in this case the
listener is my main form - but you need to filter out the spurious events
while it synchronises itself with the main and level 2 forms.

Thank you for the ideas. I found out that when there is no message, the
control is not Null, it is actually zero, so the pop-up will always appear.

Also, I moved the event to the sub-sub-form's on_Current event, runnning it
when the control <> 0, and it works like a charm!

Sometimes all it takes is a set of fresh eyes! Thanks!

 

[Rod Plastow]

A useful way of testing for Null or Zero / Null or Blank is to use the NZ
function.