ComboBox List Nightmare

jippyuk · June 17th, 2009, 04:06 AM

Hi all

firstly let me just say thanks a lot for taking the time to read this message, I hope some of you will read this and think "ohhhh this guy is so close with his code, ill just change it abit and he will be happy" rather than "this guy is way off!"

Firstly the simple scenario. (MYSQL Access 2007 VBA)

One table call it tableInfo
About 15/25 columns, but only 3 I am interested in for this example.

Column 1 /2 /3
The all have the same data type of a VarChar(255) assigned to them on my SQL Server.

The are fed different values for the time being from numerous forms etc within the database, its nothing hugely complicated...

However I have a ComboBox List which previously was functioning for Column1. For example if new data was amended to that column after an update you could then choose that item from the combolist.
Simple.

I also read that using the SQL statement UNION you are able to take 2 or more columns of the same time and basically conform them into one. Which is perfect in my scenario as what I want is to be able able take the values from all 3 of the columns and have them listed (in and order I choose) under one ComboBox list.

Code:

SELECT DISTINCT tableInfo.[column1]
FROM tableInfo
UNION SELECT DISTINCT tableInfo.[column2]
FROM tableInfo
ORDER BY 1;

This, for the 2 columns, works perfectly. It takes all the values from the columns and puts it as one. The trouble comes with MY LOGIC and the thinking of adding an addition line to the statement.

Simply adding any addition UNION SELECT DISTINCT before the Order By 1 generates code error.

"Error in your SQL Syntax" < No kidding Mr.SQL give me a chance Im learning!

So I obviously have dug around a lot on the net, there are tonnes of examples of taking info from different tables etc, but havent stumbled across how they deal with more than 2 columns.

Something like this is what I was thinking

Column1
A
B
C

Column2
D
E
F

Column3
G
H
I

Combobox List after SQL Query
1 Column in list only....... Dont want 3 columns.

A
B
C
D
E
F
G
H
I

I know this is a long message and formatted badly, however I am about to embark on another day of coding and cant stand the thought that this is something incredibly simple which I am just missing syntax wise.

Regards to all.

JP

mmcdonal · June 19th, 2009, 11:37 AM

Without being sure why you want to do this (table design and list structure), and what you are going to do with the selection, it would be hard to say. You can always create a temp table, delete all the data from it, then run 3 append queries from your columns to fill it up, then bind your combo to the temp table when your form opens.

Like, on the On Click event of the button you use to open the form, do this:

Code:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteOldData"
DoCmd.OpenQuery "qryAppendColumn1"
DoCmd.OpenQuery "qryAppendColumn2"
DoCmd.OpenQuery "qryAppendColumn3"
DoCmd.SetWarnings True
 
DoCmd.OpenForm...

Or put that on the On Load event of the form. Would that work?

Then on the combo SQL string, put SELECT DISTINCT ... ORDER BY