473,322 Members | 1,562 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

zmbd
5,501 Expert Mod 4TB
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.

7 4578
NeoPa
32,556 Expert Mod 16PB
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
5,501 Expert Mod 4TB
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
32,556 Expert Mod 16PB
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
5,501 Expert Mod 4TB
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
32,556 Expert Mod 16PB
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
5,501 Expert Mod 4TB
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
32,556 Expert Mod 16PB
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

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

Similar topics

16
by: Terry | last post by:
Hello, I am designing a form and need to know how to get a Check Box(PASS)to automatically update when the data in a Text Box (MARK), in the same form, is >=24. Using the Event dialogue box for...
1
by: ilushn | last post by:
I had this figured out a few days ago, then Access shut down and I lost everything & can't remember how I did it. I have a check box (ConsultationSkillsWorkshopFee) with an unbound field...
1
by: Terri | last post by:
I have an A2K database with linked tables to SQL Server 2000. I am trying to use an iif statement in a query. IIf(!! Is Null, "*",!!) This will return records when !! is not null but will...
7
by: Bill Reed via AccessMonster.com | last post by:
I have a field called "Wiring" in a query which is boolean. If I place "True Or False" in the criteria for the field, I get all records (there are no nulls in the recordset). Likewise if I place...
0
by: Zenobia | last post by:
This code, for a page control, is supposed to print a gif (for each new year, followed by the text of each year). It doesn't do that. It is just a simplified version of another control used for...
30
by: Jason | last post by:
I am fairly new to ASP--I have been using it about 2 months. I did these tests (below), and it doesn't make sense to me. False is equal to 0, and that's fine. True should be equal to 1, but it's...
4
by: mukesh | last post by:
One another problem I have used a formula in the calculated field of a query as - IIf( <=, (IIf(<=, , ), (IIf (<=, , )) the above formula is working properly to give the lowest of , and ...
1
by: Regnab | last post by:
I've got a form where the user can edit the lookups available in the database. It consists of a list box of the various categories on the main form, a checkbox on the main form and a sub form which...
8
by: Stinky Pete | last post by:
OK, I am thoroughly confused. All I am trying to do is fill in a Status field on my form with the condtions below, with all my theory being based on what I have read in various newsgropups. The...
7
by: Maurizio Colucci | last post by:
Hello, Is there a way in VB.NET to do something like dim myInt = if foo() then bar(3) else 4 and have the compiler infer that myInt is an Integer, since bar() returns an integer and 4 is an...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.