populating a combobox with values from two tables

#1

I created a form, added a combobox to it and want the combobox to be
populated by values from two different tables.

For the rowsource property of the combobox I am using below query

(SELECT DISTINCT [Table1].Column1 FROM [Table1]) UNION (SELECT DISTINCT
[Table2].Column1 FROM [Table2])
ORDER BY [Table1].Column;

but it does not work.

When I try to view the form in Form view, and try to choose an option
from the combobox I get a message(which is like a textbox) "Enter
Parameter Value Table2.Column1 and a place to enter some value and a OK,
Cancel button.

Why am I getting the message when I try to access the combobox and how
can I fix it? I am using Access 2007.

Any advice would be welcome.

#2

g wrote:

Quote:

I created a form, added a combobox to it and want the combobox to be
populated by values from two different tables.

For the rowsource property of the combobox I am using below query

(SELECT DISTINCT [Table1].Column1 FROM [Table1]) UNION (SELECT DISTINCT
[Table2].Column1 FROM [Table2])
ORDER BY [Table1].Column;

but it does not work.

When I try to view the form in Form view, and try to choose an option
from the combobox I get a message(which is like a textbox) "Enter
Parameter Value Table2.Column1 and a place to enter some value and a OK,
Cancel button.

Why am I getting the message when I try to access the combobox and how
can I fix it? I am using Access 2007.

Any advice would be welcome.

Have you even attempted to run the rowsource in the query builder? If
it doesn't run as a query, why would you expect it to run in the form?

Maybe if you ask in 5 or 10 more Access newsgroups you'll get a
definitive answer.

#3

"g" <g_1@g.com> skrev i en meddelelse
news:tjcYn.3083$lS1.2690 (AT) newsfe12 (DOT) iad...

Quote:

I created a form, added a combobox to it and want the combobox to be
populated by values from two different tables.

For the rowsource property of the combobox I am using below query

(SELECT DISTINCT [Table1].Column1 FROM [Table1]) UNION (SELECT DISTINCT
[Table2].Column1 FROM [Table2])
ORDER BY [Table1].Column;

but it does not work.

When I try to view the form in Form view, and try to choose an option from
the combobox I get a message(which is like a textbox) "Enter Parameter
Value Table2.Column1 and a place to enter some value and a OK, Cancel
button.

Why am I getting the message when I try to access the combobox and how can
I fix it? I am using Access 2007.

Any advice would be welcome.

There must be data in coloum1 and coloum2...
Bjarne

#4

On Sun, 04 Jul 2010 23:28:44 -0400, g <g_1@g.com> wrote:

Quote:

I created a form, added a combobox to it and want the combobox to be
populated by values from two different tables.

For the rowsource property of the combobox I am using below query

(SELECT DISTINCT [Table1].Column1 FROM [Table1]) UNION (SELECT DISTINCT
[Table2].Column1 FROM [Table2])
ORDER BY [Table1].Column;

but it does not work.

When I try to view the form in Form view, and try to choose an option
from the combobox I get a message(which is like a textbox) "Enter
Parameter Value Table2.Column1 and a place to enter some value and a OK,
Cancel button.

Why am I getting the message when I try to access the combobox and how
can I fix it? I am using Access 2007.

Any advice would be welcome.

The prompt suggests that there is no Table2, or no field named Column1 in
Table2 - you'll get a prompt if you have an expression enclosed in square
brackets which Access cannot find or recognize.

You also don't need the extra parentheses, nor do you need the DISTINCT
clause; a UNION query will already remove all duplicates. Try

SELECT [Table1].Column1 FROM [Table1] UNION SELECT
[Table2].Column1 FROM [Table2] ORDER BY 1;

The 1 in the ORDER BY just means "the first field in the SELECT clause"
because the fieldnames need not match between the multiple SELECT clauses; you
may also be able to use ORDER BY Column1 if that's the actual fieldname in the
(first) SELECT.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

#5

On 7/5/2010 12:40 AM, Salad wrote:

Quote:

g wrote:

I created a form, added a combobox to it and want the combobox to be
populated by values from two different tables.

