Results 1 to 1 of 1
  1. #1
    nilanjangm is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Location
    Kolkata, India
    Posts
    1

    Question Populating subform with query that take user input

    I have two tables. "Student" and "Class" with a PK-FK relationship.

    I have a main form "Student Record" whose "Record Source" is set to "Class". I have a subform "Student subform" in the main form whose "Record Source" is set to "Student".
    The subform is linked to the main form using "Class_Student ID" as the "Link Master Fields" and "Student_ID" as the "Link Child Fields".

    I also have a combo box "cmbClassTitle" in the main form header whose "Row Source" is set to
    Code:
    SELECT Class.[Class ID], Class.[Class Title] FROM Class ORDER BY Class.[Class ID];
    Now, I want to display all "Class Title" values in the combo box (which it is showing). Upon selecting a value, I want to display the enrolled student's details in the subform. This part is not working.

    I have written two different sets of code for that.

    Set 1
    Code:
    strSQL = "SELECT Student.[Student Name], Student.[Student DOB], Student.[Student Address]" & _
            " FROM Student RIGHT OUTER JOIN Class" & _
            " ON Student.Student_ID = Class.Class_Student_ID" & _
            " WHERE Class.Class_Title = '" & Me.cmbClassTitle & "';"
            [Form_Student subform].Form.RecordSource = strSQL
    Running the form shows up the input box asking "Class_Title", "Student_ID" and "Class_Student_ID".

    Set 2
    Code:
    Set dbase = CurrentDb()
        Set qdef = dbase.QueryDefs("Enrolled Student of Class")
        qdef.Parameters("Class Title") = cmbClassTitle
        Set rs = qdef.OpenRecordset()
    
        If Not rs.BOF And Not rs.EOF And rs.Fields.Count > 0 Then
            Set [Form_Student Record].Recordset = rs
        Else
            MsgBox "No corresponding record for " & cmbClassTitle & "!", vbCritical
            Exit Sub
        End If
    Running the form in this case produces error.


    Run-time error '3265':

    Item not found in this collection.
    The error is on line
    Code:
    qdef.Parameters("Class Title") = cmbClassTitle
    The SQL query code for "Enrolled Student of Class" is
    Code:
    SELECT Student.[Student Name], Student.[Student DOB], Student.[Student Address]
    FROM Student RIGHT JOIN Class ON Student.Student_ID=Class.Class_Student_ID
    WHERE Class.Class_Title=[Class Title];
    I'm clueless, hopelessly.
    Last edited by nilanjangm; 03-23-2010 at 12:09 AM. Reason: Done some R&D :)

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

Similar Threads

  1. Replies: 3
    Last Post: 08-25-2010, 09:03 AM
  2. User Input Query
    By ManC in forum Queries
    Replies: 2
    Last Post: 03-04-2010, 07:09 PM
  3. Chart with user input
    By mungyun in forum Access
    Replies: 1
    Last Post: 12-28-2009, 07:44 PM
  4. Replies: 0
    Last Post: 01-18-2007, 07:07 PM
  5. Set required user input
    By ZeusOsiris in forum Database Design
    Replies: 2
    Last Post: 12-04-2006, 07:13 PM

Tags for this Thread

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