By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Twist on the IIF(condition,true,false) for unbound forms

zmbd
Expert Mod 5K+
P: 5,287
So, here we have a twist on the textbox with a control source of "=Sum(IIF([field]="x",1,0)" as a counter for some given field in a record-source for either a form or a report. This works brilliantly for a form or report when there is a record set that returns records.

Now, if the form is unbound or the underlying query for either the form or the report returns no records, then the textbox, MS-Access 2010, will be blank instead of showing a count of zero. Worse, occasionally this failure will stall the remaining calculated fields from updating.

Where this becomes an interesting puzzle is in the unbound form.
So let’s work with a form where the header has a combo-box that the user can either enter a value or select the same value from the dropdown. This value is then used to build the query based on tbl_events that is then set as the form’s record source and those records are shown in the details section of the form - one of the fields being the [EventID] field from the underlying table.
Underlying table for the query:
Expand|Select|Wrap|Line Numbers
  1.  Tbl_events
  2. [EventID] - PK - autonumber
  3. [AssetID] - FK - Long
  4. [OtherData] - text
Simple select query filtered on the [AssetID] = to the combo-box value showing the [EventID] and [OtherData] becomes the record source for the form, which is then force re-queried and the filtered record show in the detail section
Now let’s add a conditional textbox in the header just below the combo-box that displays a user prompt based on whether or not there is a set of records showing and set the control source as follows:
Expand|Select|Wrap|Line Numbers
  1.  IIF(
  2. Sum(IIF([EventID]>0,1,0))>0,
  3. "Displaying records for assetID=" & [AssetID],
  4.  "Please enter AssetID") 
After the record source is established, say by selecting "9", then "Displaying Records for assetID= 9" appears as one would expect (note that I pull the "9" from the combo-box value - NOT the recordset).
However, when the form is unbound such as when it is first loaded, instead of displaying the "Please enter AssetID" or even "#Error" or something like "#Name?" we get a blank textbox.
I know that there are other ways of doing this, for example, setting the prompt to the "Enter/Select…" and then using the after_update event to change the prompt.
I’ve considered a myriad of rs=me.recordset.clone, rs.recordcount type things; however, they can add a lot of overhead.
One solution is offered via:
http://allenbrowne.com/RecordCountError.html
I was unable to get it to work.

-Z
May 9 '12 #1

✓ answered by NeoPa

Strangely enough, I fell over this same problem very recently.

Normally, a field reference will take the value of the referenced field from the currently selected record. This value may well be no value at all (Null), but that is also handled by IIf() perfectly adequately. What I found was that there are rare cases, an empty recordset which is not updatable, where there is no current record. In such a case the value returned won't be a Null, but some other result which indicates an error (not a VBA error as such) in the data. I suspect this may be waht you are falling over too.

The solution, just as it is for situations where Nulls occur, is to identify those situations and handle them in your code.

PS. I think some quote characters slipped through again in your post but I'll fix them for you.

PPS. May I say how much I appreciate working in a thread where the problem is well laid out and well expressed :-) Good for you.

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,186
My experience is that summing an IIf() call will always return a value as IIf() will always treat a Null value as not True and take the second of the two optional parameters (FalsePart).

I'm not really sure what I'm looking at here, as your post seems to misspell IIf() as IFF() in some places, and your code tags indicate you're using incorrect characters for the quotes. Are these just presentation problems or do they reflect your actual code?
May 9 '12 #2

zmbd
Expert Mod 5K+
P: 5,287
sorry about the "IFF()" vs. "IIF()"... in all cases it was supposed to be "IIF()."

(I'll make those changes)

As for the quotes... just a presentation issue. I typed the post up in Word so I could run the spellcheck over it for typos etc...

:)

Let me try again...

So in the form:
Expand|Select|Wrap|Line Numbers
  1. Forms Header Section:
  2. {ControlName}{Type}{[ControlSource]}
  3. {cbo_AssetID}{combo-box}{[tbl_assets]![AssetID]}
  4. {txt_UserPrompt}{textbox}{[IIF(Sum(IIF([EventID]>0,1,0))>0,"Displaying records for assetID=" & [AssetID],"Please enter AssetID")}
Expand|Select|Wrap|Line Numbers
  1. Forms Detail Section:
  2. {ControlName}{Type}{[ControlSource]}
  3. {txt_EventID}{textbox}{[EventID]}
  4. {txt_OtherData}{textbox}{[OtherData]}
The footer has a single command button to close the form.
The default record navigation is available.

Now, when there is no recordsource assigned (ie; intial open) OR when the the query that is built upon user selection returns no records from the tbl_events (say, I've archived the data for that asset from tbl_events) then IIF(Sum(IIF.....) fails to return anything in MSAccess2010 - you get a blank textbox <%-( !

Let's trouble shoot a bit:
Create another textbox {txt_sum} in either the header or footer and set the control source to {=sum([EventID])}. When the form first opens or if the forms's record source returns no records then the text box shows "#Name?". After the user makes a vailid entry/selection, and there are records returned, then the textbox returns the sum of the [EventID]'s shown in the details section: say results 1 thru 10 so the sum is 55.

It is the the unresolved field name that is causeing the sum() to fail then, subsequently, the nested IIF() also to fail. Now I would have thought in a nested IIF() that the fail on the inside IIF() would have cause the outside IIF() to resolve as false; thus, return the false conditional result - it does not act as expected. Instead, the outside IIF() also fails to resolve. So we have a blank textbox instead of one that asks the user to "Please enter AssetID" or a record count, should one want that for some reason... will be blank instead of 0.

Now this happens in MSAccess2007 and MSAccess2010; however, I don't seem to remember this happening in MSAccess2003 but I didn't do a lot of this type of counter/prompt in the earlier versions if it did so in those versions, then I wasn't concerned about a blank result - just the numbers so I didn't notice it (no records, no count, no problem!)

-z
May 10 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
Strangely enough, I fell over this same problem very recently.

Normally, a field reference will take the value of the referenced field from the currently selected record. This value may well be no value at all (Null), but that is also handled by IIf() perfectly adequately. What I found was that there are rare cases, an empty recordset which is not updatable, where there is no current record. In such a case the value returned won't be a Null, but some other result which indicates an error (not a VBA error as such) in the data. I suspect this may be waht you are falling over too.

The solution, just as it is for situations where Nulls occur, is to identify those situations and handle them in your code.

PS. I think some quote characters slipped through again in your post but I'll fix them for you.

PPS. May I say how much I appreciate working in a thread where the problem is well laid out and well expressed :-) Good for you.
May 10 '12 #4

zmbd
Expert Mod 5K+
P: 5,287
Ok,
When the query returns no records, I notice that the form's default navigation controls become unavailable; thus, the form "knows" there are no records... is there a way to track the state of the navigation controls? Been looking for the past two days without any glimmer of an answer.
May 12 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
zmbd:
is there a way to track the state of the navigation controls? Been looking for the past two days without any glimmer of an answer.
Not that I know of. However, there is a fairly easy way to determine reliably if the recordset is empty, which is to check for both BOF and EOF. If both are true then the recordset is empty.
May 14 '12 #6

zmbd
Expert Mod 5K+
P: 5,287
Checking for the BOF/EOF in vba is easy... I've never tried to do that in an IIF().
-z
May 14 '12 #7

NeoPa
Expert Mod 15k+
P: 31,186
Sorry Z. IIf() is used both in VBA and SQL (and control and object properties are essentially SQL based) and I don't always remember every detail of a thread when I come to revisit it. I don't know of an easy way to check for that within SQL :-(
May 14 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.