Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    23

    Unanswered: Query not displaying record using active forms ID number

    Hi all,

    I'm having a few issues with a query not displaying the record shown on an active form.

    I've done this type of query before without any issues but this time round it doesn't seem to want to work.

    SQL reads

    SELECT tblHolidayBooking.HolidayID, tblHolidayBooking.StaffID, tblStaff.FirstName, tblStaff.SecondName, tblHolidayBooking.HolidayType
    FROM tblStaff LEFT JOIN tblHolidayBooking ON tblStaff.StaffID=tblHolidayBooking.StaffID
    WHERE (((tblHolidayBooking.HolidayID)=[Forms]![fHolidayBookingForm]![HolidayID]));

    I've made sure the current record has been saved but still no joy? Any ideas appreciated?

    Thanks

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    First off, using a field from the table on the right of a LEFT JOIN statement in the WHERE clause renders the left join pointless.

    What does the query return?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Apr 2011
    Posts
    23
    The query returns a blank record and HolidayID field contains (AutoNumber)

    the query works if the required form isn't open and I manually enter the the HolidayID

  4. #4
    Join Date
    Apr 2011
    Posts
    23
    Rebuilt from scratch and seems to be working now, I think I may have created a relationship between two tables that wasn't required quite early on in the original design but I could be wrong.

Posting Permissions

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