Trying to dynamically change control names on Access Form using VBA

[kpilsbury]

I am trying to reference different controls on an access form:


txtCompleteStartDate13 txtCompleteEndDate13
txtCompleteStartDate26 txtCompleteEndDate26
txtCompleteStartDate52 txtCompleteEndDate52
txtCompleteStartDate65 txtCompleteEndDate65

And run the same queries for each set of controls, they are basically different date ranges.

I have everything working fine except the following: I am wanting to use retper (The number in the array) to point me to a different text box on the form. But the variable keeps returning a string instead of the value on the form.

Any ideas? Below is part of the code I am having issues with.

Dim retstart As Date
Dim retend As Date



DoCmd.RunSQL "DELETE FROM TMP_REGION_REPORT_DATA" (Clears Table for input)

retlist = Array("13", "26", "52", "65")


For Each retper In retlist

retstart = "[Forms]![ANNUAL_RETENTION_MAIN_new]![txtCompleteStartDate" & '" & retper & "' & "]"
retend = "[Forms]![ANNUAL_RETENTION_MAIN_new]![txtCompleteEndDate" & '" & retper & "' & "]"

Kevin

[kpilsbury]

I actually found another post from earlier that helped me resolve this: (Referencing a textbox with a VBA String)

Here is my solution:
retlist = Array("13", "26", "52", "65")


For Each retper In retlist


ctlstart = "txtCompleteStartDate" & retper
ctlend = "txtCompleteEndDate" & retper


retstart = [Forms]![ANNUAL_RETENTION_MAIN_new].Controls(ctlstart).Value
retend = [Forms]![ANNUAL_RETENTION_MAIN_new].Controls(ctlend).Value

This allows me to loop through my array and change to the different date ranges in the multiple text boxes on the form.

[pbaldy]

Try

retstart = [Forms]![ANNUAL_RETENTION_MAIN_new]("txtCompleteStartDate" & retper)

[orange]

Kevin,

Please show us all the code. Where do you Dim retper? Array?

You can name controls on a form with sequential numbers, then use a loop and index to identify the controls.

But first how about telling us WHAT you're trying to do. Simple, clear English with no jargon would be a big help for the readers.