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("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
    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)

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

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?
Gary CroxfordOperations Support AnalystAsked:
Who is Participating?
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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 Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.


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:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.