Prev: Combo Box Sorting Issue
Next: Form bleed through on tab control - Access 2003
From: g on 4 Jul 2010 23:28
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.
From: Salad on 5 Jul 2010 00:40
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?

Maybe if you ask in 5 or 10 more Access newsgroups you'll get a
definitive answer.
From: bsn on 5 Jul 2010 03:36

"g" <g_1(a)g.com> skrev i en meddelelse
news:tjcYn.3083$lS1.2690(a)newsfe12.iad...
>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


From: John W. Vinson on 5 Jul 2010 11:59
On Sun, 04 Jul 2010 23:28:44 -0400, g <g_1(a)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.

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
From: g on 5 Jul 2010 14:31
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.

> 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.
 |  Next  |  Last
Pages: 1 2 3
Prev: Combo Box Sorting Issue
Next: Form bleed through on tab control - Access 2003