Long time lurker, first time poster. I have a pretty simple database in access to update the computer lab seating chart based on an unbound combo box where a teacher can select his/her class.

what I have right now is:

Private Sub Combo29_AfterUpdate()
If DCount("[StudentName]", "SelectedClass", "[StudentNumber]=1") <> 1 Then Me!Computer1.Caption = "" Else Me!Computer1.Caption = DLookup("[StudentName]", "SelectedClass", "[StudentNumber] = 1")

It technically works just fine but instead of repeating the same line, changing number 1 to the number 2, and so on for all 25 computers, I'd prefer to have something to the effect of:

Private Sub Combo29_AfterUpdate()
Dim PCnumber as Integer
PCnumber = 0
NextPC:
PCnumber = PCnumber + 1
If DCount("[StudentName]", "SelectedClass", "[StudentNumber]=PCnumber") <> 1 Then Me!Computer & PCnumber & .Caption = "" Else Me!Computer & PCnumber & .Caption = DLookup("[StudentName]", "SelectedClass", "[StudentNumber] = PCnumber")
If PCNumber = 25 Then End Else goto NextPC
End Sub

I've browsed several boards, but I can't seem to get the syntax right to concatenate the variable PCnumber as part of the reference to the label on the form. Currently I'm doing it the "long way", repeating the whole line for each computer. This isn't the first time however that this deficiency of mine has made more work for me, and I'm hoping to learn it not just for this issue, but to make my life easier in the future.

I'm using Access 2007 and Visual Basic says it's 6.5.

Thanks,

Chuck

• ms-access
• access-vba

I've not used Access 2007 but this will probably work as a replacement for Me!Computer & PCnumber & .Caption:

Me.Controls("Computer" & PCnumber).Caption

I'd also be inclined to change the DCount functions to:

DLookup("[StudentName]", "SelectedClass", "[StudentNumber] = " & PCnumber)

... but I don't often use them either way so it may work.

As an aside, you can use the following loop structure instead of the GoTo in your code:

For PCnumber = 1 to 25 Step 1
    ' code
Next PCnumber

Using GoTo statements for anything other than Error Handling is generally considered a bad idea. It's much better to use properly formed loops or to create separate subs/functions to handle the task you've put under the GoTo statement (the latter doesn't apply in your case).

I would write the code something like this.

Private Sub Combo29_AfterUpdate()
    Dim PCnumber as Integer 'Instantiates as 0
    Do Until PCNumber = 25
        If DCount("[StudentName]", "SelectedClass", "[StudentNumber]=" & PCnumber) <> 1 Then 
            Me.Controls(Me!Computer & PCnumber).Caption = ""
        Else
            Me.Controls(Me!Computer & PCnumber).Caption = DLookup("[StudentName]", "SelectedClass", "[StudentNumber] = " & PCnumber)
        End If      
        PCNumber = PCNumber + 1
    Loop
End Sub