Limiting the amount of Records on a Listbox (1 Viewer)

[Villarreal68]

Hello everyone,

I have a database that populates rooms with occupants for classes or meetings. I have two listboxes, one has the potential occupants and the other the list of already assigned occupants to the room. So far so good.
Then I have a button that will move any one selection on the "Potential Occupants list" to the "Assigned Room Occupants" with the following code on it:

    Dim varitem As Variant
    Dim count As Integer
    Dim rsCM As Object
 
    For Each varitem In OcupantesList.ItemsSelected
        count = count + 1
 
    Next varitem
    If count = 0 Then
        MsgBox "No se ha seleccionado ningún Ocupante"
        Exit Sub
    End If
 
    Set rsCM = CurrentDb.OpenRecordset("Asignaciones")
 
        For Each varitem In OcupantesList.ItemsSelected
        rsCM.AddNew
        rsCM.OcupanteID = CInt(Forms!NuevaAsignaciones!OcupantesList.ItemData(varitem))
        rsCM.InvitadoID = Forms!NuevaAsignaciones!txtInvitadoID
        rsCM.NumCuarto = Forms!NuevaAsignaciones!txtNumCuarto
        rsCM.FechaLlegada = Me.txtfechallegada
        rsCM.FechaSalida = Me.txtfechasalida
        rsCM.Update
    Next varitem

This works as expected. But with this code you can go forever adding occupants to a room.
What I want to accomplish is to be able to limit the amout of occupants to the number of chairs in the room. I already have a field that has the number of chairs in my rooms table, but i don't know how to modify the code to stop the assignments once the limit has been reached.

Any help would be truly appreciated. thanks!
René

 

[Scooterbug]

After you add a student, get a count of the number of occupants already assigned. Then compare that number to the number of available seats. When the two number become equal, you can prevent any more students from being added.

 

[Villarreal68]

Hello Scooterbug,

Thanks for the response. Yes I figure I would have to do a compare to acomplish this, but I'm not sure how to code that.

Would something like this is the way to go?:

Dim varitem As Variant
Dim varitem1 As Variant
Dim count As Integer
Dim count1 As Integer
Dim rsCM As Object
 
For Each varitem1 In AsigancionesList.ItemSelected
         count1 = count1 + 1
 
   Next varitem1 
   If count1= Me.MaxSeatingCount then
         MsgBox "There is no more seats availaibe in this room."
         Exit Sub
   End If
 
For Each varitem In OcupantesList.ItemsSelected
        count = count + 1
 
    Next varitem
    If count = 0 Then
        MsgBox "No se ha seleccionado ningún Ocupante"
        Exit Sub
    End If
 
Set rsCM = CurrentDb.OpenRecordset("Asignaciones")
 
        For Each varitem In OcupantesList.ItemsSelected
        rsCM.AddNew
        rsCM.OcupanteID = CInt(Forms!NuevaAsignaciones!OcupantesList.ItemData(varitem))
        rsCM.InvitadoID = Forms!NuevaAsignaciones!txtInvitadoID
        rsCM.NumCuarto = Forms!NuevaAsignaciones!txtNumCuarto
        rsCM.FechaLlegada = Me.txtfechallegada
        rsCM.FechaSalida = Me.txtfechasalida
        rsCM.Update
    Next varitem

Thank for your time.

 

[Villarreal68]

I figured a way to resolve the issue. i'm not sure if its the best way, but it works. here's what I did:

I added a textbox called [txtTotalAsignedList] that gave a total count of the AsignedList listbox then I added another textbox called [txtAvailableSeats] that did the math of =[MaxRoomCapacity]-[txtTotalAsignedList] then I changed the code to check the [txtAvalilableSeats] if equal to zero then push a MsgBox stating that there is no more room and exit the Sub.

If you know of a cleaner way to do it let me know. Thanks!

Here's the modified code:

    If Me.txtAvailableSeats = 0 Then
           MsgBox "There is no more room."
           Exit Sub
     End If
 
    Dim varitem As Variant
    Dim count As Integer
    Dim rsCM As Object
 
    For Each varitem In OcupantesList.ItemsSelected
        count = count + 1
 
    Next varitem
    If count = 0 Then
        MsgBox "No se ha seleccionado ningún Ocupante"
        Exit Sub
    End If
 
    Set rsCM = CurrentDb.OpenRecordset("Asignaciones")
 
        For Each varitem In OcupantesList.ItemsSelected
        rsCM.AddNew
        rsCM.OcupanteID = CInt(Forms!NuevaAsignaciones!OcupantesList.ItemData(varitem))
        rsCM.InvitadoID = Forms!NuevaAsignaciones!txtInvitadoID
        rsCM.NumCuarto = Forms!NuevaAsignaciones!txtNumCuarto
        rsCM.FechaLlegada = Me.txtfechallegada
        rsCM.FechaSalida = Me.txtfechasalida
        rsCM.Update
    Next varitem

 

[smig]

me.listboxName.listCount