MS Access VBA Dynamically Specifying The Column Name to Write To

Thank you for looking at my question,

I have put together a small split database to which the user imports data from a number of text files.
One table, a monthly analysis of inventory movements, is created dynamically based upon user specified criteria which means the number of columns may vary depending upon the number of months in the sample (one column per month.)

I am struggling to get the table to populate dynamically. the code I am using is:

    
    rs_PeriodIssue.AddNew
    rs_PeriodIssue("Item").Value = strItemOld
    rs_PeriodIssue("Item Desc").Value = strItemDesc
    rs_PeriodIssue("WH").Value = strWarehouse
    
    intArrayElement = 0
    For i = 0 To intPeriodRange
        strPeriod = CStr(intColumns(intArrayElement, 0) & "_" & intColumns(intArrayElement, 1))
        
        rs_PeriodIssue(strPeriod).Value = CLng(strValues(i, 1))
        intArrayElement = intArrayElement + 1
    Next
    
    rs_PeriodIssue("Summed Issue").Value = CLng(Format(intSummedIssue, "0.0000"))
    rs_PeriodIssue("Average Issue").Value = CLng(Format(intAvgIssue, "0.0000"))
    rs_PeriodIssue("Sample Period").Value = CLng(intPeriodRange + 1)
    rs_PeriodIssue("Days in Sample Period").Value = CLng(intDaysInPeriod)
    rs_PeriodIssue("Year From").Value = CLng(intYearFrom)
    rs_PeriodIssue("Period From").Value = CLng(intPeriodFrom)
    rs_PeriodIssue("Year To").Value = CLng(intYearTo)
    rs_PeriodIssue("Period To").Value = CLng(intPeriodTo)
    rs_PeriodIssue.Update

Open in new window

The thing falls over at this point:
For i = 0 To intPeriodRange
strPeriod = cstr(intColumns(intArrayElement, 0) & "_" & intColumns(intArrayElement, 1))
rs_PeriodIssue(strPeriod).Value = CLng(strValues(i, 1))
intArrayElement = intArrayElement + 1
Next

with an error Type 13 Mismatch

In vba how do I (can I) dynamically specify the column name to which I wish to write an item of data?

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 62

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented: 2016-03-18

Your syntax for referring to the column is correct. What the error message is telling you is that you are trying to put a string into a numeric or that you're trying to put a numeric into string, so you need to check your datatypes.

Jim

BTW, that also assumes that strPeriod has the correct value.

I would put a break point on the line and double check that it contains the correct value. If it does, then the data type is not a numeric.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Gary CroxfordOperations Support AnalystAuthor Commented: 2016-03-18

Jim, Thank you for your response,

strPeriod has the right value because, from your first response I forced it to write a number to each column and that worked so it must be the contents of the array that are causing the issue - will look into it

MS Access VBA Dynamically Specifying The Column Name to Write To

Who is Participating?