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("tblSchedIntervals", 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 ""intarray(alpha, 0)"" as string<<<
alpha = alpha + 1
schedcount = schedcount + 1
rst1.MoveNext
Loop
If [10klinesYes].OptionValue = 1 Then
Intervals = "CS2K_gt_10K"
ElseIf [10klinesNo].OptionValue = 2 Then
Intervals = "CS2K_lt_10K"
End If
Exit_CreateSchedule_Click:
Exit Sub
Err_CreateSchedule_Click:
MsgBox Err.Description
Resume Exit_CreateSchedule_Click
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("tblSchedIntervals", 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 ""intarray(alpha, 0)"" as string<<<
alpha = alpha + 1
schedcount = schedcount + 1
rst1.MoveNext
Loop
If [10klinesYes].OptionValue = 1 Then
Intervals = "CS2K_gt_10K"
ElseIf [10klinesNo].OptionValue = 2 Then
Intervals = "CS2K_lt_10K"
End If
Exit_CreateSchedule_Click:
Exit Sub
Err_CreateSchedule_Click:
MsgBox Err.Description
Resume Exit_CreateSchedule_Click