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:
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
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.
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
Jim