Avatar of Gary Croxford
Gary Croxford
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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?
VBAMicrosoft Access

Avatar of undefined
Last Comment
Gary Croxford

8/22/2022 - Mon
Jim Dettman (EE MVE)

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
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gary Croxford

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy