Union Query - table selection from List Box

[tobypsl]

Hello

I am wondering if it is possible to construct a union query for tables selected by the user in a drop down list box ?

I currently have about 200 tables, all with the same data types, but only 10 or so of them are relevant at any given time - relevance decided by the user.

Any ideas, pointers etc greatly appreciated.

 

[earthandfire]

Maybe something like:

Private Sub Command2_Click()

Dim s As String
Dim a As Integer
  
  s = ""
  If List0.ItemsSelected.Count = 0 Then Exit Sub
  For a = 0 To List0.ListCount - 1
    If List0.Selected(a) Then
      If s <> "" Then s = s & " UNION "
      s = s & "SELECT Field1, Field2 FROM " & List0.ItemData(a)
    End If
  Next a
  s = s & ";"
  MsgBox s

End Sub

Hope this helps.

 

[tobypsl]

hi earthandfire

thanks for the input. I've entered this code, attached it to the onclick for a command button on a simple form, and the expression it creates seems to be fine - now the stupid question - how do I actually get a query to execute ie. how do I get the results of the query into a table (that's right - my Access skills are not great ).

thanks again for your help.

 

[earthandfire]

To demonstrate this I have created 4 tables (tbl1, tbl2,tbl3, tbl4) each with 2 fields (Field1, Field2).

I added a few dummy records to each of the tables

I've created a Form which I have called SubForm on which I have added 2 TextBoxes and set their ControlSource property to Field1 and Field2 respectively. (The RecordSource of the form itself is left blank).

On the main form (i.e. the form with the CommandButton and ListBox I've added a SubForm control (called Child4) and have modified the above code to be the following:

Private Sub Command2_Click()

Dim s As String
Dim a As Integer
  
  s = ""
  If List0.ItemsSelected.Count = 0 Then Exit Sub
  For a = 0 To List0.ListCount - 1
    If List0.Selected(a) Then
      If s <> "" Then s = s & " UNION "
      s = s & "SELECT Field1, Field2 FROM " & List0.ItemData(a)
    End If
  Next a
  s = s & ";"

  Child4.Form.RecordSource = s

End Sub

As it stands, using this technique, you will only be able to edit / add / delete data when only one table is selected - where multiple tables are selected you will only be able to view the data.

Hope this helps.

 

[tobypsl]

thanks earthandfire I made ammendments as suggested - I got an error 424 - Object Expected. Also the layout would allow me to see one record at a time when what I am really looking for is a continuous list - like a datasheet view.

What I tried previously was to create an append query which used a union query as the source of data - appending it to a new table. The problem was that I had to manually create the union query.

If I could use your method of creating the union query and then append those values into a table (preferably temporary otherwise when the query was refreshed the same data would appear multiple times) everything would be fine.

anyway thanks again for your help.