Loop through form's field names

[Pendragon]

Programming for sports tournaments that may run anywhere from one to ten
days. Through tabledefs I've created a temp table with field names as Day1,
Day2, Day3, etc. based on the length of the tournament. The temp table is
populated with records of the court names (i.e., field name = CourtName and
records are Court1, Court2, Court3, etc.). The Day1, Day2, Day3 records are
all boolean and are being used to indicate what courts are being used on what
day (boolean because Help noted there was no vbYesNo option for a field type).

I am utilizing a form based on the temp table so that a user may simply
click on the check box to activate a court on a given day. Since the maximum
number of days is ten, I set up ten checkboxes and each is named chkDay_
with the appropriate day number appended to the end of the name, e.g.,
chkDay3.

In form view, all days' checkboxes appear regardless of whether or not the
tournament runs on those days (i.e., a three day tournament still shows days
four through ten). I thought perhaps I could programmatically make visible
only those checkboxes for the days of the tournament.

In the form's OnOpen property, I've set an SQL statement to grab the form's
underlying fields. Assume appropriate variables have been defined. The
first six fields of the SQL statement are always the same and are not needed
for my purpose; therefore my For...Loop begins at 7.

set rs = db.OpenRecordset(sSQL)
For x = 7 to rs.Fields.Count
fldName = "chkDay" & str(x - 6) ' to get chkDay1 and so forth

Next x

What I need to know how to do is reference the appropriate check box field
on the form based on fldName and make it visible, e.g., Me.chkDay1.Visible =
True. How do I do this utilizing the variable fldName? Or is there a better
method?

Thanks in advance.

 

[Jim Burke in Novi]

Assuming that 'fldName' will have the name of the control, use
Me.Controls(fldName).Visible = true/false

 

[David H]

You would be better off spinning out the 'days' to a secondary table. Using
something like this...

TournamentID CourtNo Date

What happens if there's a three week long tournament?

You would then use a subform to select the court and the date that it's
committed to the tournament.

Much easier across the board and no coding needed.

 

[pietlinden]

Programming for sports tournaments that may run anywhere from one to ten
days.  Through tabledefs I've created a temp table with field names as Day1,
Day2, Day3, etc. based on the length of the tournament.  The temp tableis
populated with records of the court names (i.e., field name = CourtNameand
records are Court1, Court2, Court3, etc.).  The Day1, Day2, Day3 records are
all boolean and are being used to indicate what courts are being used on what
day (boolean because Help noted there was no vbYesNo option for a field type).

I am utilizing a form based on the temp table so that a user may simply
click on the check box to activate a court on a given day.  Since the maximum
number of days is ten, I set up ten checkboxes and each is named chkDay_  
with the appropriate day number appended to the end of the name, e.g.,
chkDay3.

In form view, all days' checkboxes appear regardless of whether or not the
tournament runs on those days (i.e., a three day tournament still shows days
four through ten).  I thought perhaps I could programmatically make visible
only those checkboxes for the days of the tournament.

In the form's OnOpen property, I've set an SQL statement to grab the form's
underlying fields.  Assume appropriate variables have been defined.  The
first six fields of the SQL statement are always the same and are not needed
for my purpose; therefore my For...Loop begins at 7.

set rs = db.OpenRecordset(sSQL)
For x = 7 to rs.Fields.Count
   fldName = "chkDay" & str(x - 6)         ' to get chkDay1and so forth

Next x

What I need to know how to do is reference the appropriate check box field
on the form based on fldName and make it visible, e.g., Me.chkDay1.Visible =
True.  How do I do this utilizing the variable fldName?  Or is there a better
method?

Thanks in advance.

like this:
Private Sub Command23_Click()
Dim intCounter As Integer

For intCounter = CInt(Me.txtStartNo) To CInt(Me.txtEndNumber) Step
2
'---THIS IS THE LINE YOU WANT....
Me.Controls("Check" & CStr(intCounter)).Visible = False
Next intCounter
End Sub

 

[Pendragon]

Thanks to all three for the suggestions. I used Jim's suggestion - when it
didn't work, I realized I had actually set the form to datasheet view so
using the visible property wasn't appropriate. Instead,

Me.Controls(fldName).ColumnHidden = true/false

worked well.

For David - I am actually setting the table as you described. What I needed
for the user was to be able to see a crosstab-type of visual with courts down
the left and dates across the top AND be able to click on/off those
courts/days in use. This I set into a temp table and am using a subform to
display. My initial question concerned the display of the subform/datasheet
so that only relevant days would show. My next question would be how to
programmatically create fields on a form based on the number of fields
(days), but I'll save that for another day.

Thanks everyone again!