What's wrong with this code?

[ArtPM]

I have a table with 26 entries and the first field contains a milestone ID that I need to reference in later calculations. I could explicitly creat a DIM statement for each one, but then I would have to update the program every time there are new milestones.

I would like to define these variables programmatically. Any suggestions?

The ultimate result I am looking for is to create a new Schedule and append it to a Schedule table.

User inputs will be a Schedule completion date and several variables that will alter a set of intervals (duration between two milestones). A table (ScheduleIntervals) contains 26 sets of intervals with 3 fields: IntervalName, Interval1, & Interval2 (Interval? is determined by user input to a check box).

I would then calculate the milestone dates as follows:
Milestone 25 = Milestone 26 - IntervalName25(column 2 or 3 depending on user input)(ex. 14 days)
and so on............

Then I would create a record in the Schedule table: Milestone 25 = 12/01/02; Milestone 24 = 11/16/02; et cetera.

Here's the code I have - the line designated >>> <<< is the part of the code where I don't seem to be able to get the right syntax.
*************
On Error GoTo Err_CreateSchedule_Click
' Get appropriate intervals from tblSchedIntervals
Dim Intervals As String
Dim IntervalData As String
Dim db As Database, rst1 As Recordset
Dim intName As String
Dim intData As String
Dim alpha As String
Dim schedvariable As String
Dim schedcount As String
Dim intArray(27, 5)
alpha = 1
schedcount = 1
Set db = CurrentDb
Set rst1 = db.OpenRecordset(&quot;tblSchedIntervals&quot;, dbOpenDynaset)
rst1.MoveFirst
Do Until rst1.EOF
intName = rst1.Fields(0)
intData = rst1.Fields(2)
intArray(alpha, 1) = rst1.Fields(0)
intArray(alpha, 2) = rst1.Fields(1)
intArray(alpha, 3) = rst1.Fields(2)
>>>dim &quot;&quot;intarray(alpha, 0)&quot;&quot; as string<<<
alpha = alpha + 1
schedcount = schedcount + 1
rst1.MoveNext
Loop
If [10klinesYes].OptionValue = 1 Then
Intervals = &quot;CS2K_gt_10K&quot;
ElseIf [10klinesNo].OptionValue = 2 Then
Intervals = &quot;CS2K_lt_10K&quot;
End If

Exit_CreateSchedule_Click:
Exit Sub

Err_CreateSchedule_Click:
MsgBox Err.Description
Resume Exit_CreateSchedule_Click

 

[snoopy75]

Which version of Access are you using? I know that Access 97 doesn't support arrays of strings, so that might be your problem.

--Ryan

 

[ArtPM]

Snoopy - thanks for the reply. I am using Access 2000.

I have tested the array and it does contain all 26 records with the three fields. I cannot get the right DIM Statement syntax that allows me to define the first field in the array as a variable.

 

[snoopy75]

Oh, I think I see what you want... Look in the VB help files for the &quot;Redim&quot; command. If I understand correctly, that may help you.

--Ryan

 

[ArtPM]

Ryan -- Thank you for your help

I don't follow you. The array has all of the information I need. When I try to enter the DIM statement to try to use the contents of the first field of the array as the name of the variable, I get syntax errors. I'm not clear what the redim will do for me. Again, I am trying to do the following: Dim intArray(1,1) As String ... and I expect that a new variable whose name = contents of intArray(1,1) is defined.

 

[RoyVidar]

Hi!

You can't have &quot;mixed&quot; types of elementes in an array. All elements must be of the same datatype.

You don't have to (or can't) declare a variable more than once in a sub/function (well with the exception of dynamic arrays)

dim arrTest() as string ' means all elements must be strings

So - if you need some elements of the array to be string, the whole array must be string (then convert the numerics)

strTest=clng(arrTest(N,M))
arrTest(N,M)=str(sngTest)

Also, the numerics (like the N and M over) - must be numeric (or converted) Your &quot;alpha&quot; is a string!!!

When declaring arrays you can either declare them as a fixed array

dim arrTest(12,14) as integer

or dynamicly

dim arrTest() as integer

The later variant must then be &quot;resized&quot; when needed:

Redim (Preserve) arrtest(N,M)

Preserve is optional and enables you to keep the original values.

So;

Your:
Dim intArray(27, 5) ' on the top and

>>>dim &quot;&quot;intarray(alpha, 0)&quot;&quot; as string<<< lower.

Won't work. See if some of the above, or a browse thru the helpfiles will get you where you want.

Roy-Vidar

 

[snoopy75]

RoyVidar is right. You can't individually Dim elements in an array, only the whole array. If you want to have an array for each record, with one row being a string and the rest of the rows being numeric, simply define 2 arrays. Dim one as a string and the other one the way you're doing it.

Dim intArray(27,5)
Dim strArray(27,1) As String

Then you can still use the same subscript for both arrays. Sorry I haven't been much help. I hope I've got it right this time.

--Ryan