For the rowsource property of the combobox I am using below query

(SELECT DISTINCT [Table1].Column1 FROM [Table1]) UNION (SELECT
DISTINCT [Table2].Column1 FROM [Table2])
ORDER BY [Table1].Column;

but it does not work.

When I try to view the form in Form view, and try to choose an option
from the combobox I get a message(which is like a textbox) "Enter
Parameter Value Table2.Column1 and a place to enter some value and a
OK, Cancel button.

Why am I getting the message when I try to access the combobox and how
can I fix it? I am using Access 2007.

Any advice would be welcome.

Have you even attempted to run the rowsource in the query builder? If it
doesn't run as a query, why would you expect it to run in the form?

It did not. That is why I posted to seek help to know what I was doing
wrong.

Quote:

Maybe if you ask in 5 or 10 more Access newsgroups you'll get a
definitive answer.

As I told you before, I do it because some people read only one forum
and others another so I guess if I post in both, I can get advice from
both.

FYI, people who are regular posters in this forum may not have time
always to respond whereas someone else who may be a regular in another
Access forum(where I may have posted) may respond sooner which can solve
the issue. Lot of my posts in this forum were not responded at times(and
many were responded too and you also helped me a lot for which I am
grateful to you and all who responded). In this situation, John gave an
answer which solved the issue.

When someone is stuck up, he does not seek one source of help as for
some reason if that source is busy/not available, he has to wait or may
not get the help. Instead, he seeks different sources which can help him
out.

As long as I acknowledge(and thank) the posters time and effort in the
groups I posted and tell them what solved the problem(which helps people
who may read the thread in the future and benefit from it), I don't
think I deserve to be blamed for cross posting.

#6

On 7/5/2010 11:59 AM, John W. Vinson wrote:

Quote:

On Sun, 04 Jul 2010 23:28:44 -0400, g<g_1@g.com> wrote:

I created a form, added a combobox to it and want the combobox to be
populated by values from two different tables.

For the rowsource property of the combobox I am using below query

(SELECT DISTINCT [Table1].Column1 FROM [Table1]) UNION (SELECT DISTINCT
[Table2].Column1 FROM [Table2])
ORDER BY [Table1].Column;

but it does not work.

When I try to view the form in Form view, and try to choose an option
from the combobox I get a message(which is like a textbox) "Enter
Parameter Value Table2.Column1 and a place to enter some value and a OK,
Cancel button.

Why am I getting the message when I try to access the combobox and how
can I fix it? I am using Access 2007.

Any advice would be welcome.

The prompt suggests that there is no Table2, or no field named Column1 in
Table2 - you'll get a prompt if you have an expression enclosed in square
brackets which Access cannot find or recognize.

Thanks John. Yes, it was a typo on my part which was causing the error.
"You'll get a prompt if you have an expression enclosed in square
brackets which Access cannot find or recognize." This info is useful
and will help me in solving such issues in the future, if they arise.

Quote:

You also don't need the extra parentheses, nor do you need the DISTINCT
clause; a UNION query will already remove all duplicates. Try

SELECT [Table1].Column1 FROM [Table1] UNION SELECT
[Table2].Column1 FROM [Table2] ORDER BY 1;

The 1 in the ORDER BY just means "the first field in the SELECT clause"
because the fieldnames need not match between the multiple SELECT clauses; you
may also be able to use ORDER BY Column1 if that's the actual fieldname in the
(first) SELECT.

I appreciate your time and help.

#7

g wrote:

Quote:

On 7/5/2010 12:40 AM, Salad wrote:

g wrote:

I created a form, added a combobox to it and want the combobox to be
populated by values from two different tables.

For the rowsource property of the combobox I am using below query

(SELECT DISTINCT [Table1].Column1 FROM [Table1]) UNION (SELECT
DISTINCT [Table2].Column1 FROM [Table2])
ORDER BY [Table1].Column;

but it does not work.

When I try to view the form in Form view, and try to choose an option
from the combobox I get a message(which is like a textbox) "Enter
Parameter Value Table2.Column1 and a place to enter some value and a
OK, Cancel button.

