List Only Those Not Already Selected

Posted on 2002-06-16
Last Modified: 2006-11-17
Hi Everyone

I am creating a booking system

I have the following Tables - Fields

Available Dates - Available Dates ID, Available Dates
Available Time - Available Times ID, Available Time
Computers - Computer ID, Computers No
Bookings - Available Dates ID, Available Times ID, Computer ID, Student ID

I have a Main/Subform.  The Main form is based on a Student table, the subform based on the Bookings table

I have combo boxes for the Dates, Times and Computers

Where I am stuck is when a student selectes a Date, then a Time, Then a Computer,  I want the combo for the Computer to only show the computers that have not already been allocated to a student already at the same date and time.

I guess I am looking for a code which will be based on a query that eliminates and Dates/Times the same as those selected by the student.  This would run on the GotFocus Event for the Computer combo

Question by:nmilmine
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 54

Expert Comment

ID: 7081216
Hi Neil,

For this you need a LEFT JOIN query between Computer and Bookings.
Just place both in the editor and connect them by computerID. Then doubleclick the connectionline and make computer "leading". Next add the Bookings - Available Dates ID and add the criteria "Is Null"

Getting the idea ?


Expert Comment

by:Syed Irtaza Ali
ID: 7082041
tblDate  Available Dates
tblTime  Available Time
tblComp  Computers
tblBook  Bookings

First run this query to get available dates

cboDate.Rowsource =

