Solved

List Only Those Not Already Selected

Posted on 2002-06-16
9
243 Views
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

Thanks
Neil
0
Comment
Question by:nmilmine
9 Comments
 
LVL 54

Expert Comment

by:nico5038
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 ?

Nic;o)
0
 
LVL 2

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
(SELECT DATEID FROM TBLBOOK)));

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.

Nomi.

 
0
 
LVL 4

Author Comment

by:nmilmine
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])));

Thanks
Neil
0
 
LVL 54

Accepted Solution

by:
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.
1)
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.
2)
Use:
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));

Nic;o)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Author Comment

by:nmilmine
ID: 7082690
Thanks Nic

Neil
0
 
LVL 4

Author Comment

by:nmilmine
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

Thanks
Neil
0
 

Expert Comment

by:Edouard
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.


Note:
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

IN SUMMARY:
STEP 1: CREATE ADDITIONAL QUERIES
      qryCurrentComputer
      qryUnselectedComputersUNIONCurrentComputer
STEP 2: ADJUST THE SUBFORM
      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)
STEP 3: ADJUST THE SUBFORM'S EVENT CODE


STEP BY STEP:

STEP 1: CREATE ADDITIONAL QUERIES
=================================

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;



STEP 2: ADJUST THE SUBFORM
==========================

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.


STEP 3: ADJUST THE SUBFORM'S EVENT CODE:
========================================
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"
    Else
        Me!cboComputerID.RowSource = "qryUnselectedComputersUNIONCurrentComputer"
    End If
    Me!cboComputerID.Requery
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()
    Me!cboComputerID.SetFocus
    Me!cboComputerID.Dropdown
End Sub
Private Sub cmdDropDownDate_Click()
    Me!cboDateID.SetFocus
    Me!cboDateID.Dropdown
End Sub
Private Sub cmdDropDownTime_Click()
    Me!cboTimeID.SetFocus
    Me!cboTimeID.Dropdown
End Sub
Private Sub txtComputerNo_GotFocus()
    Me!cboComputerID.SetFocus
End Sub
Private Sub txtDate_GotFocus()
    Me!cboDateID.SetFocus
End Sub
Private Sub txtTime_GotFocus()
    Me!cboTimeID.SetFocus
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.

THAT'S IT!

NOTE:
=====
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.SetFocus
            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)


OPTIONAL:
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)) '--
                  Me!cboTradeID.SetFocus
                  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!

Edouard
0
 
LVL 54

Expert Comment

by:nico5038
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 !

Nic;o)
0
 
LVL 4

Author Comment

by:nmilmine
ID: 7268554
Thanks Edouard

I will look at as time allows

Cheers
Neil
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now