Solved updating from arrays (1 Viewer)

[StuartWB]

Hello all really struggling here to get this to work, I need to update a config table, there are 2 arrays array 1 "ImportTableHeaderFields" Single dimension which is basically the field names of the table and Array 2 is "ImportConfig " a 2 Dimensional array of the configuration data Description and Serial number
the Array structure looks like this
Array Name Value
ImportTableHeaderFields(0) = Date
ImportTableHeaderFields(1) = Electronics Sect
ImportTableHeaderFields(2) = APU
ImportTableHeaderFields(3) = SPU
ImportTableHeaderFields(4) = memory

ImportConfig(0,0) = APU
ImportConfig(0,1) = MPU
ImportConfig(0,2) = CPU

ImportConfig(1,0) = Serial Numbers 1005
ImportConfig(1,1) = Serial Numbers 22325
ImportConfig(1,2) = Serial Numbers 5524Z

My code so far gets me all the values i need in both arrays

Sub RowsArray2()



Dim dbs As Database, rst As Recordset, rs2 As DAO.Recordset, StrSql As String

Dim ImportTableHeaderFields, ImportConfig As Variant

StrSql = "SELECT * FROM Product"                    ' Build SQL statement that returns specified fields.

Set dbs = CurrentDb

Set rst = CurrentDb.OpenRecordset("Import")



       'List Import Table column Names in the Array

        ReDim ImportTableHeaderFields(rst.Fields.Count - 1)

        Dim lngCount As Long

        For lngCount = 0 To rst.Fields.Count - 1

        ImportTableHeaderFields(lngCount) = rst.Fields(lngCount).Name

        Next lngCount

                                                       

    Set rst = dbs.OpenRecordset(StrSql)

        rst.MoveLast

        rst.MoveFirst

        ImportConfig = rst.GetRows(rst.RecordCount)

rst.Close

Set dbs = Nothing

End Sub



what i need to do is something like this i think but i cannot get it to insert anything in to the table.

   with rs2

   .AddNew    (when ImportTableHeaderFields = ImportConfig add ImportConfig(1,0).value to new record)

    rst![ImportTableHeaderFields] = rs2![ImportConfig].value

    .update

Any help would be appreciated

 

[MajP]

I am having a hard time wrapping my head around what you are trying to do. Any chance you could post a small version of this database with the tables in question? I do not understand the need for the array. One thing you do not show how you create RS2. Is this a global variable? If so you also declare it in the code.

The way I interpret this is you get the field names out of table products and put that into a single dimension array, then read the values and put that into a multi dimension array. Then you look like you just copy that data into another unknown recordset. I do not get it, makes no sense to me. Looks like you are trying just a simple insert query in a very bizarre way. At least provide some table data before and the desired recordset after. Maybe someone else can interpret, but since the code is incomplete I cannot.

To make your code more readable use the code tags. It is the three dots followed by the down arrow at the top of this box.

 

[CJ_London]

It is the three dots followed by the down arrow at the top of this box.

used to be, but now has it's own button </>.

click on the button and copy paste to the window that opens

 

[StuartWB]

MajP please accept my apology for the untidy code, i hope that you can read it now.
I suppose it is like a an insert query, but when i create a query from the table there are only 2 fields Description and Serial Number.
The table they need to go in to has 69 fields and only the serial number need to be added in to a new row.
does that make sense ?

query result vary

Table layout is fixed

 

[MajP]

MajP please accept my apology for the untidy code,

. No big deal, the tag features allow you to do a lot of formatting, images, links etc. Once you figure it out.
In the products is there simply one set of data. In other words are you creating only one record? Or are there multiple records of 69 fields.

In the table you are inserting into, does that table already exist with the correct field names. If I understand then I would try making a cross tab query of table product. Then I would get basically the same output format. Now I can read the fields in the same manner and do an insert into the import table. Let me try.

 

[StuartWB]

Thanks for the reply
The product has a max of 69 fields that could be populated. the config data that we get may not contain some of the fields so therefore there may only be 30 fields, this is only for 1 set of records or row at a time.

The table i am inserting into already exist and the field names are correct, this does not change.

I hope i'm making sense

 

[MajP]

This worked for me. There is no error checking here. This assumes that in the destination table all possible names exist. I do not check first if that name exists in the destination or handle the error if it does not. Just a simple resume next would probably work.

ublic Sub CreateInput()
  Dim rsImport As DAO.Recordset
  Dim rsExport As DAO.Recordset
  Dim fld1 As DAO.Field
  Dim fld2 As DAO.Field
  Dim fldName As String
  Set rsImport = CurrentDb.OpenRecordset("Products")
  Set rsExport = CurrentDb.OpenRecordset("tblOut")
 
  rsExport.AddNew
 
  Do While Not rsImport.EOF
    fldName = rsImport!Desc
    Debug.Print fldName & " " & rsImport!serial
    rsExport.Fields(fldName) = rsImport!serial
    rsImport.MoveNext
  Loop
  rsExport.Update
End Sub

 

[StuartWB]

Works a treat, really appreciated.
thank you for all your help, there is so much to learn and so many ways to do it.