Query Field Name based upon Text Box value

[Malcolm17]

Brilliant, thank you for that, I have something similar in my main database, but what you are showing is what I was thinking about where we manually select tables ( T1 + T2 + T3 ) on a continuous form, this does solve my problem, where I was trying to be a little more adventurous and be able to select one option to book say all tables in the room. (Initially thinking this might be possible with a crosstab query)

I’ll have a think and look again tomorrow if I have a chance.

Many thanks again,

Malcolm

 

[Gasman]

So you book a room, and not a table?

 

[mike60smart]

So you book a room, and not a table?

No
In the example Smith has booked Tables 7, 8 & 9
When you move to a new record for Jones - book required table(s)

 

[Malcolm17]

Mainly it would be to book an individual table up to 4 people, but often it would be to book 2 tables joined together (eg 7 & 8) for a party of say 8 people, rarely you’d want to select all tables in the room for a booking.

That’s the challenge here:
Table 7 can be used on its own
Table 8 can be used on its own
Tables 7 & 8 can be joined to make an 8
All Tables (inc 7 & 8) can be joined to make a table of 40.

The challenge is not showing a group if it has any of its individual tables booked, or not showing any of it’s individual tables if it’s booked as a group.

Malcolm

 

[Gasman]

Cannot believe you would not have the option of having private functions, but you know your business best.

 

[Malcolm17]

I’m trying to lose our restaurant booking system to save costs and build it in to our MS Access Diary system that logs staffing, maintenance, health and safety etc which we use and it emails management a report at the end of the day, also links to our tills data.

More than that I’m keeping myself occupied in my spare time and learning more. It’s been my wee project for the past 10 years on and off.

 

[mike60smart]

I’m trying to lose our restaurant booking system to save costs and build it in to our MS Access Diary system that logs staffing, maintenance, health and safety etc which we use and it emails management a report at the end of the day, also links to our tills data.

More than that I’m keeping myself occupied in my spare time and learning more. It’s been my wee project for the past 10 years on and off.

Hi Malcome
If you add an additional field in the tblBookings named Type with 2 values "Individual" or "Fully Booked" your Data Entry Form would be as shown in the attached 2 screenshots.

1 for Fully Booked and 1 for normal Individual Bookings.

On the Individual Bookings Form ignore the Covers of 40 these are errors.

 

[Ken Sheridan]

I would use an unbound form to make bookings. In the image below a a booking has previously been made for tables 5 and 6 from 18:00 to 18:59, and a further one hour booking for the same tables is being made for the same tables from 19:15 to 20:14. I need to amend the code to confirm the booking so that it handles single bookings for multiple tables, but when I've done that I'll post the file here. The basis for the list box in the form is an extension of the query I posted in post #15,to which parameters have been added referencing the date combo boxes and the tables multi-select list box on the form:

 

[Ken Sheridan]

Malcolm, I'm attaching the file from which the form I illustrated in my last post was taken. This enables a single booking for multiple tables to be made. You'll see that the file also includes the following query:

SELECT
    COUNT(*)
FROM
    Bookings AS B1
    INNER JOIN TableBookings AS TB1 ON B1.BookingID = TB1.BookingID,
    Bookings AS B2
    INNER JOIN TableBookings AS TB2 ON B2.BookingID = TB2.BookingID
WHERE
    B1.BookingID <> B2.BookingID
    AND TB1.TableNo = TB2.TableNo
    AND B1.TheDate = B2.TheDate
    AND B1.Arrive <= B2.Depart
    AND B1.Depart >= B2.Arrive;

This query will return zero if no tables have been double booked for the same time slot or slots, so it should be possible to use it as the basis for a CHECK CONSTRAINT on the TableBookings table, which will raise an error if an attempt is made to double book a table. I'll try it out, and if it does the trick I'll post the code for applying the constraint to the table. Access doesn't allow this to be done directly in the query designer, so it's necessary to execute the ALTER TABLE statement in VBA.

 

