show msg if subfrm is not null

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.

0
Utf
11/21/2007 4:27:01 AM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
638 Views

Similar Articles

[PageSpeed] 13

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.




0
Utf
11/21/2007 11:54:00 AM
> 
> 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!
0
Utf
11/22/2007 3:34:00 AM
A useful way of testing for Null or Zero / Null or Blank is to use the NZ 
function.

"miss031" wrote:

> > 
> > 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!
0
Utf
11/22/2007 11:34:00 AM
Reply:

Similar Artilces:

ranges not showing on chart
i have 3 ranges on my chart, being the 'relative', average and 'jps'. i have adjusted the 'relative' as i am using the first third and last third of the values instead of all of them by selecting them w the mouse and holding CTRL, and now the average and 'jps' ranges are not showing. these values are all close to the 'relative' so shouldnt be above the graph, and i have checked that the values are in the cells i have sourced the data from. i cannot think of whatelse i am missing thanks everyone ...

Send on Behalf not showing
Hi I hav exchange 2003 and xp pro.One user(piet) have rights to send on behalf of (koos) another but in the From: field is the name of piet displayed. they want the follwing to be displayed: From: Piet send on behalf of Koos. i did give piet send on behalf rights and removed the tick next to "send as" under the security tab of both users.. what else i'm i missing.......thanks Craig from Huguenot <CraigfromHuguenot@discussions.microsoft.com> wrote: >I hav exchange 2003 and xp pro.One user(piet) have rights to send on behalf >of (koos) another but in the From: ...

IIF
I,m using two bound text fields to create a thrid unbound field they are: 2 bound to same table - [contractnum] and [fmscase] - both are text, format = >, required = no, allow zero lenght = no, indexed = yes (dups ok). The 3rd unbound field is [contract or fms no.] in it's control source I'm using the following: "=IIF([contractnum]=null,[fmscase],[contractnum])" The above simply doesn't work and I'm not sure why. The goal is to have the 3rd field show either the contract no. or the fmscase no (one or the other) - if there is a contract no there will not b...

follow up folder does not show flagged email
according to the help screens the follow up folder that comes with outlook 2003 should show all items flagged for follow up. mine does not. anybody know why and what i can do about it? ...

Not showing blanks for 1 out of 2 series in a chart
I have understood the use of NA(), but since I also want to show the data table, it is not a nice looking solution. I have a monthly chart showing several years for comparison, but I do not want the last month for current year to drop down to zero. How can I avoid this without showing #N/A in the data table? ...

money 2002 show aust banks etc #2
how do i get money 2002 to show australian banks on it. and reflect australian tax implications? You need the Australian version of Money. There are no retrofits. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny UK Wishes/Suggestions mnyukwsh@microsoft.com "mac" <janeclay@tpg.com.au> wrote in message news:0a5601c380ff$f0ceb400$a101280a@phx.gbl... > how do i get money 2002 to show australian banks on it. > and reflect australian tax implications? > ...

