Append variable to control name

[Duck]

I am trying to add three rows to a table from one form. I want the
same field in the table to updated from only one of three controls on
the form (txtSource1, txtSource2, txtSource3) for each of the three
new records. Here is the code that's not working:

For intI = 1 To 3
With rstFunding
.AddNew
!ClientID = Me.txtClientID
!Source = intI
!Amt = Me.txtSource & intI
!StartDate = Me.txtStartDate
!EndDate = Me.txtEndDate
.Update
End With
Loop

I get an error stating "Method or Data Member not Found" with
'txtSource' highlighted

 

[Duck]

I am trying to add three rows to a table from one form.  I want the
same field in the table to updated from only one of three controls on
the form (txtSource1, txtSource2, txtSource3) for each of the three
new records.  Here is the code that's not working:

For intI = 1 To 3
    With rstFunding
        .AddNew
        !ClientID = Me.txtClientID
        !Source = intI
        !Amt = Me.txtSource & intI
        !StartDate = Me.txtStartDate
        !EndDate = Me.txtEndDate
        .Update
    End With
Loop

I get an error stating "Method or Data Member not Found" with
'txtSource' highlighted

Well after messing around a little more I found that changing the one
line to:

!Amt = IIf(intI = 1, txtSource1, IIf(intI = 2, txtSource2,
txtSource3))

accomplished what I wanted, but is there a more eligant method?

 

[boblarson]

That would be:

!Amt = Me.Controls(Me.txtSource & intI)

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Duck]

That would be:

!Amt = Me.Controls(Me.txtSource & intI)

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials athttp://www.btabdevelopment.com

__________________________________







- Show quoted text -

Thanks for the reply but when I tried:

!Amt = Me.Controls(Me.txtSource & intI)

I got the exact same error I was getting before: "Method or Data
Member Not Found" with .txtSource highlighted

 

[Douglas J. Steele]

I think Bob may have erred here.

Try:

!Amt = Me.Controls("txtSource" & intI)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That would be:

!Amt = Me.Controls(Me.txtSource & intI)

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials athttp://www.btabdevelopment.com

__________________________________







- Show quoted text -

Thanks for the reply but when I tried:

!Amt = Me.Controls(Me.txtSource & intI)

I got the exact same error I was getting before: "Method or Data
Member Not Found" with .txtSource highlighted