Creating a list of variables from record data

[ArtPM]

Is it possible to create a list of variables from a table?

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?

Thanks in advance!

 

[Paddyo]

Im not too sure If I understand what you want to do but this may help.

How do you know which milestone to use in your calculation. Are they numbered 1,2,3 etc?

If so why not setup un a function which reads a record in the milestone table based on whatever your reference is and use this function in your calculation.

Public Function ReadMileStone(XXX)
Code to read record from table
Readmilestone = ValueRequired
End function


Calculation:
a = ReadmileStone(1) * 20

Not sure if this makes sense to you but hope it helps

 

[ArtPM]

Paddyo,

Thanks for your reply. I'm afraid I didn't explain myself very well.

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.

Let me know if this is any clearer than my first post.

Thank you again!!!
Art

 

[Paddyo]

Sorry for the misunderstanding.

Am I right in saying the following:

Each record has 26 milestone fields at present?
You are concerned in case a new milestone field may be created?
You want your program to recognise that this new field has been created?

I know you that in VB you can reference fields in a recordset using variables as follows:

Lets say we have an array with all for the milestones
Dim MileStone(NumberofMileStones)
(Numberofmilestones could be read from a parameter file)
(Read the recordset - recset)
(Setup the array from the recordset as follows)

For X = 1 to NumberofMileStones
MileStone(X) = recset("MileStone" + CStr(X))
Next X

You could do the same when writing back to the table.
Im not sure if this works in VBA.

I hope I understood your problem and I hope that this helps

Good Luck
Paul