Still showing numbers not text in display
Since my previous post I went through both tables and did the lookup wizard on all the fields and selected only the RATING not ID. When I pull both tables into my query it shows the number for some and the word for others. The frustrating part is they were all created the exact same way so why are they displaying differently? I checked all the properties on both tables and everything is exactly the same and the information is still displaying in 2 different formats. Help before I pull all my hair out... =( Thanks for the quick responses by the way! Also... When I pull each...

Show formulas instead of results
I am trying to have the actual formulas show up on my spreadsheet, instead of the results. I think I have done it before, but I can't remember now. Thank you Tools>Options>View, check Formulas -- Kind regards, Niek Otten "Hippopotamus" <Hippopotamus@discussions.microsoft.com> wrote in message news:4248D92C-72E8-462D-B68C-71B3052E40DC@microsoft.com... >I am trying to have the actual formulas show up on my spreadsheet, instead >of > the results. I think I have done it before, but I can't remember now. > > Thank you goto tools and option...

Show 'incomplete' axes & show ticks but NOT axes?
I've been reading Edward Tufte's "The Visual Display of Quantitative Information" (which, by the way, I can whole heartedly recommend to anyone with a personal/professional interest in statistical graphics). Anyway, I'd love to implement some of his ideas. For example - using a chart's axes as a 'range frame' - so that each axis is only shown from the minimum to maximum of both variables plotted . Meaning that the axes themselves carry information . Does anyone know if or how this could be done in excel? An add-in maybe? Also, things like showing the tick ...

How to show & print gridlines in .htm from .xls.
I am saving an excel worksheet as filename.htm The worksheet has gridlines turned on, they show and print in filename.xls but not in filename.htm Any way to have the gridlines show and print in filename.htm? Anti-Spam: Change shellyfnospam to shellyf to respond. Thanx, y'all have a nice day! Retired to Cyberspace from Green Valley, AZ. Same answer as when you asked the exact same question yesterday. check it out at the archives: http://google.com/groups?&as_uauthors=shelly%20f&as_scoring=d In article <4043d4ff.1360416@news.west.cox.net>,...

Items in catalog aren't showing up on Business Portal
I am add items to the Fabrikam list in GP and using Catalog Manager to add them to a Department. Then I am making them active in Catalog Manager. But they aren't showing up in Business Portal. I am able to add new Departments and the new Departments show up on the Order page and when I make an existing item active within the new Department, that item shows up on the order page. But if I add a new item to GP (or copy an existing one to a new Item ID) and then try to either add them to my new Department or add them to an existing Department, they do not show up as something that can ...

Smartlist not showing all Extender Fields
I created an extender window to add additional information on customers that we want to report on. I was able to add the new fields into the smartlist using the smartlist button on the bottom of the extender window. The problem I have now is that I had to change the names of some of the fields and delete one of them. The new fields don't seem to be showing up in Smartlist. I even tried deleting the link and recreating it and still the new fields are no where to be found. Any ideas on how to fix?? Thanks, Dawn ...

Purchased, but still shows Trial
Please help my with my frustration. I'm sure there's something simple I'm missing but cannot find it. I've been running Money 2005 for a while. I purchased it online which created something called a Money 2005 Service which I can see when I log into Microsoft Billing. I decided to try Money 2006. I purchased it and downloaded it and installed it on a different computer. I tried it out and decided I wanted to use it for all my finances. I uninstalled it from that computer. I ran the downloaded file on my original computer, and it uninstalled Money 2005 and installed Mon...

C field to show the expected amount of time to be spent on a task
What is the formula for a custom field to show the expected amount of time to be spent on a task on a particular day? Can anyone help me for this? Thillai -- You need to examine the assignment Work field in either the Task Usage view or Resource Usage view. If you apply the Task Usage view and zoom the timescale to Weeks Over Days, you will see the assignment Work field in the right half of the view (the timephased grid). You can examine the Work assigned to a resource on any day. Hope this helps. -- Dale A. Howard [MVP] VP of Educational Services msProjectExperts ...

Using Powerpoint 2007
PPS files on webpage open as PPT using PowerPoint 2007. Don't have this same issue when opening the same link from an Office 2003 computer, they run the show only. -- Miroshak ...

slow e-mail small envelopes showing up on task bar
Just started having problems with send and receive of my e-mails. When connecting now I get small white envelopes in the task bar that expand and contract for each e-mail. It now takes forever to receive my mail. Somebody help please!!!!! ...

Show value, not formula in formula bar
We have a client that does not want to see the formula in the formula bar. I decided to protect the excel report and hide all formulas, but that is not good enough. They still want to be able to see the value in the formula bar. Anyone have any idea how this can be done? How about selecting the entire sheet and convert all formulas to values ? If this is palatable, .. : Press Ctrl+A to select entire sheet, then Right-click > Copy Right-click > Paste special > Values > OK -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "Deroc...

Query to show record plus previous three
I'm sure there is a simple solution to my problem, but it evades me at the moment. I need a query that will pull an attendance record for a specific student in a specific course for a specific week (parameters set by form combo boxes) that will also display the three attendance records for the same student/course for the previous three weeks. Lets say a teacher selected a student named John Smith in Grade 12 Chemistry for week number 10. The form would display that record from the underlying attendance table and also the number of hours and any comments recorded for John Smith in ...

showing dates, e.g. 1st, 2nd, 3rd with st, nd, rd in superscript
I've recently started using Word 2007. I prefer to write dates in the form 1st January, rather than January 1. My previous edition of word always automatically gave st, nd and rd as smaller font superscripts or suffixes. I can't find a way of getting Word 7 to do that as a default. See the Date Fields with Ordinals section of the following page of fellow MVP Graham Mayor's website: http://www.gmayor.com/formatting_word_fields.htm -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid p...

Scrolling to show sub-folders when opening folders
Every other version of Outlook I have had scrolls up or down to show all the subfolders in an opened folder. 2003 is inert. If you open a folder at the bottom of the window, all the subfolders become "visible" off the bottom of the screen, and you have to scroll down manually to find them. When filing 100 or more e-mails per day, this wastes an extraordinary amounbt of time. PLEASE, is there any way to regain the 'auto-scroll-into-visible-window' feature Outlook has always had in the past? ...

Scanpst shows errors
WinXp, Office2003 both updated, 2003 format pst of 34mb Suddenly OL2003 has started giving problems, ie switching between folders or msgs is slow to display Scanpst shows errors, which it fixes however the problem reoccurs, over a period of time, and errors are again shown. There is only the OLbackup addin. Scanpst log file shows; ??Couldn't find BBT entry in the RBT (6CF618) - repeated for some 80 addresses followed by *Attempting to recover all top-level objects !!Folder invalid high-water-mark (nidi=5E94, nidiHigh=5EC7) !!Search folder invalid high-water-mark (nidi=5EA7, ...

not all records show on form
I have a form that I am using to update some client information. The table I am updating using a clientCode and a contactID to interact with other tables relationships. So the form I have created gets the client code from this table and then gets the contact information using the contactID relationship to the table with these contacts. so it is a pretty simple form, but for some reason not all the records from the first table shows up. there are 220 records in that table but only 175 show up, and there are no filters on the form. Any thoughts? Thanks sounds like you don't hav...

show real time weather in a power point
Is there a way to insert a real time weather forcast during a power point presentation? the weather information can be from a public web site. Sorry, but this newsgroup is for questions about Access, the database product that's part of Office Professional. You'd be best off reposting to a newsgroup related to PowerPoint. When you do repost, it would probably help the responder to know what version of PowerPoint you're using, and perhaps what operating system. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "szh8866" &l...

Unreconcied Transactions not showing in a Cash Transactions #2
HI, I am using Money 2006 Premium. I am downloading all my investments transactions from Broker. They all marked as (E) transactions. But a coupel of times I found missed transactions for stock options and add them manualy to Investments Transactions, but these transactions marked as ( ) - unreconciled and not showing up in Cash Transactions and it make my cash ballance incorrect. I already trryed manualy make Make As.. (C) or (R) but they still not showing in Cash Transactions. How can I make them show up there and fix my Cash balance proble? Thank you. Sam -- Sam. In microsoft.publi...

Manipulating Field to show data differently that entered
I am working in a venue tracker database and I have a look up box in the header, searching for a venue name in alpha order. The problem is, we have many venues that start with "THE," so searching is not as easy if you don't remember that "the." How do a break apart the field to show like so: "Restaurant One, The"? Thank you for any help! -- Message posted via http://www.accessmonster.com Create a query based on your Venue table. The first field will be VenueID. In the second field, put the following expression: RevisedVenueName:IIF(Left([VenueName],4) ...