Automate run all table names in Combo box

[Chris via AccessMonster.com]

I have a form with a combo box and button that creates tables based on a group of records by a variable name of a value in a field. The combo box holds the values and the button executes the code below. This all works fine, however I need a button for when the list is long in the combo box to run all instances of possible tables (I still need the single run feature).

This is where it all goes south. I have tried a For Each statemtent but I usually don't ever get that far with VB in MS Access. Here is a sample of the code that works and an unfinished sample of something I came up with to try a run all button. I appreciate andy help.

Working-

Private Sub Command2_Click()
Dim strSQL As String
Dim strSourceSelected As String
strSourceSelected = Trim$(Me![Combo5])

strSQL = "SELECT Split.* INTO " & strSourceSelected
strSQL = strSQL & " FROM Split"
strSQL = strSQL & " WHERE Split.Field4 = [Forms]![Create_Split]![Combo5]"
DoCmd.RunSQL strSQL
End Sub
Not Working-

Private Sub Command2_Click()
Dim strSQL As String
Dim strSourceSelected As String
Dim strSourceSelected2 As Variant
Dim strSourceSelected3 As Variant
strSourceSelected = Trim$(Me![Combo5])
strSourceSelected2 = Trim$(Me![Combo5])
strSourceSelected3 = "Select Field4 From Market_Distinct"

For Each strSourceSelected2 In strSourceSelected3
strSQL = "SELECT Market.* INTO " & strSourceSelected
strSQL = strSQL & " FROM Market"
strSQL = strSQL & " WHERE Market.Field4 = strSourceSelected"
DoCmd.RunSQL strSQL

Next
End Sub

--
Message posted via http://www.accessmonster.com

[MacDermott]

Before you go any further down this road, perhaps we could have an
explanation of why you're wanting this rather unorthodox procedure.
If you read through the posts in this and similar forums, you'll find many,
many instances where people are advised to put tables with similar
structures together into a single one. The reasons for this are multiple
and compelling.
But here you are wanting to do the opposite.
Why?

- Turtle

"Chris via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:a9******************************@AccessMonste r.com...

I have a form with a combo box and button that creates tables based on a group of records by a variable name of a value in a field. The combo box
holds the values and the button executes the code below. This all works
fine, however I need a button for when the list is long in the combo box to
run all instances of possible tables (I still need the single run feature).
This is where it all goes south. I have tried a For Each statemtent but I usually don't ever get that far with VB in MS Access. Here is a sample of
the code that works and an unfinished sample of something I came up with to
try a run all button. I appreciate andy help.
Working-

Private Sub Command2_Click()
Dim strSQL As String
Dim strSourceSelected As String
strSourceSelected = Trim$(Me![Combo5])

strSQL = "SELECT Split.* INTO " & strSourceSelected
strSQL = strSQL & " FROM Split"
strSQL = strSQL & " WHERE Split.Field4 = [Forms]![Create_Split]![Combo5]"
DoCmd.RunSQL strSQL
End Sub
Not Working-

Private Sub Command2_Click()
Dim strSQL As String
Dim strSourceSelected As String
Dim strSourceSelected2 As Variant
Dim strSourceSelected3 As Variant
strSourceSelected = Trim$(Me![Combo5])
strSourceSelected2 = Trim$(Me![Combo5])
strSourceSelected3 = "Select Field4 From Market_Distinct"

For Each strSourceSelected2 In strSourceSelected3
strSQL = "SELECT Market.* INTO " & strSourceSelected
strSQL = strSQL & " FROM Market"
strSQL = strSQL & " WHERE Market.Field4 = strSourceSelected"
DoCmd.RunSQL strSQL

Next
End Sub

--
Message posted via http://www.accessmonster.com

[Chris via AccessMonster.com]

I need multiple tables because a script run from another department via
cold fusion is accessing those tables based on information accessed via
web. Also each segment of records contatins complicated integration with
other files as well.

So to make a complicated story short I am trying to retrofit into an
existing process, as I think this would be easier than rewriting the whole
process. (that would be beyond my control anyway).

Thanks

--
Message posted via http://www.accessmonster.com

[Chris via AccessMonster.com]

I am still in limbo on this? Is there anyone who could provide assistance?

--
Message posted via http://www.accessmonster.com

[MacDermott]

Well, I still don't like this approach (think in the long run you'd be
better off doing the rewrite), but here's one approach to what you're trying
to do:

First, here's my understanding of what you need:
Instead of selecting one value of Field4 in your table Market, you want
to create a separate table for each value in Field4, all at once.
So there's nothing to choose from the combobox; it goes away, or is at
least irrelevant.
You have a query Marketing_Distinct which gives you the distinct values
in Field4.
(WARNING: AIR CODE)

Private Sub Command2_Click()
Dim strSQL As String
Dim strSourceSelected As String
Dim RS as DAO.RecordSet
Set RS=CurrentDB.OpenRecordset( "Select Field4 From Market_Distinct")

Do Until RS.EOF
strSourceSelected=RS!Field4
strSQL = "SELECT Market.* INTO " & strSourceSelected
strSQL = strSQL & " FROM Market"
strSQL = strSQL & " WHERE Market.Field4 = strSourceSelected"
DoCmd.RunSQL strSQL
RS.MoveNext
Loop

RS.Close
Set RS=Nothing

End Sub

"Chris via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:08******************************@AccessMonste r.com...

I am still in limbo on this? Is there anyone who could provide assistance?

--
Message posted via http://www.accessmonster.com