Is this loop possible in vba?

[irishjoe]

Is this possible?

Instead of,

Me.answer1.Value = getProb(Me.bob1.Value, Me.dave1.Value, Me.Tim1.Value)
Me.answer2.Value = getProb(Me.bob2.Value, Me.dave2.Value, Me.Tim2.Value)
Me.answer3.Value = getProb(Me.bob3.Value, Me.dave3.Value, Me.Tim3.Value)
Me.answer4.Value = getProb(Me.bob4.Value, Me.dave4.Value, Me.Tim4.Value)
Me.answer5.Value = getProb(Me.bob5.Value, Me.dave5.Value, Me.Tim5.Value)


Have,

For X = 1 to 5
Me.answerX.Value = getProb(Me.bobX.Value, Me.daveX.Value, Me.TimX.Value)
loop

 

[Mile-O]

This'll do it:

Dim x As Integer
For x = 1 To 5
    Me("answer" & x) = getProb(Me("bob" & x), Me("dave" & x), Me("Tim" & x))
Next x

...but the bigger problem is that it appears your database is not normalised. What happens when you have to add Jim? You'll need to edit the code. If you were to design your database properly you wouldn't have to address this ever again.

 

[irishjoe]

I have normalised my database.
This is for a form where the user modifies 3 values and a score is generated from the results. The results are not being stored to save space.

I was just going to go through each record on afterUpdate to get the correct values.

The way I would liked to have done it is to set the form as "default view = Continuous forms" instead of "Default view = Single form". The problem is that when I get a score for the 3 values, it changes all the scores not just one.
Do you know of a guide for playing about with continuous forms? I have always used forms for one record at a time in the past so I am not that experienced with them.

Thanks for answering my question.
Joe.

 

[Mile-O]

I have normalised my database.
This is for a form where the user modifies 3 values and a score is generated from the results. The results are not being stored to save space.

The score is a dependant value (a calculation) meaning that you may think your database is normalised. It's not. It's not about saving space (i.e. one field instead of three) but retaining the base data in a logical manner.

...but good luck anyway.

 

[Mile-O]

The problem is that when I get a score for the 3 values, it changes all the scores not just one.

Because you need a one-to-many relationship (one examinee, many results) that need to be represented in a form & subform combination. But you're storing the score so its not feasible.

 

[irishjoe]

Thanks a lot for your help.
You have pointed me in the right direction.