[Ken Sheridan]

I've amended my file to prevent double-bookings of one or more tables:

The following line of code was used to apply a CHECK CONSTRAINT to the TableBookings table:

CurrentProject.Connection.Execute "ALTER TABLE TableBookings ADD CONSTRAINT PreventDoubleBooking CHECK((SELECT COUNT(*) FROM Bookings AS B1 INNER JOIN TableBookings AS TB1 ON B1.BookingID = TB1.BookingID, Bookings AS B2 INNER JOIN TableBookings AS TB2 ON B2.BookingID = TB2.BookingID WHERE  B1.BookingID <> B2.BookingID AND TB1.TableNo = TB2.TableNo AND B1.TheDate = B2.TheDate AND B1.Arrive <= B2.Depart AND B1.Depart >= B2.Arrive)=0)"

The following line of code can be used to drop the PreventDoubleBooking CHECK CONSTRAINT from the TableBookings table:

CurrentProject.Connection.Execute "ALTER TABLE TableBookings DROP CONSTRAINT PreventDoubleBooking"

The cmdConfirmBooking button's Click event procedure was amended as follows:

Private Sub cmdConfirmBooking_Click()

    Const MESSAGE_TEXT = "The booking was unsuccessful due to attempted double-booking of one or more tables."
    Dim strSQL As String
    Dim strTableNo As String
    Dim strTheDate As String
    Dim strArrive As String
    Dim strDepart As String
    Dim lngBookingID As Long
    Dim varItem As Variant
  
    On Error GoTo Err_Handler
  
    '  get next BookingID value in sequence
    lngBookingID = Nz(DMax("BookingID", "Bookings"), 0) + 1
      
    With Me.lstTableAvailability
        If .ItemsSelected.Count > 0 Then
            For Each varItem In .ItemsSelected
                strTheDate = "#" & Format(.Column(1, varItem), "yyyy-mm-dd") & "#"
                ' only assign earliest time from selected set to strArrive variable
                If strArrive = "" Then
                    strArrive = "#" & Format(.Column(2, varItem), "hh:nn:ss") & "#"
                End If
                strDepart = "#" & Format(.Column(3, varItem), "hh:nn:ss") & "#"
            Next varItem
              
            ' build and execute SQL statement to insert new row into Bookings table
            strSQL = "INSERT INTO Bookings(BookingID,TheDate,Arrive,Depart) " & _
            "VALUES(" & lngBookingID & ", " & strTheDate & "," & strArrive & "," & strDepart & ")"
            CurrentDb.Execute strSQL
              
             For Each varItem In .ItemsSelected
                ' build and execute SQL statement to insert new row into TableBookings table
                strTableNo = """" & .ItemData(varItem) & """"
              
                strSQL = "INSERT INTO TableBookings(BookingID,TableNo) " & _
                "VALUES(" & lngBookingID & ", " & strTableNo & ")"
                CurrentDb.Execute strSQL
                ' if table or tables double-booked inform user and cancel booking
                If IsNull(DLookup("BookingID", "TableBookings", "BookingID = " & lngBookingID)) Then
                    MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"
                    strSQL = "DELETE * FROM Bookings WHERE BookingID = " & lngBookingID
                    CurrentDb.Execute strSQL
                    ' deselect all selected rows from listbox
                    cmdClearSelections_Click
                    Exit Sub
                End If
            Next varItem
        End If
    End With
  
    ' open bookings form in dialogue mode
    DoCmd.OpenForm "frmBookings", WhereCondition:="BookingID = " & lngBookingID, WindowMode:=acDialog
    ' call command button's Click event procedure to clear listbox selections
    cmdClearSelections_Click
    ' requery list box to show new booking
    Me.lstTableAvailability.Requery  
  
Exit_Here:
    Exit Sub
  
Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error"
End Sub