Get around repetitive control code

[MFrost]

Hi folks.
I'm trying to create a form that can be update with (x) number of subforms with minimal code changing each time. The form is actually a queue monitoring application; each subform being a separate queue.

Depending on the time of year the number of queues is increased or decreased to suit the demand and pressure on the queues (increasing or decreasing the number of subforms).

Ive currently managed to get the code needing to be added each time i add a queue down to:

    Me!NetReports1.Form!ReportQueueNo.SetFocus
    Me!NetReports1.Form!ReportQueueNo.Text = "124"
    Me!NetReports1.Form!NetReports1.RowSource = "NetReports1 Report Audit"
    Me!NetReports1.Form!txtQueueNum.Caption = "NetReports 1"
    Me!NetReports1.Form!NetReports1_LastErrorDetail.RowSource = "SELECT [NetReports1 LastError].NONFAILS, [NetReports1 LastError].LASTFAIL_DATE, [NetReports1 LastError].LASTFAIL_TYPE, [NetReports1 LastError].LASTFAIL_MESSAGE FROM [NetReports1 LastError];"
    Me!NetReports1.height = sFormHeight
    Me!NetReports1.top = top
    Me!NetReports1.Left = 0
    Me!NetReports1.Width = WindowWidth
    Me!NetReports1.Form!NetReports1.height = sFormHeight - 600
    top = top + sFormHeight

and i just have to put another copy of the subform onto the main form increasing the name by 1 (e.g. NetReports2, NetReports3, NetReports4)

Is there any way to prevent repeating this code for every queue and using some kind of array to reference each subform? (e.g. loop round for number of queues using NetReports(x)) I'm under the impression that vba does not support control arrays like visual basic 6 does - but i was hoping there was a way around this?

The idea is that the only code i have to change is the number of queues. (e.g. NumberOfQueues = 5)

I'm probably dreaming, but it would make things a lot easier if this was possible.

I hope you can understand my (probably) shoddy explanation! Sorry!

Thanks a lot for any assistance. I look forward to any response.

Mathew

 

[DCrake]

So how many sub forms do you currently have on your main form?

 

[MFrost]

Currently have 6. They are all instances of the same form I just change the control source at runtime.

We will be moving up to 8 queues shortly, but depending on load IT will deploy more Virtual machines for queues on demand.

 

[DCrake]

Cant you just have one subform and change the recordsource of the subforms underlying query?

 

[MFrost]

Cant you just have one subform and change the recordsource of the subforms underlying query?

Well I need to be able to see all the queues, with only 1 subform I will only be able to view one queue at a time. Unless i have misunderstood what you have said, then i apologise.

As far as im aware i am already changing the recordsource of the subforms underlying queries with the block of code a create for each instance of a queue.