Why am I getting the message when I try to access the combobox and how
can I fix it? I am using Access 2007.

Any advice would be welcome.

Have you even attempted to run the rowsource in the query builder? If it
doesn't run as a query, why would you expect it to run in the form?

It did not. That is why I posted to seek help to know what I was doing
wrong.

Then you need to improve your debugging skills. I would have created
SELECT DISTINCT [Table1].Column1 FROM [Table1]
as one query and tested it and
SELECT DISTINCT [Table2].Column1 FROM [Table2]
as another and tested prior to asking.

Quote:

Maybe if you ask in 5 or 10 more Access newsgroups you'll get a
definitive answer.

As I told you before, I do it because some people read only one forum
and others another so I guess if I post in both, I can get advice from
both.

FYI, people who are regular posters in this forum may not have time
always to respond whereas someone else who may be a regular in another
Access forum(where I may have posted) may respond sooner which can solve
the issue. Lot of my posts in this forum were not responded at times(and
many were responded too and you also helped me a lot for which I am
grateful to you and all who responded). In this situation, John gave an
answer which solved the issue.

When someone is stuck up, he does not seek one source of help as for
some reason if that source is busy/not available, he has to wait or may
not get the help. Instead, he seeks different sources which can help him
out.

As long as I acknowledge(and thank) the posters time and effort in the
groups I posted and tell them what solved the problem(which helps people
who may read the thread in the future and benefit from it), I don't
think I deserve to be blamed for cross posting.

Who cares. There's a shitload of microsoft.public.access newsgroups.
I'd recommend finding one of them that has a pulse and post there.

#8

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote

Quote:

Who cares. There's a shitload of microsoft.public.access newsgroups. I'd
recommend finding one of them that has a pulse and post there.

I don't understand your criticism of the correct use of cross-posting.

It strikes me as appropriate to post to these two grouips.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

#9

g <g_1@g.com> wrote in news:UxpYn.3428$Zp1.1266 (AT) newsfe15 (DOT) iad:

Quote:

On 7/5/2010 12:40 AM, Salad wrote:
Maybe if you ask in 5 or 10 more Access newsgroups you'll get a
definitive answer.

As I told you before, I do it because some people read only one
forum and others another so I guess if I post in both, I can get
advice from both.

Your assumptions are basically wrong. Since MS discontinued their
news server/web-based interface to the Usenet groups, there is a
very small handful of groups with posts.

Even beyond that, all the Access gurus that I'm aware of were
reading all the microsoft.public.access.* newgroups regularly, so
you were much more likely to annoy by multi-posting than increase
your chances of getting help.

Really, at this point, so far as I'm concerned the only two
newsgroups to post in are comp.databases.ms-access and
microsoft.public.access. If you don't get an answer in those, you're
not going to increase your chances by posting to one of the
specialized newsgroups.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

#10

"Douglas J. Steele" <NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote in
news:i0tbf8$39k$1 (AT) news (DOT) eternal-september.org:

Quote:

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote in message
news:u_2dndE0scV6tq_RnZ2dnUVZ_s-dnZ2d (AT) earthlink (DOT) com...

Who cares. There's a shitload of microsoft.public.access
newsgroups. I'd recommend finding one of them that has a pulse
and post there.

I don't understand your criticism of the correct use of
cross-posting.

It strikes me as appropriate to post to these two grouips.

I don't post very often, but when I do, I make no effort whatsoever
to find a sub-subject-appropriate newsgroup to post to, as I don't
see the point. I always post in microsoft.public.access and no other
group. Now comp.databases.ms-access is perhaps again the preferred
newsgroup, as it seems to have more traffic than mpa does.

I always felt the breakdown of topics was just not useful at all, as
it raised more questions about where to post than it helped by
segregating topics. I think that topics were more useful in the
web-based interface than when the groups were read via a news
reader, precisely because news readers provide such a superior
interface that it's not nearly as much a problem to have multiple
topics mixed up in one newsgroup, because of threading and so forth.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/