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

[sartemieff]

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:

SELECT tblPHAs.Name
FROM tblPHAs
ORDER BY tblPHAs.Name;

A second listbox is similar, except the query contains an expression:

SELECT [First Name] & " " & [Last Name] AS Expr2
FROM tblScreeners
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.

[sartemieff]

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]

I think you'd need a UNION query if the data source doesn't contain the empty value you require :

SELECT   TOP 1 
         Null AS [Name]
FROM     [tblPHAs]
UNION
SELECT   [Name]
FROM     [tblPHAs]
ORDER BY [Name]

[sartemieff]

I get an error when I use that code...

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:

SELECT TOP 1
NULL AS tblPHAs.Name 
FROM     tblPHAs 
UNION 
SELECT tblPHAs.ID, tblPHAs.Name
FROM tblPHAs
ORDER BY tblPHAs.Name;

[NeoPa]

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

You could try :

SELECT   TOP 1 
         0 AS [ID],
         Null AS [Name]
FROM     [tblPHAs]
UNION
SELECT   [ID],
         [Name]
FROM     [tblPHAs]
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.

[sartemieff]

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]

but I haven't really changed my question

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.