dynamically change variable

[s0rtd]

hi guys, this is a bit of a complex one, well to me anyway.
I have a form with 119 boxes on it named box1 to 119 these change colour according to whether the time is available or not.

i have a piece of code that is invoked if you double click on a box, the code loads a form which displays information relating to that job (ie the box you double click)

this is the code behind the double click event of a single box.

Private Sub box1_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

    DoCmd.OpenForm "frm_weekstartservice"
    Forms![frm_weekstartservice].Visible = False


stDocName = "Service Jobs"
    
    stLinkCriteria = "[Job Number]=" & BillJob(1)
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

now instead of writing that code out 119 times (once for every box) - and changing the array variable BillJob(1) to BillJob(2) for box to etc etc ...

is there an easy way to have these dynamically adjusted?? or create a module, and call that module??

i hope what i have written is understandable..

cheers
Jurgen

 

[dgm]

Private Sub box1_DblClick(Cancel As Integer)
    openServiceJob 1
End Sub

Private Sub box2_DblClick(Cancel As Integer)
    openServiceJob 2
End Sub

Sub openServiceJob(jobNo As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

    DoCmd.OpenForm "frm_weekstartservice"
    Forms![frm_weekstartservice].Visible = False

    stDocName = "Service Jobs"
    
    stLinkCriteria = "[Job Number]=" & BillJob(jobNo)
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Something like that maybe, although u still have to enter code for all 119 boxes. Just a bit less for each one now.

 

[s0rtd]

cheers dgm, that just what i thought of doing, well even better, i was using a public function.