Options for populating a listbox rowsource plus a Union query question (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 00:32
Joined
Mar 14, 2017
Messages
4,734
Ok so this SQL looked fine to me, ansi-sql wise. But Access doesn't like it. MUST I have a "from" clause on the bottom half?? Access says to this "Query input must contain at least one table or query". Sorry, I don't do Union's very often in Access, I guess luckily my data structures are usually good enough to where it doesn't come up.

Code:
select qryListPeopleWithUsername.Name, qryListPeopleWithUsername.Account, qryListPeopleWithUsername.Username from qryListPeopleWithUsername
union all
select 'John Doe' as [Name], 'accountvalue(redacted)' as [Account], 'usernamevalue (redacted)' as [Username]

What is the context? Well, I have a Listbox on a form. In this Listbox, I want to populate Name, Account and Username from another query--qryListPeopleWithUsername. This query is a carefully-curated resultset that's "just perfect" - except for one little flaw. For development and testing purposes, I want to "stack" onto it a few more records - the name, account & username of myself and a few testers/admin folks (which will not come up in that query).

So in a practical sense, I want to do precisely what the obvious intent of that union query is.

Another option, I guess, would be to change the Listbox's Rowsource type to Value List, and add all the values in code. Then I can add whatever I jolly well please. But I'm wanting to do this the simplest way and not reinvent the wheel, any other thoughts?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:32
Joined
Oct 29, 2018
Messages
14,860
Maybe just try adding the same query for the second SQL statement, but also maybe don't use UNION ALL, just UNION. Otherwise, you may have to use a SELECT DISTINCT in the second SQL statement.
 

Isaac

Lifelong Learner
Local time
Today, 00:32
Joined
Mar 14, 2017
Messages
4,734
Maybe just try adding the same query for the second SQL statement
That sounds so silly (I'm "addressing" Microsoft, not you dbGuy!) that I never thought to try it - and works perfect!
Thanks
I just added "from qryListPeopleWithUsername" at the end of the second sql, and it worked fine - Oh, and I removed Union All to just Union, realizing I didn't honestly need any dupes.

Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:32
Joined
Oct 29, 2018
Messages
14,860
That sounds so silly (I'm "addressing" Microsoft, not you dbGuy!) that I never thought to try it - and works perfect!
Thanks
I just added "from qryListPeopleWithUsername" at the end of the second sql, and it worked fine - Oh, and I removed Union All to just Union, realizing I didn't honestly need any dupes.

Thanks!
Congratulations! Glad to hear you got it to work. Good luck with your project.
 

Users who are viewing this thread

Top Bottom