SELECT tblDate.DateID, tblDate.DateNo
FROM tblDate
WHERE (((tblDate.DateID) Not In

Now for the time use this query when the cboDate combo is updated.

cboTime.RowSource =

SELECT tblTime.TimeID, tblTime.Time
FROM tblTime
WHERE (((tblTime.TimeID) Not In
(Select TimeID from tblBook
Where DateID=Forms!form1!subform!cbodate)));

This way u wont get the times allocated for that day.

Now when the cboTime is updated use this query for the computers

cboComp.RowSource =

SELECT tblComp.CompId, tblComp.CompNo
FROM tblComp
WHERE (((tblComp.CompId) Not In (Select CompID from tblBook Where DateID=Forms!form1!subform!cboDate AND TimeID=Forms!form1!subform!cboTime)));

There u got ur computer now.

Happy. Good.



Author Comment

ID: 7082614
Hi Nic

Tried adding as suggested.  It caused the existing computer fields not to display the result

Code is:-
SELECT Computers.[Computer ID], Computers.[Computer No], Bookings.[Available Date ID]
FROM Computers LEFT JOIN Bookings ON Computers.[Computer ID] = Bookings.[Computer ID]
WHERE (((Bookings.[Available Date ID]) Is Null));

Also it isn't referring to the time as well as the date

Hi Nomehero

I have put the following code in to the computer combo box but it isn't recognising the Date Available ID or the Available Times ID but both of these fields are in the Bookings table

SELECT Computers.[Computer ID], Computers.[Computer No]
FROM Computers
WHERE (((Computers.[Computer ID]) Not In (Select [Computer ID] From [Bookings] Where [Date Available ID]=[Forms]![Bookings]![Bookings].[Form]![Available Date] AND [Available Times ID]=[Forms]![Bookings]![Bookings].[Form]![Available Time])));

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 54

Accepted Solution

nico5038 earned 100 total points
ID: 7082631
Hi Neil,

The JOIN is OK, now we need to "replace" the table Bookings with a query filtering the bookings for the date and time mentioned on the form.
Just open a query for bookings and use the right-click pop-up menu to do a Build of the date and time field of your form. Save this as qryBookingDateTime.
SELECT Computers.[Computer ID], Computers.[Computer No], qryBookingDateTime.[Available Date ID]
FROM Computers LEFT JOIN qryBookingDateTimeON Computers.[Computer ID] = qryBookingDateTime.[Computer ID]
WHERE (((qryBookingDateTime.[Available Date ID]) Is Null));


Author Comment

ID: 7082690
Thanks Nic


Author Comment

ID: 7102194
Hi Nic

I am having a problem with this as I find that I need to use the OnGotFocus Requery option to get the list to show correctly

This has the effect of making all the other entries disappear from view.  They are still retained in the table and when you out and back in again they are all showing as correct.  It is only when you enter a new entry for a student that it is a problem.  Any thoughts


Expert Comment

ID: 7260582
Hi Neil,

Your question has been answered by Nico a while ago but you were still stuck with a problem at the end, which is that the OnGotFocus requery event makes all the other entries disappear from view until you get out of the combo box again (OnLostFocus). (In fact, if you enter that combo box in an existing record it also makes the currently selected Computer name disappear from view.)

Maybe you have a solution by now, but in case you don't here is one. It seems like a lot of stuff but that's only because I explain it detailed step by step. Anyway, the result is worth it.

I have changed the field names a little to allow others to follow easier and because it follows better design practices. (no fields with same name as tables etc.) Just paste the whole thing in a word processor, do a Find>Replace to enter your values. (Use "Find whole words only", add square brackets and be careful with the duplicate names.)

      Your tables with their fields:
            Available Dates: Available Dates ID, Available Dates
            Available Time: Available Times ID, Available Time
            Computers: Computer ID, Computers No
            Bookings: Available Dates ID, Available Times ID, Computer ID, Student ID

      My example:
            tblAvailableDates: DateID, Date
            tblAvailableTimes: TimeID, Time
            tblComputers: ComputerID, ComputerNo
            tblBookings: DateID, TimeID, ComputerID, StudentID

      Change its Default View to Continuous Forms.
      Adjust its Record Source.
      Adjust the Detail section (Add 3 text boxes and 3 transparent command buttons).
      Adjust the Form Header section (3 labels functioning as column headings)



So far you have:
1. qryBookingDateTime as mentioned by Nico.
2. The LEFT JOIN query based on qryBookingDateTime and table Computers as mentioned by Nico. In my example I call it qryUnselectedComputers.

(note: my field names vary slightly from yours in that they don't allow any spaces)

SQL for qryUnselectedComputers:
SELECT tblComputers.ComputerID, tblComputers.ComputerNo
FROM tblComputers LEFT JOIN qryBookingDateTime ON tblComputers.ComputerID = qryBookingDateTime.ComputerID
WHERE (((qryBookingDateTime.DateID) Is Null))
ORDER BY tblComputers.ComputerNo;

You need also:
3. qryCurrentComputer
4. qryUnselectedComputersUNIONCurrentComputer (This query is used as rowsource for combo box cboComputerID when NOT on a NEW booking record, so the list will also show the currently selected computer. qryUnselectedComputers is used when ON a new booking record.)

SQL for qryCurrentComputer:
SELECT DISTINCT tblBookings.ComputerID, tblComputers.ComputerNo
FROM tblComputers INNER JOIN tblBookings ON tblComputers.ComputerID=tblBookings.ComputerID
WHERE (((tblBookings.ComputerID)=[Forms]![frmStudents]![fsubBookings].[Form]![cboComputerID]));

SQL for qryUnselectedComputersUNIONCurrentComputer:
SELECT qryUnselectedComputers.ComputerID, qryUnselectedComputers.ComputerNo
FROM qryUnselectedComputers
UNION SELECT qryCurrentComputer.ComputerID, qryCurrentComputer.ComputerNo
FROM qryCurrentComputer
ORDER BY qryUnselectedComputers.ComputerNo;


I would think your subform shows as a Datasheet. Change its Default View to Continuous Forms. Now we'll set it up so it will look and behave very much as a datasheet subform.

1st. Adjust its Record Source:
You had it set to tblBookings (or a query based on it).
Click the <Build...> button and add tblAvailableDates, tblAvailableTimes and tblComputer. Now just drag the fields Date, Time and ComputerNo to the grid.
You will get something like the following:
SELECT tblBookings.DateID, tblBookings.TimeID, tblBookings.ComputerID, tblBookings.StudentID, tblComputers.ComputerNo, tblAvailableDates.Date, tblAvailableTimes.Time
FROM tblComputers INNER JOIN (tblAvailableTimes INNER JOIN (tblAvailableDates INNER JOIN tblBookings ON tblAvailableDates.DateID = tblBookings.DateID) ON tblAvailableTimes.TimeID = tblBookings.TimeID) ON tblComputers.ComputerID = tblBookings.ComputerID;

2nd. Adjust the Detail section (We'll add 3 text boxes and 3 command buttons):
So far you had already combo boxes for Date, Time and Computer. Let's call them cboDateID, cboTimeID, cboComputerID. In Continuous Forms view the arrows on the right always show. That's UGLY.
Covering the combo boxes with text boxes will hide these arrows AND will take care of your initial problem (the entries disappearing after the OnGotFocus requery event.) Adding small transparent command buttons towards the right of each text box will take care of the dropdown effect.

Go as follows:
- Align and size the three combo boxes so they all touch each other (like in datasheet view). Set their Top value to 0.
- Set the form's Detail Height value to the same as the combos' height.
- On top of each combo box draw a text box of exactly the same size. Let's name them txtDate, txtTime and txtComputerNo.
(TIP: don't drag or Ctrl-Arrow or Shift-Arrow to size and place your controls at this point. Instead, copy the values from the underlying combo box and paste them into the values for the text box.)
- Select all three text boxes and click Format -> Bring to Front.
- Set their Control Source to Date, Time and ComputerNo respectively (possible because of the subforms new Record Source).
- Set the following properties for combos and textboxes:
      Back Style: Normal
      Back Color: white
      Border Style: Solid
      Border Color: dark gray
      Border Width: Hairline
      Tab Stop: YES for combobox, NO for textbox
      Tab order: txtDate, cboDateID, txtTime, cboTimeID, txtComputerNo, cboComputerID
- Also make sure each text box has the same font style and font size as the underlying combo box.
- Draw a small command button on top of the first text box.
- Set its properties:
      Transparent: Yes
      Top: 0
      Width: enough to cover an arrow of a combo box
      Height: same as combo and text box
      Tab Stop: No
      Right-align it with the underlying text box
- Add two more such command buttons by copying the first one and pasting it into the forms' Detail section. (Right-click the detail bar + paste). Position them towards the right of the two other text boxes.
- Select all three command buttons and click Format -> Bring to Front.
- Let's name them cmdDropDownDate, cmdDropDownTime and cmdDropDownComputer.

3rd. Adjust the subform's Form Header section:
If you don't have them yet, add three labels (one above each text box) with the captions "Available Dates", "Available Times" and "Computer No".
- Set their properties:
      Top: 0
      Width: as the textbox below
      *Special Effect: Etched
      Font Weight: Bold
      Text Align: Center
 * Simulates the look of the column headings in a datasheet. To get even better results, remove the borders of the labels (set to Flat + Transparent) and replace them with a combination of etched and solid black lines.

Paste the following code anywhere in the subform's module in one time:

'*****FROM HERE*****
Private Sub ArrowKeys(intKeyCode As Integer)
'-- This procedure is called from the comboboxes' OnKeyDown events.
'-- It allows the user to move around in the subform by using the
'-- arrow keys like in Datasheet view.
        Select Case intKeyCode
            Case 38 '-- Arrow Up key
                If Me.CurrentRecord > 1 Then
                    DoCmd.GoToRecord , , acPrevious
                End If
            Case 40 '-- Arrow Down key
                If Me.NewRecord = False Then
                    DoCmd.GoToRecord , , acNext
                End If
        End Select
End Sub
Private Sub cboComputerID_GotFocus()
    If Me.NewRecord = True Then
        Me!cboComputerID.RowSource = "qryUnselectedComputers"
        Me!cboComputerID.RowSource = "qryUnselectedComputersUNIONCurrentComputer"
    End If
End Sub
Private Sub cboComputerID_KeyDown(KeyCode As Integer, Shift As Integer)
    ArrowKeys (KeyCode)
End Sub
Private Sub cboDateID_KeyDown(KeyCode As Integer, Shift As Integer)
    ArrowKeys (KeyCode)
End Sub
Private Sub cboTimeID_KeyDown(KeyCode As Integer, Shift As Integer)
    ArrowKeys (KeyCode)
End Sub
Private Sub cmdDropDownComputer_Click()
End Sub
Private Sub cmdDropDownDate_Click()
End Sub
Private Sub cmdDropDownTime_Click()
End Sub
Private Sub txtComputerNo_GotFocus()
End Sub
Private Sub txtDate_GotFocus()
End Sub
Private Sub txtTime_GotFocus()
End Sub
'*****TILL HERE*****

You DON'T need an OnLostFocus event for cboComputerID since txtComputerNo will always show on top of the combo box.

Verify the form's properties to see if the controls all have the phrase [Event Procedure] next to their respective events.


There are still some differences between the resulting "datasheet lookalike" subform and a real Datasheet subform:

1. A real Datasheet allows you to select one or more columns or rows by clicking them.

2. The text in a real Datasheet has automatically a nice left margin of about 25 Twips (0.046cm or 0.011 inch) whether contained in a text box or a combo box. In form view you can only set the Left Margin of a text box, not of a combo box. To get the same effect for the combo box in our example, you would have to set its border to transparent, move it slightly to the right and reduce its width accordingly. (Also reduce its height a little and lower it a bit to avoid making the textbox border disappear with the focus event.) I tried it and aligning the text of the textbox with the text of the combobox below it seems a question of luck, not worth the effort.

3. When clicking a word inside a combo box of a real Datasheet subform, the cursor will be placed at the position where you clicked. When clicking a word inside the text box of our Continuous Forms subform, the OnGotFocus event moves the focus to the underlying combo box, selecting the whole word below. I actually like that but if you don't then by using the textbox SelStart property you could imitate the placement of the insertion point through the OnClick event. Use:
      Private Sub txtDate_Click()
            intI = Me!txtDate.SelStart
            Me!cboDateID.SelStart = intI
      End Sub
When using this then additional steps should be taken to disallow right-clicking because accidentally pasting anything inside the textbox would change the underlying data. Allowing the user to access the textbox further then the OnGotFocus event is risky (see below)

If the subform shows a lot of its records at the same time, the requering combo boxes tend to flicker a little when entered. This could be a nuisance if the user is expected to access or change existing records (compared to just entering new records as your example above).

I have an application that needed a solution for that:
In short it involves removing the onGotFocus event fom the text box above the combo box and replacing it with an OnKeyDown event like this:

      Private Sub txtTradeNm_KeyDown(KeyCode As Integer, Shift As Integer)
            Dim strText As String
            Select Case KeyCode
                  Case 48 To 57, 65 To 90, 97 To 105  '-- user pressed 0 to 9 or a to z
                  strText = LCase(Chr(KeyCode)) '--
                  SendKeys strText '-- Paste the character in the combo box
            Case 9  '-- User pressed TAB key, allow to execute tab
                  '-- Do nothing
            Case Else '-- User pressed other keys, inappropriate
                  KeyCode = Empty '-- Remove the contents from KeyCode
            End Select
      End Sub

Now when the user tries to type something inside the textbox that character will be moved to the combobox and the contents of the textbox are not affected. If he just tabs passed the field nothing is requeried and no flickering occurs.
You also need to reduce the width of the textbox and replace the command button with a solid color (white) rectangle of the same size with the same OnClick event that puts the focus to the combo box. The only drawback is that no dropdown arrow shows when entering the textbox. An acceptable solution: don't remove the command button but add a same size solid color (white) textbox underneath. Set its font to Webdings3 and its source to "Arrow". (The character "" shows a small down arrow in Webdings3.) Create a new field "Arrow" (datatype=text) in the base table. Now add code that takes care of changing the Arrow field from Null to "" when entering a record. The result is very much like having a down arrow next to your textbox when needed, except it doesn't have the raised "button look".

Since this is an older question I will add a more generic example as a zero point Access "question" because I believe it could be useful to many people.

Suggestions or improvements always welcome!

LVL 54

Expert Comment

ID: 7260894
Hi Neil,

Must have missed your last comment indeed.
If still a problem and Edouard's comment-story doesn't help, then drop it (with a reference to this Q in my nico5038 mailbox "at" yahoo and I'll have a look.

Thanks for reminding me Edouard !


Author Comment

ID: 7268554
Thanks Edouard

I will look at as time allows


Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question