Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Reference Parent Form in Query? (A2k (9.0.3821) SR-1)

    How to Reference Parent Form in Query?

    A2k (9.0.3821) SR-1

    I have a parent form and a sub form.

    In the subform I have query with the following criteria to populate a combo box

    [Forms]![frm_TOC_BP_ACD]![SubConSubForm].[Form]![lngAccountTypeID]

    The above works.

    My question.

    Is there a way to code the above without hard coding the parent form name frm_TOC_BP_ACD in the query criteria?

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Reference Parent Form in Query? (A2k (9.0.3821) SR-1)

    Are you talking about a saved query? If so, then no, there is no other way. If you're creating the SQL for the query on the fly, you can simply substitute the actual value on the parent form in the SQL.
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: How to Reference Parent Form in Query? (A2k (9.0.3821) SR-1)

    If the query is a named query, then you have to use the full delineation (that is: Forms!........). However, if the combo box's rowsource is just an SQL statement, then you might be able to get by with just using the control name [lngAccountTypeID].
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Reference Parent Form in Query? (A2k (9.0.3821) SR-1)

    Hi Charlotte & Mark

    Trying your suggestion, using SQL only in Open Event to avoid hard coding from name in Query.

    Operator enters Account Type combo lngAccountTypeID, Account Type should isolate just Contractor for that Account Type in lngSubConCodeID combo.

    The combo works without the Where statement, however it returns all records.

    Need the syntax of the Where statement below to restrict contractor combo by account type

    How do I reference lngAccountTypeID in the SQL?

    Private Sub Form_Current()

    Dim strSQL As String

    strSQL = "SELECT tblContractor.lngContractorID, tblContractor.strAccountNo, "
    strSQL = strSQL & "tblContractor.lngAccountTypeID, tbl_BP_OtherLookup.strCode, "
    strSQL = strSQL & "tblContractor.strBusinessName, tblContractor.strBusinessContact, "
    strSQL = strSQL & "tblContractor.strLicenseNo, tblContractor.strBusinessAddr1, "
    strSQL = strSQL & "tblContractor.strBusinessWorkPhone, tblContractor.strInsuranceCo, "
    strSQL = strSQL & "tblContractor.dtmInsExpDate, tblContractor.dtmLicenseDate, "
    strSQL = strSQL & "tblContractor.strPermitNo, tblContractor.dtmPermitDate "

    strSQL = strSQL & "FROM tblContractor INNER JOIN tbl_BP_OtherLookup ON "
    strSQL = strSQL & "tblContractor.lngAccountTypeID = tbl_BP_OtherLookup.lngID "

    strSQL = strSQL & "WHERE ((tblContractor.lngAccountTypeID) = [lngAccountTypeID]) "

    strSQL = strSQL & "ORDER BY tbl_BP_OtherLookup.strCode, tblContractor.strBusinessName;"

    Debug.Print "strSQL: " & strSQL
    Me!lngSubConCodeID.RowSource = strSQL
    Me!lngSubConCodeID.Requery

    End Sub

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Reference Parent Form in Query? (A2k (9.0.3821) SR-1)

    If lngAccountTypeID is a textbox on your form then you have to put it out of the quotes:
    strSQL = strSQL & "WHERE ((tblContractor.lngAccountTypeID) = " & Me!lngAccountTypeID
    and insert a space in the next line:
    strSQL = strSQL & " ORDER BY tbl_BP_OtherLookup.strCode, tblContractor.strBusinessName;"
    Francois

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Reference Parent Form in Query? (A2k (9.0.3821) SR-1)

    Hi Francois

    Your code fixed it.

    strSQL = strSQL & "WHERE tblContractor.lngAccountTypeID = " & Me!lngAccountTypeID

    Thanks for your help

    John

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: How to Reference Parent Form in Query? (A2k (9.0.3821) SR-1)

    >>Trying your suggestion, using SQL only in Open Event to avoid hard coding from name in Query.<<

    The problem that can be encountered with code in the Open event that references a control on the form is that the OPen even executes before the control has data in it! Sometimes it works better in the Load event; and will always work in the Current event (if appropriate).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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