Loop through textboxes to find empty then add text

[SuzieQue]

I have a form to assign students to a classroom. On the form is an unbound combobox (cbo_StudentList) that allows user to choose a student. I created a command button (command_AddStudent). I also have 40 textboxes (bound to a table) where I want students assigned to the class to be listed.

I need to figure out how to add the combobox values to the next empty textbox when the command button is clicked. I tried loop and if statements based on a tag assigned to the 40 textboxes but it adds the student name to all 40 textboxes. If I select a different student and click the command button it overwrites all 40 with the new name.

Is there a way to keep the students assigned to the class in textboxes, loop through them to find the next empty box and fill it (and only it) with the new name. See code below. Note I am recording the first and last names of the students that appear in column 3 & 2 of the query for the combobox.

Private Sub Command_AddStudent_Click()
Dim ctl As Control
Dim addstudent As String
Dim student As String
 
addstudent = Me.Cbo_StudentList.Column(3) & " " & Me.Cbo_StudentList.Column(2)
 
For Each ctl In Me.Controls
     If ctl.tag = "SeatsAvailable" Then
        If ctl.Value = Not Null Then
          ctl.Value = student
          Else
          ctl.Value = addstudent
        End If
    End If
Next ctl
End Sub

[TheSmileyCoder]

Hi and Welcome to Bytes

Nice and detailed explanation for a first time poster, I hope we will hear more from you :)
First:
You cannot write this:

If ctl.Value = Not Null

You can try either:

If not IsNull(ctl.value) then

or

If ctl.Value & ""<>"" then

The latter of the two also checks for zero length strings.

Secondly, I hope you realize that it seems your design does not follow best practice in regards to Database Normalization. I strongly urge you to take the time to read through the article, it will save you alot of grief down the road.

Third your code used the string variable Student, yet it is never assigned a value.

Now finally moving on to your question, looping through the controls should certainly be an option.

'Loop through all controls on form
  For Each ctl In Me.Controls
    'Check for seat tag
    If ctl.tag = "SeatsAvailable" Then
      'Is seat taken?
        if ctl.value & ""<>"" Then
          'Seat available, fill seat
          ctl.value=AddStudent
        End If
    End If
  Next ctl

[NeoPa]

You may also want to introduce a concept of the order the fields, or controls on the form, appear in. The order from the collection may not match that which you want to assign values in.

[SuzieQue]

Thanks for the advice and help. I was struggling on how to structure this table among the others I created. The article made a lot of sense so back to the drawing board for me.

[TheSmileyCoder]

Your welcome.
Its a pleasure answering structured and detailed questions.