List box with query and string for rowsource

[Joshua Powell]

Is this possible...

I would like to create a form with a listbox that gives me values from
a query AND an additional value that I provide. More specifically I
want my users to select from the query, or if what they want isn't
there, then I want them to select the value "Not Found".

For simplicity's sake I will say my query has two fields,
lngIndividualID and strName. I already tried doing it this way, and
it so did not work, I don't know why I even bothered trying this, I
only include this hoping it might shed light on the goal I'm trying to
achieve:

ColumnCount = "2"
Rowsource = "1;(Not Found);qryLIST_IndividualNames"

(I used the number 1 as the "Not Found" value because I knew there was
no and would be no individual with ID number 1)

Am I trying to do something totally impossible? I'm this close to
creating a record into the table that my query is referencing with
strName "Not Found" and then making up for it by adding a WHERE clause
in my SQL statements that removes that record. What a pain that would
be though! Any help is awesome, even if its "sorry dude you just
can't do that."

Thanks
Joshua

 

[Arvin Meyer]

I'm not sure what you are asking for but you can select a value to fill a
column like this:

SELECT [Enter a value] AS Expr1, Whatever FROM Table1;

You can also select everything or part of anything like this:

SELECT IDField, Whatever FROM Table1 WHERE Whatever Like "*" & [Enter a
value] & "*";

And lastly, you can add a value to a unique list like this:

SELECT Whatever FROM Table1
UNION
SELECT [Enter a value] AS Whatever FROM Table1;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Douglas J. Steele]

Change the rowsource dynamically.

If DCount("*", "qryLIST_IndividualNames") = 0 Then
Me.List0.RowSourceType = "Value List"
Me.List0.RowSource = "1;""(Not Found)"""
Else
Me.List0.RowSourceType = "Table/Query"
Me.List0.RowSource = "qryLIST_IndividualNames"
End If

 

[user]

(I found this in the Access Cookbook by Getz, Litwin & Baron - great book!)

In the RowSource of the combo box you put the query:

select ID, Value from tblValues
UNION
select "", "<n/a>" from tblValues
ORDER BY Value;

and have the combo box store the first value, and display only the second
value. When the user chooses <n/a> then no value is stored (or you could
use zero I guess). Otherwise the ID of the Value they chose is stored.

Doug

 

[Joshua Powell]

To all that provided help, thank you so very much! The solution was
found in using the UNION query, I feel a little ashamed that I didn't
think of that... I don't typically use union queries so there
capabilities hadn't until now shown themselves. For the sake of
thoroughness I'll paste in what my rowsource ended up being

SELECT tblIndividuals.lngIndividualID, [tblIndividuals]![strFirstName]
& " " & [tblIndividuals]![strLastName] AS Name FROM tblIndividuals
UNION SELECT "","<n/a>" FROM tblIndividuals
ORDER BY Name;

and it worked like a charm, especially having a zero length string as
my "<n/a>" stored value, which made the VB code behind the combo box
especially beautiful.

Thanks again for your help!