Control Array in Access / Text Boxes as Objects

[frozbie]

Hi,

Is it possible to simulate a control array in MS Access?

I’m using a restricted version of Access 2000. I can use all normal features but some Active X controls are not licensed and it will not be possible to licence them in time I have available for my project.

I have a form which has 14 combo boxes and 24 text boxes to allow user to choose shift type and enter start and end times of shift. I know in VB 6 I have used control arrays which has vastly simplified the whole code.

I have experimented with treating the text boxes as objects and trying to create a string with the first part of the name and using numbers to differentiate between the textboxes but Access does not seem to like this.

The code I have tried is below:

Dim obTextBox As TextBox
Dim str As String

str = "Forms!frm_Shift_Entry_3!txtFST2"

‘Set obTextBox = Forms!frm_Shift_Entry_3!txtFST
'Set obTextBox = str

obTextBox.Name = "txtFST2"
obTextBox.Value = Format("12:35", "Short Time")
'b = 7
'obTextBox.Name = "txtFST" & b
'obTextBox.Value = Format("17:12", "Short Time")

commented out sections are other options I have tried.

Can anyone point out any mistakes I’m making or advise whether this is possible in Access?

Thanks

Frozbie

 

[Sergeant]

When I loop through sequentially named controls, I use this syntax and it works:

For i = 1 to 6
  Ctl = "txtField" & i
  Me.Controls(Ctl).Whatever = Whatever
Next i

 

[Mile-O]

It's not possible in Access as each control has their own event and there can't be one event that deals with all the controls.

What you could do though...is...

Create a function on your form's Class module and pass in a byte as a parameter. Assuming each textbox is called TextBox with a number tagged on the end...And I'll leave the return value as a Variant.

i.e.

Private Function MyControlArray(ByVal bytControlID As Byte)
    Me("TextBox" & bytControlID).BackColor = 0
End Function

Not the best, but you can put the function in the each control's relevant event and pass the number associated with their name.

 

[Rich]

Why do you have so many controls just to select a shift and enter start/end times?

 

[Mile-O]

Why do you have so many controls just to select a shift and enter start/end times?

That was the question I was thinking and forgot to ask.

 

[frozbie]

The client is always right! Except when you can persuade them otherwise

Hi everyone,

Thanks for your responses. I'm at home now but will try these out tomorrow.

To answer your questions Rich and SJ, if there is a better/simpler way to deal with the form I would like to use it, any suggestions very much appreciated

I have to perform validation checks on the shift times:
shift does not conflict with existing shift.
start time is same as or greater than 11 hours since last shift end time (due to EU working directive.
End time less than or same as 11 hours before next shift start for same reason

I have assumed that I need to do this validation in code and using textboxes and combo boxes seem to be the best way to structure the form.

Oh yeah, and the other thing is, the client wants to view seven days worth of shifts at a time and there can be two shifts in a day which makes 14 start time boxes, 14 end time boxes and 14 boxes for choosing which type of shift!

In VB6 I would have used a control array. If I had the option, I might have used a data/flexgrid but the clients IT system is severaly locked down and it will take months and more money than they want to spend to change their Access configuration to allow these to be used.

It has been an interesting challenge so far...

Frozbie

 

[Rich]

Three textboxes, a query,a continuous form and a normalised db will give you 7days displayed records

 

[Sergeant]

I don't know now...I may have totally missed the point on that one. It was early in the morning.

 

[tatarik]

Thanks Sergeant! Your answer really helped me