Connect with Expertise | Find Experts, Get Answers, Share Insights

How do I include a blank option in a listbox bound to an optional field?

 
Join Date: Mar 2010
Posts: 4
#1: Mar 19 '10
I am using MS Access 2007 on an XP platform. I have forms (split and single) with listboxes. If a listbox is bound to a field that can contain a null, I would like the listbox to include a blank entry at the top of the list so it is readily apparent when the bound field contains a null and so that the user can deselect a value and revert to a null value. I would also like the listbox to display the entry that corresponds to the value of the bound field, whereas it currently just displays the first entry.

I don't know if this is relevant, but one listbox is bound to a field, that stores a number, which is a foreign key to another table that contains a second field, which is the description. So the row source is actually a query that displays all the descriptions - here is the sql code:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblPHAs.Name
  2. FROM tblPHAs
  3. ORDER BY tblPHAs.Name;
A second listbox is similar, except the query contains an expression:
Expand|Select|Wrap|Line Numbers
  1. SELECT [First Name] & " " & [Last Name] AS Expr2
  2. FROM tblScreeners
  3. ORDER BY tblScreeners.[Last Name];
Finally, when I modify values using the form, the changes are not reflected in the bound table. Yet the property sheet for the form indicates that Allow Additions/Deletions/Edits are all Yes.

 
Join Date: Mar 2010
Posts: 4
#2: Mar 19 '10

re: How do I include a blank option in a listbox bound to an optional field?


I actually figured out what part of the problem is: I was not including the "bound" column. I have changed my queries and the form properties so that the bound column (i.e., the ID) is selected and I am keeping it hidden by setting that column width to 0, but I still have the main problem of providing a choice in the listbox that corresponds to a null value in the bound field.
NeoPa's Avatar
E
M
C
 
Join Date: Oct 2006
Location: London - UK
Posts: 18,893
#3: Mar 20 '10

re: How do I include a blank option in a listbox bound to an optional field?


I think you'd need a UNION query if the data source doesn't contain the empty value you require :
Expand|Select|Wrap|Line Numbers
  1. SELECT   TOP 1 
  2.          Null AS [Name]
  3. FROM     [tblPHAs]
  4. UNION
  5. SELECT   [Name]
  6. FROM     [tblPHAs]
  7. ORDER BY [Name]
 
Join Date: Mar 2010
Posts: 4
#4: Mar 22 '10

re: How do I include a blank option in a listbox bound to an optional field?


I tried including that code and get the following error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing or the punctuation is incorrect.

Here is exact code:
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1
  2. NULL AS tblPHAs.Name 
  3. FROM     tblPHAs 
  4. UNION 
  5. SELECT tblPHAs.ID, tblPHAs.Name
  6. FROM tblPHAs
  7. ORDER BY tblPHAs.Name;
NeoPa's Avatar
E
M
C
 
Join Date: Oct 2006
Location: London - UK
Posts: 18,893
#5: Mar 22 '10

re: How do I include a blank option in a listbox bound to an optional field?


Well, you've changed the question fundamentally haven't you.

You could try :
Expand|Select|Wrap|Line Numbers
  1. SELECT   TOP 1 
  2.          0 AS [ID],
  3.          Null AS [Name]
  4. FROM     [tblPHAs]
  5. UNION
  6. SELECT   [ID],
  7.          [Name]
  8. FROM     [tblPHAs]
  9. ORDER BY [Name]
but I doubt it'll help too much as this is quite a different situation and you're almost certainly trying to use the ID value from the results. Any such code would need to be changed to make sense.
 
Join Date: Mar 2010
Posts: 4
#6: Mar 22 '10

re: How do I include a blank option in a listbox bound to an optional field?


I really appreciate your help, but I haven't really changed my question - just the code, as obviously I'm not clear on how to achieve my goal, which is:

1) to have a listbox display the name that corresponds to a stored number and, in the case of a null value, display a blank entry;and
2) to changed the value of the bound field to correspond to the listbox entry selected by the user.

The new code gets me much closer, but here's the remaining problem. I'm using a split form. As I cycle through the records, if the first record contains a null for PHA, the listbox displays a blank, as it should, But once I display a record with a non-null value for PHA, any subsequent records containing a null for PHA display the name that corresponds to the value of the last record with a non-null value. How do I correct this? Thanks again for your help!!!!
NeoPa's Avatar
E
M
C
 
Join Date: Oct 2006
Location: London - UK
Posts: 18,893
#7: Mar 22 '10

re: How do I include a blank option in a listbox bound to an optional field?


@sartemieff
Was there any reference to use of an ID in your original question. I must have missed it.

I will need to review the new situation a little later as I need to rush now.

If I find I need more info to fit the extra pieces into the picture I'll ask, but there may be all the information I require as far as I know just now. I'll know better when I come to look at it again with some more time available.
Reply