473,704 Members | 3,112 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using combo-box as a user filter - trying to use format to display text "all" for null values

This is something I've done plenty of times in '97, but I can't seem to
get it to work correctly in Access 2003. Say, for example, I have a
form with an unbound combobox, the data source is a table of customers,
first column is the key field, which is hidden with a 0" width, and the
second column is the customer name. The SQL is a union query that also
inserts Null in the first column, and "All" in the second column. That
combobox gets used as criteria for a query, such that a value entered
by the user restricts the results to records that are related to that
customer, and choosing "All" results in that combobox having a null
value, which I use in the query to return records irrespective of the

Functionally, it works fine in 2003 as it did in '97: "All" shows up
in the combobox as a possible selection, and when it's selected, the
comboboxes value gets set to Null, which I use in the query to not have
the customer ID be a criteria in the results. The problem is in the
display: in '97, I could set the default value of that combobox to
Null, and the format of that field to @;"All", which would
automatically display "All" in that field when the form was opened,
showing that as a default, all customers would be returned. In 2003,
the combobox is blank. The value of the combobox is null, as it should
be, but the word "All" doesn't show up in that field until the user
pulls it down and selects All. I even have other controls that
programatically reset this combobox to Null, and in that case, it also
doesn't work: it is simply a blank display.

In short, the combobox will not display "All" on a null value UNLESS
that value was selected by the user. How do I get the combobox to
display a pre-defined string when the value of that control is Null,
even when the value wasn't set to Null directly by the user by
selection within the combobox?


Nov 13 '05 #1
3 4043
I'll just add, I've implemented a work-around: rather than using Null
as the value in the query's criteria to indicate that that field should
not be used to restrict results, I'm using -1. -1 is an impossible
value for a valid customerID field, as it's an autonumber, so in this
case I can use that just as easily as Null. I'd still, however, like
to understand why this doesn't work in 2003 like it does in '97.

Thanks again!

Nov 13 '05 #2
Yes, the first method listed there (using a union query), is exactly
what I'm doing to get "All" into the list of choices for the user.
When they select "All", the value of the combobox is set to Null, as it
should be. The problem I'm having is that I'm hiding the first column
(it's width is set to 0), and therefore what is displayed in the field
when the box is collapsed is the value of the second column in the row
source (in this case, either All, or the customer's name, corresponding
to either Null, or the customer's primary key field being stored as the
value for the control). If they select "Company ABC" in the drop down,
"Company ABC" remains visible in the combobox when it's collapsed, just
as it should. If they select "All" in the drop-down, "All" remains
visible in the control when it's collapsed, as it should. If I set the
value of the combobox control to 1 (either through code, or by setting
the default value of that control to 1 then opening the form), and 1
happens to be the primary key ID for "Company ABC", then "Company ABC"
is shown in the value of the combobox when the form wakes up, or when
the value is changed programatically , just as it should. In other
words, since the second column of the combobox is the first visible
field, when the value of the combobox is set programmaticall y, it
automatically displays the coresponding value from the second column.
This is what is NOT working with Null values: if the value of the
combobox is set to Null, it is NOT automatically displaying "All" in
the collapsed combobox, even though "All" is the corresponding value of
the second column to a Null value in the first column, as per the union
query as described in your link.

If I remember correctly, Access '97 behaved in a similar manner, in
that the second column wasn't automatically pulled in if Null was the
value of the first (bound) column. But in '97, I was able to work
around that by setting the format property to @;"All" (or possibly
;;;"All" with numerics, can't remember for sure). But using the format
property to display "All" for Null isn't working when the control is a
combobox with a row source such as this.

Perhaps my "new" workaround is simply the new solution to this in 2003,
but I'm still curious to see what others may have to say. :)

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Nils Benson | last post by:
Here's a strange problem: I have an arbitrary query that has calculated columns in its output. It could be a "SELECT TOP 1 * FROM ...", or a "SELECT STDEV(somefield) FROM ...". It doesn't matter what the exact select is - just that the data is NOT straight from a table, but is calculated. Anyhow, when I run the queries in the query analyzer, everything works as expected. When I make the calls via SQLExecuteDirect, I get back the correct...
by: Leila | last post by:
Folks I have an html file which looks like this: .. .. .. <body onLoad="WindowOnLoad();"> .. ..
by: YM | last post by:
Hi, Need help making all values in the entire database negative regardless of what number is put in. If it's a VBA line, please indicate where I write it :) Thanks,
by: Jim in Arizona | last post by:
This doesn't make any sense to me. I'm hoping some SQL guru out there knows the answer. I'm making a help desk type database system. There's only two tables, one called TTickets and the other called TNotes. The Primay Key of TTickets is a one to Many to the Foreign Key of the TNotes table. In my VB code, I create a relation between the two tables so I can display several notes (from TNotes) with a single entry in the TTickets table. So...
by: Ed | last post by:
All of a sudden my previously working code started throwing this error. from the SqlDatasource. I am using C# and Asp.net 2.0. Getting the following error: You have specified that your delete command compares all values on SqlDataSource 'sdsAddEdit', but the dictionary passed in for values is empty. Pass in a valid dictionary for delete or change your mode to OverwriteChanges.
by: saddy | last post by:
hello i m using seagate crystal report and then i will use the same report in my vb.net application In the report i want to show the values of the table that was built in the SQL server 2000 , a table that has 15 fields (columns) and some 10,0000 records ,the problem is that when we graphically view our fields most of them are missing please can anybody tell me how could i extend the graph so that we can easily view all of our records...
by: Yakov | last post by:
What would be the nicest way to write the loop for all values of unsigned short (0..0xffff), usnig 'unsigned short port;' as an index ? For comparison, there is just one way to write the for(k=0; k<1000; k+ +) loop, but there seems to be several subtle ways to enumerate all 0..0xffff values using ushort. And no, for(port=0; port <PORT_MAX; port++) {} is not one of them. Yakov
by: =?Utf-8?B?a2V2aW4=?= | last post by:
using VS2005 My masterpage has two panels and two hyperlinks. The initial content aspx page is blank. The page_load event of the masterpage hides one of the panels based on SESSION variables. When I press one particular link all controls are null, thus throwing an error. The other hyperlink behaves normally.
by: Roget | last post by:
Using a DetailsView and SQLDataSource controls I am passing three values through an InsertCommand into a SQL Server database. <asp:SqlDataSource ID="AP_ListData" runat="server" ConnectionString="<%$ ConnectionStrings:SCMDConn %>" InsertCommand = "INSERT INTO tblPartMaster(AP_ID, PartName, PartNumber)VALUES(@APID, @PartName, @PartNumber)" > <InsertParameters> <asp:Parameter Name="APID"...
by: himanshupancholi | last post by:
Hi, I need to Copy all values of a column from one table to another. Below are the details: Source: STL_GRP table, VEND column Destination PARTNER table, VEND column. I am using the below query which is incorrect: UPDATE PARTNER SET VEND=
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.