Run a query with values taken from a form

[haylo]

Hi,

I would like to run a make-table query in VBA which will be attached to a button in a form. The form will also contain two text boxes; one where the user types in the original table to apply the query to (table1) and the new name of the table created (table2). My question is, at the moment my SQL string refers to a static table (table) and I would like it to refer to table1. Below is the macro so far..

Private Sub Button1_Click()

** Dim strSQL As String

***strSQL = "SELECT

![Field1] AS field1,

![field2] AS field2" _
** & " INTO " & Table2.Value _
** & " FROM " & Table1.Value _
** & " GROUP BY

![field3];" _

** DoCmd.SetWarnings False
** DoCmd.RunSQL strSQL
** DoCmd.SetWarnings True

End Sub

Any help would be much appreciated.

Thanks

 

[Joe4]

Regarding your "From" table, will the tables always have the same structure (i.e. same field names)?
If not, you will want to avoid making direct reference to any field names in your query, you will probably just want to use the asterisk ("*") to tell it to import ALL fields.

 

[haylo]

Thanks for your response Joe.

The 'From' table structure will be the same in the medium term and will also contain data which is no longer needed.

Is it right to say that the 'Select' is related to the 'From' table, so I could get rid of the '

![Field1]' syntax and replace it with something else? If yes, what should I replace it with?

Thanks

 

[Joe4]

You'll want to replace it with the name of your text box, i.e.

strSQL = "SELECT [" & Me.txtFromTable & "]![Field1] AS field1, [" & Me.txtFromTable &  "]![field2] AS field2"...

By the way, I am not sure what you are trying to do with your code. If you are doing an aggregate query (Group By), each field in the Select portion either needs to be in the "Group By" clause, or have an Aggregate function applied to it.

 

[Kreszch68]

I made an example database instead of trying to explain by typing a lot of text. Goto this link and then select the TableReplicator file.

The code behind all this

Option Compare Database
Option Explicit

Private Sub cmbSourceTables_AfterUpdate()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs(Me.cmbSourceTables.Value)
Me.lstFields.RowSource = ""
For Each fld In tdf.Fields
    Me.lstFields.AddItem fld.Name
Next fld

End Sub

Private Sub cmbSourceTables_Enter()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
Me.cmbSourceTables.RowSource = ""
For Each tdf In db.TableDefs
    
    If Left(tdf.Name, 4) <> "Msys" Then
        Me.cmbSourceTables.AddItem tdf.Name
    End If
    
Next tdf
End Sub

Private Sub cmdCreateBackUp_Click()
Dim vItem As Variant
Dim i As Integer
Dim sFields() As String
Dim sField_ As String
Dim sBackUpTableName As String
Dim sSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
If Len(Nz(Me.txtBackUpTableName.Value, vbNullString)) < 2 Then
    MsgBox "Name of backup table is too short", vbExclamation
    Exit Sub
Else
    For Each tdf In db.TableDefs
        If tdf.Name = Me.txtBackUpTableName.Value Then
            db.TableDefs.Delete tdf.Name
        End If
    Next tdf
    
    sBackUpTableName = Me.txtBackUpTableName.Value
    
End If
If Me.lstFields.ItemsSelected.Count <> 0 Then
    ReDim sFields(Me.lstFields.ItemsSelected.Count - 1)
Else
    MsgBox "Select at least one field from the list", vbExclamation
    Exit Sub
End If
For Each vItem In Me.lstFields.ItemsSelected
    sFields(i) = Me.lstFields.ItemData(vItem)
    i = i + 1
Next vItem
sField_ = Join(sFields, ", ")
sSQL = "Select " & sField_ & " Into " & sBackUpTableName & " From " & Me.cmbSourceTables.Value
db.Execute sSQL
MsgBox sBackUpTableName & " created", vbInformation
End Sub

PS To keep it short and simple I left out a mechanism to avoid deleting existing tables if a user enters a name of an existing table as the name for a backup table.