Results 1 to 2 of 2
  1. #1
    sullyman is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    7

    Find Records Query

    Hi folks,



    I have multiple combos working together correctly as follows:

    Year
    Location
    Course
    Student

    The combos bring back the correct records for that individual student in a subform when the last combobox (student) is selected using the following code in the afterupdate of the student combobox

    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.Find "[Studentid] = " & Str(Nz(Me![CboStudent], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    I'd like to break this down further so when i select

    1. Year: it will bring me back all records for that year,
    2. Location: it will bring back all records for locations that match the year selected in the previous combo
    3. Course: it will bring back all records for courses that match the year and location selected in the previous combos
    4. Student: it will bring back all records for that student that match the year, location, course selected in the previous combos (This is currently working with the code above)

    How can i include the code above to each previous combo afterupdate step to break down the records further.

    I'm thinking in my mind it would be similar to the following for the Course Combo but i cannot get it to work:

    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.Find "[Yearid] & [Locationid] & [Courseid] = " & Str(Nz(Me![CboYear],[CboLocation], [CboCourse], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    Any ideas please are appreciated

  2. #2
    sullyman is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    7
    Within my subform, i had the Link Master and Child properties set to

    LocationID, StudentID

    If i just use LocationID, i can now get all locations to show but then when i select Student, it doesn't refine further to show just that student.

    I thought i could have multiple entries within master, child properties to refine the data down?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to Add New Records based upon Query?
    By SteveAb in forum Database Design
    Replies: 0
    Last Post: 08-06-2009, 10:24 PM
  2. Find Missing Records
    By Flanders in forum Queries
    Replies: 6
    Last Post: 06-24-2009, 07:02 AM
  3. Query two different records in the same table
    By Eildydar in forum Access
    Replies: 5
    Last Post: 04-15-2009, 01:39 PM
  4. query to find the highest value in a field
    By jhjarvie in forum Queries
    Replies: 0
    Last Post: 09-02-2008, 02:27 PM
  5. query that will contain all unique records
    By halcolm1 in forum Queries
    Replies: 0
    Last Post: 01-19-2007, 05:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums