Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box from Outer Join (2003 )

    I am working on a scheduling/logistics program where we are keeping track of Unavailability rather than Availability. The customer would like a drop-down (combo box) in their form showing only available drivers. I just can't get it to work.

    My structure is as follows: I have a Driver table (tblDrivers) which contains driver information. This is linked by Driver ID to the Schedules table (tblSchedules) which contains the date the driver is unavailable. This, in turn, is linked to the Schedules Detail table (tblScheduleDetails) which contains the details of when the driver is unavailable that day. Right now we are only tracking unavailability in the morning and afternoon, but the intent is to broaden this to one-hour intervals - thus the reason for the detail table.

    I created a query showing all the unavailable drivers based on tblScheduleDetails (qryUnavailableDrivers). Next, I created a query bringing in tblSchedules (qryUnavailableSchedules). Lastly, I created a query based on tblDrivers to give me the Driver name(qryAvailable). This looks great when I view the query. This last query is an outer join, showing all drivers from tblDrivers and those from qryUnavailableSchedules who are unavailable. I want to take this last query and use it to drive my combo box so that only those who are available in the morning show up in the morning; those who are available in the afternoon show up in the afternoon. My combo box should show any drivers who are currently available.

    Somehow I think I'm making this too difficult. I tried a nested query as suggested in post 186109, but I get nothing returned. My SQL statement in the combo box reads

    SELECT qryDrivers.DriverID, qryDrivers.Driver FROM qryDrivers ORDER BY [Driver] WHERE NOT IN (SELECT qryUnavailableSchedules.DriverID FROM qryUnavailableSchedules);

    qryDrivers is based on tblDrivers but concatenates the driver first and last name. This statement returns NO data whereas combo boxes based on the individual queries seem to run fine.

    What am I doing wrong? This has been driving me nuts.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box from Outer Join (2003 )

    Your SQL is not valid as far as I can see (WHERE what? NOT IN ...), but apart from that, you haven't brought the date/time component into it. If you want further assistance, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box from Outer Join (2003 )

    You're right. Adding ...WHERE qryDriver.DriverID NOT IN... actually returned data. Can't believe I didn't see I had missed that. The date/time component is handled in the qryUnavailableDrivers query by adding the criteria from the form (the query contains the criteria Form!frmLoads!ScheduledLoadDate under the ScheduledLoadDate field). When the user enters the scheduled date, this is used to build the query. I'm working on stripping down my database so I can send a copy. I'm still having trouble updating the schedule based on the load form due to the complexity of my design. Thanks for getting me over the first hurdle.

  4. #4
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box from Outer Join (2003 )

    I have figured most of this out on my own, so I probably don't need to post my database. One problem is remaining, though - I need this combo box to update based on another field on the form. Right now, it's not working properly. The user enters a Scheduled Load Date at the top of the form. The combo boxes below will now need to update based on that date. I'd rather not put this field on another form. Can I force these other fields (Driver and Truck) to update using VBA? Both Driver and Truck fields are combo boxes tied to a query that is using the scheduled load date from the form as criteria. I tried me.refresh and me.requery after the date field has been updated, but they don't seem to have any effect. I'm fairly new at this Access stuff (I'm an old dBase programmer). Any suggestions would be welcome.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box from Outer Join (2003 )

    You can use the After Update event of the Scheduled Load Date text box:

    - Open the form in design view.
    - Click on the Scheduled Load Date text box to select it.
    - Activate the Event tab of the Properties window.
    - Click in the After Update event.
    - Select [Event Procedure] from the dropdown list.
    - Click the ... button to the right of the dropdown arrow.
    - Call the Requery methods of the Driver and Truck controls.
    - The code will look more or less like this:

    Private Sub Scheduled_Load_Date_AfterUpdate()
    Me.Driver.Requery
    Me.Truck.Requery
    End Sub

  6. #6
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box from Outer Join (2003 )

    Thanks for your quick reply! That worked perfectly for the Driver combo box, but I obviously have something wrong with my Truck combo box because I'm still not seeing data there. I'll play with it for awhile. I must be doing something stupid. Thanks again for your help.

  7. #7
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box from Outer Join (2003 )

    I give up! I think there are a few things wrong with this database. I've tried to strip it down as well as I could. I hope I haven't taken out too much, but I had to get it below 100K so I could attach it. Please see if you can figure out what I'm trying to do. The form in question is frmLoads. Thanks.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box from Outer Join (2003 )

    The Row Source of the cboTruck combo box is not valid: the ORDER BY part should be at the end instead of in front of the WHERE part. Try this as row source:

    SELECT TruckID, Description FROM qryTrucks WHERE TruckID NOT IN (SELECT TruckID FROM qryUnavailableTrucks) ORDER BY Description;

  9. #9
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box from Outer Join (2003 )

    This works perfectly. Thanks! NOW, when I try to update the detail files, I'm running into problems. The Drivers table is linked to a Schedules table (by DriverID) which contains information on the day a Driver is unavailable. That, in turn, is linked to a ScheduleDetails table (by ScheduleID) which contains details about that day. Right now it contains a check box field labelled "morning" and "afternoon". We only need to know if the Driver is available in the morning or afternoon. Eventually, we'll expand this to hourly increments. I use a For..Next loop to update the Schedule table, and it works perfectly. How do I update the ScheduleDetails table with the appropriate ScheduleID? The code I'm using right now updates the ScheduleID in the ScheduleDetails table as "1" for everyrecord:

    For intCounter = 1 To intDateDiff
    rstSchedules.AddNew
    rstSchedules!DriverID = Me!DriverID
    rstSchedules!ScheduleDate = ScheduledLoadDate - 1 + intCounter
    rstSchedules.Update
    rstScheduleDetails.AddNew
    rstScheduleDetails!ScheduleID = rstSchedules!ScheduleID
    rstScheduleDetails!Morning = True
    rstScheduleDetails!Afternoon = True
    rstScheduleDetails.Update
    Next intCounter

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box from Outer Join (2003 )

    Try this:

    Dim lngScheduleID As Long

    For intCounter = 1 To intDateDiff
    rstSchedules.AddNew
    rstSchedules!DriverID = Me!DriverID
    rstSchedules!ScheduleDate = ScheduledLoadDate - 1 + intCounter
    lngScheduleID = rstSchedules!ScheduleID
    rstSchedules.Update
    rstScheduleDetails.AddNew
    rstScheduleDetails!ScheduleID = lngScheduleID
    rstScheduleDetails!Morning = True
    rstScheduleDetails!Afternoon = True
    rstScheduleDetails.Update
    Next intCounter

    Note: as it is now, you don't really need tblScheduleDetails (the Morning and Afternoon fields could be in tblSchedules). When you start using hourly increments, you should create a separate record in tblScheduleDetails for each hour.

  11. #11
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box from Outer Join (2003 )

    That's perfect. I must admit, you have me seriously considering eliminating that detail table. It would make maintenance easier...

    Thanks for all your help!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box from Outer Join (2003 )

    For the moment, it would be easier. But later on, you shouldn't create 24 fields in tblSchedules (one for each hour). Checking when a driver is available would be difficult.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •