locked
DoCmd.OpenForm with subform criteria RRS feed

  • Question

  • Can anyone give me a hand writing this DoCmd.open form statement?

    I have a textbox and I would like to click a button and open the main form based on criteria of the subform. So I need to write a two part where statement, but the subform part of it is making me stumble.

    DoCmd.OpenForm "Flight Form View/Edit", , , "[FlightID] =" & [Forms]![Patient].[FlightID] & "AND" & [Forms]![Patient].[HCP] & " =" & Me.HealthCare

    The above is what I have so far, but it is not recognizing the patient form. The Patient form is a subform on "Flight Form View/Edit"

    I would like the criteria to be that FlightID of "Flight Form View/Edit" = FlightID of patient form, and the entry on the search form =the health care number on the patient form.

    If anyone can help me out I would really appreciate it!

    Katie

    Wednesday, June 26, 2013 10:30 PM

Answers

  • you need to identify the subform as a form and as a child of its parent.

    forms!flight!patient.form.flightID


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    • Marked as answer by Dummy yoyo Monday, July 8, 2013 8:22 AM
    Wednesday, June 26, 2013 11:38 PM
  • It's not quite so straightforward, I'm afraid.  A subform will normally implement the 'many' side of a one-to-many relationship type, of which the parent form represents the 'one' side, so the HCP value on which you are trying to restrict the records returned in the parent form could be one in a set of rows.  You need to restrict the rows returned on the basis of any of the referenced rows having the value, which you can do by means of the IN operator against a subquery.

    You haven't really given enough information to be able to give you the expression you'd need to use, but if you take a look at the file StudentLog.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    you'll see that the students form includes a button to filer the form by course (for which the form includes a subform).  This opens the dialogue form frmCourseDlg whose module includes the following code:

        Const MESSAGETEXT = "No courses selected."
        Dim strCourseList As String
        Dim varItem As Variant
        
        With Me.lstCourses
            If .ItemsSelected.Count > 0 Then
                For Each varItem In .ItemsSelected
                    strCourseList = strCourseList & "," & .ItemData(varItem)
                Next varItem
                strCourseList = Mid(strCourseList, 2)
                Forms("frmStudents").Filter = "StudentID In" & _
                    "(Select StudentID From CourseRegistrations " & _
                    "WHERE CourseID In(" & strCourseList & "))"
                Forms("frmStudents").FilterOn = True
            Else
                MsgBox MESSAGETEXT, vbExclamation, "Warning"
            End If
        End With

    This is a little more complex than you will require as it uses a multi-select list box to select multiple courses. but the important line is:

                Forms("frmStudents").Filter = "StudentID In" & _
                    "(Select StudentID From CourseRegistrations " & _
                    "WHERE CourseID In(" & strCourseList & "))"

    If this was filtering by only one CourseID value, as in your case with the HCP value, it would be something like this:

                Forms("frmStudents").Filter = "StudentID In" & _
                    "(Select StudentID From CourseRegistrations " & _
                    "WHERE CourseID = " & Me.cboCourse & ")"

    where cboCourse is a combo box to select a single course.

    This filters an open form by assigning the expression to the form's Filter property, but the expression for the WhereCondition argument of the OpenForm method would be just the same.  In your case it would be combined with the filtering on FlightID of course.

    Ken Sheridan, Stafford, England

    • Marked as answer by Dummy yoyo Monday, July 8, 2013 8:21 AM
    Thursday, June 27, 2013 12:19 AM

All replies

  • you need to identify the subform as a form and as a child of its parent.

    forms!flight!patient.form.flightID


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    • Marked as answer by Dummy yoyo Monday, July 8, 2013 8:22 AM
    Wednesday, June 26, 2013 11:38 PM
  • It's not quite so straightforward, I'm afraid.  A subform will normally implement the 'many' side of a one-to-many relationship type, of which the parent form represents the 'one' side, so the HCP value on which you are trying to restrict the records returned in the parent form could be one in a set of rows.  You need to restrict the rows returned on the basis of any of the referenced rows having the value, which you can do by means of the IN operator against a subquery.

    You haven't really given enough information to be able to give you the expression you'd need to use, but if you take a look at the file StudentLog.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    you'll see that the students form includes a button to filer the form by course (for which the form includes a subform).  This opens the dialogue form frmCourseDlg whose module includes the following code:

        Const MESSAGETEXT = "No courses selected."
        Dim strCourseList As String
        Dim varItem As Variant
        
        With Me.lstCourses
            If .ItemsSelected.Count > 0 Then
                For Each varItem In .ItemsSelected
                    strCourseList = strCourseList & "," & .ItemData(varItem)
                Next varItem
                strCourseList = Mid(strCourseList, 2)
                Forms("frmStudents").Filter = "StudentID In" & _
                    "(Select StudentID From CourseRegistrations " & _
                    "WHERE CourseID In(" & strCourseList & "))"
                Forms("frmStudents").FilterOn = True
            Else
                MsgBox MESSAGETEXT, vbExclamation, "Warning"
            End If
        End With

    This is a little more complex than you will require as it uses a multi-select list box to select multiple courses. but the important line is:

                Forms("frmStudents").Filter = "StudentID In" & _
                    "(Select StudentID From CourseRegistrations " & _
                    "WHERE CourseID In(" & strCourseList & "))"

    If this was filtering by only one CourseID value, as in your case with the HCP value, it would be something like this:

                Forms("frmStudents").Filter = "StudentID In" & _
                    "(Select StudentID From CourseRegistrations " & _
                    "WHERE CourseID = " & Me.cboCourse & ")"

    where cboCourse is a combo box to select a single course.

    This filters an open form by assigning the expression to the form's Filter property, but the expression for the WhereCondition argument of the OpenForm method would be just the same.  In your case it would be combined with the filtering on FlightID of course.

    Ken Sheridan, Stafford, England

    • Marked as answer by Dummy yoyo Monday, July 8, 2013 8:21 AM
    Thursday, June 27, 2013 12:19 AM
  • THIS^^^

    Thanks suzyQ for the concise answer

    Tuesday, September 22, 2015 3:45 PM