locked
Open query to criteria with form button RRS feed

  • Question

  • Hello,

    Have both a form -- frmCompanies, and a query -- qryCompanies.

    Both contain a field CityID.

    Would like to click a form button, and have qryCompanies open to criteria filtered to whatever CityID is the form's active record.

    Ideally would also like the query to be synced with the form's CityID field, so it refreshes as I scroll the form's records.

    Any assistance would be greatly appreciated.

    Thank you.

    Tuesday, March 22, 2011 5:20 PM

Answers

  • you could put a subform in your main form linked by CityID, as you change records in your main form, the subform will follow.  Are you trying to see all the other companies that are in the same city as the company in your form? 

    Another way to do this is to have another form (in datasheet view) and on current in your company form you can set the filter of the other form to cityID and requery it.

    • Marked as answer by Bruce Song Monday, April 4, 2011 1:33 AM
    Tuesday, March 22, 2011 5:56 PM
  • To keep the two in sync you'll need to create a form in datasheet or continuous forms view, frmCompanies_Cont say, based on the query, and open the form filtered to the current CityID.  In the button's Click event put:

        Dim strCriteria As String
       
        strCriteria = "CityID = " & Me.CityID
        DoCmd.OpenForm "frmCompanies_Cont", WhereCondition:=strCriteria

    To keep the forms in sync put the following in the frmCompanies form's Current event procedure:

        Const FORM_NOT_OPEN = 2450

        On Error Resume Next
        Forms("frmCompanies_Cont").Filter = "CityID = " & Me.CityID
        Select Case Err.Number
            Case 0
            ' no error
            Case FORM_NOT_OPEN
            ' anticipated error, do nothing
            Case Else
            ' unknown error, inform user
            MsgBox Err.Descriptiion, vbExclamation, "Error"
        End Select

    There is no need to requery the second form.  As its FilterOn property will already be True by virtue of the WhereCondition argument of the OpenForm method, all that's necessary is to assign a new string expression to the Filter property.


    Ken Sheridan, Stafford, England
    • Marked as answer by Bruce Song Monday, April 4, 2011 1:33 AM
    Tuesday, March 22, 2011 11:25 PM
  • Assuming CityID is a numeric field put:
             Forms!frmCompanies!CityID
    in the criteria area under the CityID field in the query
    This will generate:
             WHERE ([CityId]=Forms!frmCompanies!CityID

    Then use:
             DoCmd.OpenQuery "qryCompanies"
    in the click event of the button conrtol.

    • Marked as answer by Bruce Song Monday, April 4, 2011 1:33 AM
    Wednesday, March 23, 2011 2:15 AM

All replies

  • you could put a subform in your main form linked by CityID, as you change records in your main form, the subform will follow.  Are you trying to see all the other companies that are in the same city as the company in your form? 

    Another way to do this is to have another form (in datasheet view) and on current in your company form you can set the filter of the other form to cityID and requery it.

    • Marked as answer by Bruce Song Monday, April 4, 2011 1:33 AM
    Tuesday, March 22, 2011 5:56 PM
  • To keep the two in sync you'll need to create a form in datasheet or continuous forms view, frmCompanies_Cont say, based on the query, and open the form filtered to the current CityID.  In the button's Click event put:

        Dim strCriteria As String
       
        strCriteria = "CityID = " & Me.CityID
        DoCmd.OpenForm "frmCompanies_Cont", WhereCondition:=strCriteria

    To keep the forms in sync put the following in the frmCompanies form's Current event procedure:

        Const FORM_NOT_OPEN = 2450

        On Error Resume Next
        Forms("frmCompanies_Cont").Filter = "CityID = " & Me.CityID
        Select Case Err.Number
            Case 0
            ' no error
            Case FORM_NOT_OPEN
            ' anticipated error, do nothing
            Case Else
            ' unknown error, inform user
            MsgBox Err.Descriptiion, vbExclamation, "Error"
        End Select

    There is no need to requery the second form.  As its FilterOn property will already be True by virtue of the WhereCondition argument of the OpenForm method, all that's necessary is to assign a new string expression to the Filter property.


    Ken Sheridan, Stafford, England
    • Marked as answer by Bruce Song Monday, April 4, 2011 1:33 AM
    Tuesday, March 22, 2011 11:25 PM
  • Assuming CityID is a numeric field put:
             Forms!frmCompanies!CityID
    in the criteria area under the CityID field in the query
    This will generate:
             WHERE ([CityId]=Forms!frmCompanies!CityID

    Then use:
             DoCmd.OpenQuery "qryCompanies"
    in the click event of the button conrtol.

    • Marked as answer by Bruce Song Monday, April 4, 2011 1:33 AM
    Wednesday, March 23, 2011 2:15 AM