Insert listbox into table

Question

I have a listbox (value list) "lst_FieldsMatch". 

I want to insert the items into my table called "FieldList" (SQL server table). 

I have 50 columns in Fieldlist table.  I want to insert all the values of my listbox into 1 record.  Each value going to a column.  The columns are names as follows.. Field1, Field2, Field3, Field4....Field50.

I have the following and it's working....How does this code look?

    Dim db As DAO.Database

    Dim ctrl As access.Control

    Dim ctrl2 As access.Control

    Dim varItem As Variant

    Dim varItem2 As Variant

    Dim i As Integer, ListCount As Integer

    Dim strSQLServer As String, strSQLDBName As String, strSQLTable As String

    Set db = CurrentDb

    On Error Resume Next

    varSQL = "Select ProjectsID From Projects Where ClientName='" & Me.Cmb_ClientName & "' AND AuditYear='" & Me.cmb_AuditYear & _

            "' AND ProjectName='" & Me.Cmb_ProjectName & "' AND ToolName='" & Me.cmb_ToolName & "' AND AuditTable='" & Me.cmb_AuditTable & "'"

    Set rst = db.OpenRecordset(varSQL, dbOpenDynaset)

    If rst.EOF = True And rst.BOF = True Then

      MsgBox "No Project Selected"

      Exit Sub

    Else

      ' Step thru the recordset

      rst.MoveLast

      rst.MoveFirst

      MyProjectsID = rst.Fields("ProjectsID")

      strSQLServer = "USATL02PRSQ70" 'rst3.Fields("SQLServer").value

      strSQLDBName = "AS_PROJECTS" 'rst3.Fields("SQLDBName").value

      strSQLTable = "FIELDLIST" 'rst3.Fields("SQLTable").value

    End If

    rst3.Close

    Set rst3 = Nothing

    rst.Close

    Set rst = Nothing

    Set ctrl = Me.lst_FieldsMatch

    strSQL = "INSERT INTO FieldList("

    strSQL2 = "VALUES ("

    i = 1

    ListCount = Me.lst_FieldsMatch.ListCount

    For varItem = 0 To ctrl.ListCount - 1

          strSQL = strSQL & "Field" & Trim(Str(varItem + 1)) & ", "

          strSQL2 = strSQL2 & "'" & Me.lst_FieldsMatch.ItemData(varItem) & "', "

          i = i + 1

    Next varItem

    strSQL = Mid(strSQL, 1, Len(strSQL) - 2) & ", ProjectsIDLookup, MapName) "

    strSQL2 = Mid(strSQL2, 1, Len(strSQL2) - 2) & ", '" & MyProjectsID & "', '" & Me.txt_MapName & "')"

    If Me.cmb_TableType = "Main Table" Then

            If Len(strSQLTable) > 1 And IsNull(strSQLTable) = False Then

               Call SQL_PassThrough(sConnectDAO, strSQL & strSQL2)

            End If

    End If

Answer 1

I give up. You keep telling what you want to do. I understand what that is. But you don't tell us WHY you want to do this or what doing it will accomplish for you.

Answer 2

I have a form with 3 subforms.  The subforms have 50 textboxes.  I attach 3 tables(passthru queries) to the form, 1 to each subform.  The tables will change and the field names will be different.  I have created a search on the main form to search for things.  The searches are passthru that return results that get fed to the 3 subforms.

Answer 3

I would still like to know what you are trying to accomplish here. I think you could do this more easily and more properly.

Answer 4

There's no reporting done with this.  I have 50 field place holders to capture the names of the fields of a table.  The table will have different field names each time.  The form is hard coded and I need to map the field names from the table to the form.

Answer 5

Can I ask WHY you would want to do this? This denormalizes your data and will make it harder to report on.