Availablilty check on a "For...Next" statement (1 Viewer)

[Villarreal68]

Hello everyone,

I'm working on a database for a friend that is an older person (technically challenged) and I'm trying to make it as easy aspoisslbe for him.

I've run into an issue that I cant figure out how to solve.

The following code works perfect (thanks to all the code in this site's repository of info.)

I need to include as part of the FOR...NEXT loop, code that will check the availability of beds before the next record gets assigned. If I do an individual assignment from the listbox the 6th "IF...THEN" statement at the top of the code handles that, but if there are 2 beds available and the user selects 3 occupants, the FOR...NEXT code assignes all 3 to the room, but there are only 2 beds left.

How can I code into the FOR...NEXT the checking of bed availalbilty before it posts the next selection?

Any help will be truly appreciated.

René

On Error GoTo err_AddGuest
'Verify the Check-In date is filled.
If Me.txtfechallegada = "" Then
MsgBox "Verifique que la 'Fecha de Llegada' este seleccionada", , "Asignación de Ocupantes"
Me.txtfechallegada.SetFocus
Exit Sub
End If
'Verify the Check-Out date is filled.
If Me.txtfechasalida = "" Then
MsgBox "Verifique que la 'Fecha de Salida' este seleccionada", , "Asignación de Ocupantes"
Me.txtfechasalida.SetFocus
Exit Sub
End If
'Verify a Guest Name is selected.
If IsNull(Me.Combo0) Then
MsgBox "Primero Seleccione el nombre de Huésped.", , "Asignación de Ocupantes"
Me.Combo0.SetFocus
Exit Sub
End If
'Verify a Host Name is selected.
If IsNull(Me.Combo6) Then
MsgBox "Primero Seleccione el nombre de Anfitrión", , "Asignación de Ocupantes"
Me.Combo6.SetFocus
Exit Sub
End If
'Keep Count of Ocupants assigned for this guest (if all assigned notify and exit)
If Me.txtOcupantesNoAsignados = 0 Then
MsgBox "Ya han sido asignados todos los Ocupantes de este Huesped.", , "Asignación de Ocupantes"
Exit Sub
End If
'Keep Count of Available beds from Host (If all filled Notify and exit)
If Me.txtDisponibles = 0 Then
MsgBox "Ya no hay mas campo en este cuarto.", , "Asignación de Ocupantes"
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
 
'Verify an Occupant Name is selected
Next varItem
If count = 0 Then
MsgBox "No se ha seleccionado ningún Ocupante", , "Asignación de Ocupantes"
Exit Sub
End If
'Do the Assignment of Guest Occupants to Host beds.
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.AnfitrionID = Forms!NuevaAsignaciones!txtAnfitrionID
rsCM.FechaLlegada = Me.txtfechallegada
rsCM.FechaSalida = Me.txtfechasalida
rsCM.Update
Next varItem
 
exit_AddGuest:
Me.OcupantesList.Requery
Me.AsignadosList.Requery
rsCM.Close
Set rsCM = Nothing
 
Exit Sub
err_AddGuest:
If Err.Number = 3022 Then
MsgBox "Ya esta asignado este Ocupante."
ElseIf Err.Number = 3058 Then
MsgBox "Favor de Seleccionar un Anfitrion Primero."
ElseIf Err.Number = 3314 Then
MsgBox "Favor de Primero Seleccionar Las fechas de llegada y Partida o un Anfitrion."
Else
MsgBox Err.Number & Err.Description
End If
Resume exit_AddGuest