How do I test for various combinations of data, before updating a record?

[anb001]

I’m currently working on a POB (Persons On Board) database, and have a problem when needing to do a check if a cabin/bed is occupied or no.

A little information on how it works:
When planning crew on board an oil rig, you can choose from e.g. 60 cabins, with e.g. 2 beds in each cabin. Cabins are normally numbered e.g. “310”, which is third floor, cabin 10, and beds are “A” and “B”. Two persons normally share a cabin. One is working day shift, the other one night shift. You will normally not have two persons on day shift or two persons on night shift in the same cabin (called ‘hot-bunking’). Only in extreme cases.

What I’m after is a way for the database to check if a cabin is either in use already, or planned in use, and if so-called ‘hot-bunking’ is occurring, when I click a command button (there are various txtboxes / option groups on the form, where user can enter/choose cabin/bed/Working shift etc).

I have a query with needed information, i.e.:
- CabinNumber
- Bed
- InUse (yes/no)
- PlannedForUse (yes/No)
- WorkingShift
And then of course fields for the name of the person occupying the cabin / bed.

Example:
A person is coming on board and is planned for Cabin 310, Bed A, and he will be working Day Shift. When clicking the command button, I need to check if:

- Cabin 310, Bed A being occupied/planned occupied by any one else? If in use, then update not possible, and action to be taken. If not in use, then proceed to below.
- Cabin 310, Bed B is in use/planned in use, and if it is, it needs to check if this is Day Shift or Night Shift. If this is Day Shift, then update not possible, and action to be taken. If Night Shift, then all ok, and information can be updated.

I’m not looking for some finalized code, but I would appreciate if someone can assist with a “push” in the right direction.
Thanks.
Anders

 

[anb001]

I'm trying to get it to work by using nested 'Select Case' statements.

This is my idea (just part of the code):

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qryAllCabins WHERE [Cabin] = '" & Me.txtCabinNo & "'")

With rs
        Select Case .Fields("InUse") Or .Fields("PlannedUse")
        Case True
            MsgBox ("This cabin/bed is already in use.")
        Case False
            'Select Case
            'Case etc
            
            'End Select
        End Select
          
End With

Set db = Nothing
Set rs = Nothing

Setting the recordset as above, gives me two records, which e.g. is Cabin 325, Bed A and Cabin 325, Bed B.

I'm getting somewhat stuck, since the first Select Case statement checks for Cabin 325, i.e. both beds. What I need is to check first for e.g. Cabin/Bed 325 A, and then to see if Cabin/Bed 325 B is in use for the same shift.

Can I include, in the first Select Case statement, something like ".fields("InUse") where .fields("Bed") = me.txtbedNo"

Thanks.

 

[Setty]

Hi,

I give here some air code assuming there are consistent records for Bed A and Bed B for all cabins. I have used some variables to store the bed available status and its current working shift. There will be two records for each cabin (Bed A and Bed B). You have to loop through both records and store the status in the variables. Then use If constructs for various combinations. Modify the code as appropriate and test thoroughly for all combination of data.

Surendran Setty

Code:

Sub BedAvlStatusProc()

On Error GoTo Err_Handler

Dim db As Database
Dim rst As dao.Recordset
Dim stSQL As String, stMsg As String

Dim Bed_A_Avl_Status As String, Bed_B_Avl_Status As String
Dim Bed_A_Cur_Shift As String, Bed_B_Cur_Shift As String

Set db = CurrentDb()

stSQL = "SELECT * FROM qryAllCabins WHERE [Cabin] = '" & Me.txtCabinNo & "'"
Set rst = db.OpenRecordset(stSQL)

If rst.BOF And rst.EOF Then

    MsgBox "No Data found for cabin " & Me.txtCabinNo
    rst.Close
    GoTo Exit_Proc

End If
        
Bed_A_Avl_Status = vbNullString
Bed_B_Avl_Status = vbNullString

Do Until rst.EOF

    If rst!Bed = "A" Then

        If rst!InUse = True Then
            Bed_A_Avl_Status = "Not Avl"
        Else
            Bed_A_Avl_Status = "Avl"
            Bed_A_Cur_Shift = rst!workingshift
            If rst!PlannedForUse = True Then
                Bed_A_Avl_Status = "Not Avl"
            End If
        End If

    End If

    If rst!Bed = "B" Then

        If rst!InUse = True Then
            Bed_B_Avl_Status = "Not Avl"
        Else
            Bed_B_Avl_Status = "Avl"
            Bed_B_Cur_Shift = rst!workingshift
            If rst!PlannedForUse = True Then
                Bed_B_Avl_Status = "Not Avl"
            End If
        End If

    End If

    rst.MoveNext

Loop

rst.Close

If Bed_A_Avl_Status = "Avl" And Bed_B_Avl_Status = "Avl" Then

    'Both beds are available. You have choice to update either Bed A or Bed B
    'If you follow some convention like Bed A for day shift and Bed B for Night shift, then
    'update the appropriate bed as per user working shift in the form
    'If user working shift is Day, update Bed A and also ensure to update Bed B working shift as night shift or vice versa
    'After update, use this statement to exit the proc
    
    GoTo Exit_Proc

End If

If Bed_A_Avl_Status = "Avl" And Bed_B_Avl_Status = "Not Avl" Then

    'Only Bed A is available. Check whether working shift of Bed A matches the user working shift

    If Bed_A_Cur_Shift = Me!workingshift And Not (Bed_B_Cur_Shift = Me!workingshift) Then
        'Proceed Update
    Else
        'Bed A Shift - Hot Bunking - ? Decide here whether to update or not
        'If no hot bunking, show a message to the user and
        'use this statement to exit the proc
        stMsg = "Your Message"
        MsgBox stMsg
        GoTo Exit_Proc
    End If

End If

If Bed_B_Avl_Status = "Avl" And Bed_A_Avl_Status = "Not Avl" Then

    'Only Bed B is available. Check whether working shift of Bed B matches the user working shift

    If Bed_B_Cur_Shift = Me!workingshift And Not (Bed_A_Cur_Shift = Me!workingshift) Then
        'Proceed Update and exit proc
        GoTo Exit_Proc
    Else
        'Bed B Shift - Hot Bunking - ? Decide here whether to update or not
        'If no hot bunking, show a message to the user and
        'exit the proc
        stMsg = "Your Message"
        MsgBox stMsg
        GoTo Exit_Proc
    End If

End If

'If you're here, there is something wrong with the data for this cabin

    If Bed_A_Avl_Status = vbNullString Or Bed_B_Avl_Status = vbNullString Then
    
        MsgBox "Verify whether the records for the cabin " & Me.txtCabinNo & " has any inconsistency"
    
    End If

Exit_Proc:

    On Error Resume Next
    
        Set rst = Nothing
        Set db = Nothing
        Exit Sub

Err_Handler:

        MsgBox Err.Number & " " & Err.Description
        
        Resume Exit_Proc

End Sub

 

[jdraw]

PLease show your tables and relationships as a jpg. Yuo may have some design options.

see for general reservation model
http://www.databaseanswers.org/data_models/holiday_homes/index.htm