MS Access VBA Dynamically Specifying The Column Name to Write To

Gary Croxford
Gary Croxford used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
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.
Gary CroxfordOperations Support Analyst

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial