Loop and String Concatenation problem

[Ebes1099]

I'm having a problem with a loop I'm trying to run. I have a field on my form called mstrMembers. When a button is clicked, I want to copy the value in that field to 13 other fields. Those fields are names Mbrs0 through Mbrs12. So I'm running a loop and concatenation the String Mbrs & a (a is the loop variable to run from 0-12. I have a variable called result that puts the two strings together to make the field names of the places I want to copy. But then when I put result = mstrMembers instead of getting the field name Mbrs* which result is holding, it just sets the value of the variable 'result' to the value in mstrMembers. I want it to see what the value of result is and find that field on the form and put the value from mstrMembers in there. I can see why it's not working but I don't know how to fix it. I've attached the code. Thanks for the help.

Private Sub FillButton_Click()
Dim a As Integer
Dim result As String
Dim front As String

front = "Mbrs"

For a = 0 To 12
result = front & a
result = mstrMembers
Next a

End Sub

 

[dhookom]

I can't imagine why you would want to do this but:

Private Sub FillButton_Click()
    Dim a As Integer
    Dim result As String
    Dim front As String
    result = Me.mstrMembers
    front = "Mbrs"

    For a = 0 To 12
        Me(front & a) = result
    Next a
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Ebes1099 . . .

Straight up using only one dim statement:

[blue]Private Sub FillButton_Click()
   Dim a As Integer

   For a = 0 To 12
      Me("Mbrs" & CStr(a)) = Me.mstrMembers
   Next
    
End Sub[/blue]

Note: I agree with [blue]dhookom[/blue] ... this makes no sense. Actually I'm thinking [red]homework[/red] or some kind of test. In any case you have it!

btw; welcome to [blue]tek-tips1[/blue] do have a look at one of the links at the bottom of my post. the links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. again . . . welcome to [blue]tek-tips1[/blue] [blue]its worthy reading1[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Ebes1099]

Thanks for the help guys, that worked. I'll explain to you why I'm doing this since you both think its crazy. Maybe you know a better way and that's why it looks funny to you.

I have a form to predict the number of members in a group for the year 2010. Each month in the year is it's own field because I need to multiply the members in a month times a dollar amount set for that month (which is another field). Most of the time the membership won't change so I have a box on the left where the user can enter the members and click the button and copy that value into every box. And the boxes are still controlled by the field on the table behind it so they are editable. So that's why. If you think there's a better way, I'm listening. But for now, thanks for the help.

 

[PHV]

Each month in the year is it's own field
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886