Using VBA to fill table w/field names in an Array

[cstimart]

I am trying to fill a table with VBA/DAO and have many field names. I can enter each field using the following, as examples...

rs![ItemNo] = 1
rs![ItemAge] = 1

However, I would like to put "ItemNo" and "ItemAge" and all the others into an array, like

strTemp = Array("ItemNo", "ItemAge"....)

Then I'd be able to use a loop to fill the data, rather then many individually written lines.

I believe this is possible, but don't recall how.

Thanks!

 

[xenou]

Hi,

Your question seems incomplete. If you want to make all fields equal to 1 then you can do so easily:

[COLOR="Navy"]Sub[/COLOR] Test()
    
    [COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] DAO.Recordset
    
   
    [COLOR="SeaGreen"]'//Open table[/COLOR]
    [COLOR="Navy"]Set[/COLOR] rs = CurrentDb.OpenRecordset("Table3", dbOpenTable)
    
    [COLOR="SeaGreen"]'//Create record with all fields to 1[/COLOR]
    [COLOR="Navy"]With[/COLOR] rs
        .AddNew
        [COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] .Fields.Count - 1
            .Fields(i) = 1
        [COLOR="Navy"]Next[/COLOR]
        .Update
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    
    rs.Close

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

But I can't imagine that's what you really want. In any case, you could do the same without VBA by just setting the default values for the fields in the DB. Maybe that's what you really need?

 

[cstimart]

Not all values will be 1, they'll vary.

i revised my code to save the data in an excel spreadsheet...and then TransferSpreadsheet, as the fields won't always be in sequential order.

Thanks!

 

[xenou]

Hi,
Good deal! Post back if any other problems.
ξ

 

[HiTechCoach]

TIP:

You can use also reference a field in a recordset like this:

rs("ItemNo") = 1

or

Dim strFieldName as String

strFieldName = ItemNo

rs(strFieldName) = 1

 

[HiTechCoach]

It may be possible on some type of loop. It will depend on how you will get the values for each field.

Would you please provide for details.