none
Open Query and Set Parameters via VBA and Unbound form RRS feed

  • Question

  • I have an unbound form which I would like to use to run and set query parameters using VBA. A user can Select CustomerID, CustState, AccntRep and StartDate, EndDate  parameters using unbound text and combo boxes. However the query must run properly whether the user specifies All or just one of the parameters. How can this be achieved with a on-click event of a command button?

    Thanks,

    js

    Wednesday, December 9, 2015 4:38 PM

Answers

  • I have an unbound form which I would like to use to run and set query parameters using VBA. A user can Select CustomerID, CustState, AccntRep and StartDate, EndDate  parameters using unbound text and combo boxes. However the query must run properly whether the user specifies All or just one of the parameters. How can this be achieved with a on-click event of a command button?

    Hi js,

    Construct the WHERE  part of the SQL-string only on the the parameters that have a value.

    So if no value is given for CustomerID, CustState, AccntRep, StartDate, EndDate, then no WHERE part is constructed, and all records are retrieved.

    Imb.

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:38 PM
    Wednesday, December 9, 2015 8:28 PM
  • Any suggestions?

    js

    Hi js. I thought I gave you plenty.

    One thing to consider with using parameters, you have to worry about the problem you're trying to fix now. If you try my suggestion of not using parameters, then there's no problem if the user select all criteria or just a few or none at all. Again, if you have any questions on how to implement something like that, after you have played around with the demos in the page I linked above, then just let us know.

    Cheers!

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:38 PM
    Wednesday, December 9, 2015 8:41 PM
  • ................but say I want to run it with just State, no records are returned and vice versa.

    The way to make parameters optional is to use the following syntax:

    (ColumnName = <parameter> OR <parameter> IS NULL)

    If the parameter is Null the expression will evaluate to True for any row, regardless of the value or absence of a value in the column.   Note that the expression is parenthesized.  This is important, to force it to evaluate independently of other expressions in the query's WHERE clause.  You'll find an example of this in the demo file MultiSelect.zip in my public databases folder at:

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

    This contains a query, qryEmployees_MultiFieldParam, whose SQL statement is:

    PARAMETERS Forms!frmMultiFieldDlg!txtDateFrom DATETIME,
    Forms!frmMultiFieldDlg!txtDateTo DATETIME;
    SELECT Employees.*, FirstName & " " & Lastname AS FullName, Projects.Project
    FROM Projects RIGHT JOIN (Employees LEFT JOIN ProjectEmployees
    ON Employees.EmployeeID=ProjectEmployees.EmployeeID)
    ON Projects.ProjectID=ProjectEmployees.ProjectID
    WHERE (City=Forms!frmMultiFieldDlg!cboCity
       OR Forms!frmMultiFieldDlg!cboCity IS NULL)
    AND (Department=Forms!frmMultiFieldDlg!cboDepartment
       OR Forms!frmMultiFieldDlg!cboDepartment IS NULL)
    AND (DateAppointed>=Forms!frmMultiFieldDlg!txtDateFrom
       OR Forms!frmMultiFieldDlg!txtDateFrom IS NULL)
    AND (DateAppointed<Forms!frmMultiFieldDlg!txtDateTo+1
       OR Forms!frmMultiFieldDlg!txtDateTo IS NULL);

    As you can see this query has four parameters, each if which can optionally have a value or be Null.  The OR operations which make each optional are each parenthesized so that they evaluate independently of the AND operations.

    I'd recommend that queries like this, or their WHERE clause at least, be written in SQL view, in which the logic is readily apparent.  The query should be saved in SQL view.  If saved in design view Access will move things around considerably; you'll see this if you switch the above query in my demo to design view.  It will probably still work, but the possibility of the query becoming too complex to open in some cases cannot be ruled out, and adding any further parameters to the WHERE clause will be difficult, whereas in SQL view it is a simple task to add one or more parenthesized OR expressions.

    One other interesting thing about this query is that, when testing for Null parameters in this way, parameters of date/time data type can (and should) be declared as such, but parameters of other data types should not as they can then never be Null, and the query will fail.


    Ken Sheridan, Stafford, England

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:38 PM
    Wednesday, December 9, 2015 10:07 PM

All replies

  • Hi js. It's "best practice" not to allow users have direct access to queries and tables. If you bind a form to the query, you can have your button open the form using the criteria selected on the unbound form without the need for parameters. Just my 2 cents...
    Wednesday, December 9, 2015 4:54 PM
  • Consider letting them see the results of the query in a subform and allow them to filter as needed.
    Take a look at this video

    https://www.youtube.com/watch?v=n8tHGFyt0NM&feature=iv&src_vid=jfdzT3lXk7E&annotation_id=annotation_3121751963

    and see how they use a datasheet view query based subform to filter selection and then run a report as filtered.
    Then use code like below to run report based on the subform filter if needed.

    Private Sub cmdPrintList_Click()
    Dim myReportFilter As String
    myReportFilter = Forms!MainFormName.SubformName.Form.Filter

    DoCmd.OpenReport "MyReportName", acViewPreview, , myReportFilter, acWindowNormal

    End Sub


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.



    • Edited by DriveEV Wednesday, December 9, 2015 5:03 PM
    Wednesday, December 9, 2015 5:02 PM
  •  A user can Select CustomerID, CustState, AccntRep and StartDate, EndDate  parameters using unbound text and combo boxes.

    If you have a Customer table with ID it should also contain the state where the customer does business/exist so there is no need to select CustState.

    Build a little, test a little

    Wednesday, December 9, 2015 5:06 PM
  • Thank you Karl, what I need to figure out here is how to pass the parameters from my form to the query using vba, keeping in mind that some parameters may be null
    Wednesday, December 9, 2015 5:21 PM
  • Hi js. If you saw my post earlier, I was saying you won't have to worry about using parameters to pass to the query if you use a form (or subform) to display the results. If you have any questions about how to do that, let us know.
    Wednesday, December 9, 2015 5:38 PM
  • Thanks DB, I'm really like to  build up my VBA skillset, but if you'd like to give an example of how to achieve this with your method please feel free.

    Thanks,

    js

    Wednesday, December 9, 2015 5:56 PM
  • Hi js. Not a problem. You can take a look at any of the samples available at UtterAccess. Let us know if you have any questions about any of them. Good luck!

    For example, you could start with this one: Simple Search Form

    Hope that helps...

    • Edited by .theDBguy Wednesday, December 9, 2015 6:03 PM
    Wednesday, December 9, 2015 5:58 PM
  • I agree with theDBguy that a query that reference the object on the form is the way to go without any VBA.

    Build a little, test a little

    Wednesday, December 9, 2015 6:01 PM
  • I like the concept of referencing the Form in the Object in the query and I understand it pretty well. I've got 2 parameters I'm using, Account Rep and State.  An unbound form works great when both parameters are entered, but say I want to run it with just State, no records are returned and vice versa. I'd like to give the user options and not have to enter all parameters to return results. Any suggestions?

    js

    Wednesday, December 9, 2015 8:17 PM
  • I have an unbound form which I would like to use to run and set query parameters using VBA. A user can Select CustomerID, CustState, AccntRep and StartDate, EndDate  parameters using unbound text and combo boxes. However the query must run properly whether the user specifies All or just one of the parameters. How can this be achieved with a on-click event of a command button?

    Hi js,

    Construct the WHERE  part of the SQL-string only on the the parameters that have a value.

    So if no value is given for CustomerID, CustState, AccntRep, StartDate, EndDate, then no WHERE part is constructed, and all records are retrieved.

    Imb.

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:38 PM
    Wednesday, December 9, 2015 8:28 PM
  • Any suggestions?

    js

    Hi js. I thought I gave you plenty.

    One thing to consider with using parameters, you have to worry about the problem you're trying to fix now. If you try my suggestion of not using parameters, then there's no problem if the user select all criteria or just a few or none at all. Again, if you have any questions on how to implement something like that, after you have played around with the demos in the page I linked above, then just let us know.

    Cheers!

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:38 PM
    Wednesday, December 9, 2015 8:41 PM
  • ................but say I want to run it with just State, no records are returned and vice versa.

    The way to make parameters optional is to use the following syntax:

    (ColumnName = <parameter> OR <parameter> IS NULL)

    If the parameter is Null the expression will evaluate to True for any row, regardless of the value or absence of a value in the column.   Note that the expression is parenthesized.  This is important, to force it to evaluate independently of other expressions in the query's WHERE clause.  You'll find an example of this in the demo file MultiSelect.zip in my public databases folder at:

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

    This contains a query, qryEmployees_MultiFieldParam, whose SQL statement is:

    PARAMETERS Forms!frmMultiFieldDlg!txtDateFrom DATETIME,
    Forms!frmMultiFieldDlg!txtDateTo DATETIME;
    SELECT Employees.*, FirstName & " " & Lastname AS FullName, Projects.Project
    FROM Projects RIGHT JOIN (Employees LEFT JOIN ProjectEmployees
    ON Employees.EmployeeID=ProjectEmployees.EmployeeID)
    ON Projects.ProjectID=ProjectEmployees.ProjectID
    WHERE (City=Forms!frmMultiFieldDlg!cboCity
       OR Forms!frmMultiFieldDlg!cboCity IS NULL)
    AND (Department=Forms!frmMultiFieldDlg!cboDepartment
       OR Forms!frmMultiFieldDlg!cboDepartment IS NULL)
    AND (DateAppointed>=Forms!frmMultiFieldDlg!txtDateFrom
       OR Forms!frmMultiFieldDlg!txtDateFrom IS NULL)
    AND (DateAppointed<Forms!frmMultiFieldDlg!txtDateTo+1
       OR Forms!frmMultiFieldDlg!txtDateTo IS NULL);

    As you can see this query has four parameters, each if which can optionally have a value or be Null.  The OR operations which make each optional are each parenthesized so that they evaluate independently of the AND operations.

    I'd recommend that queries like this, or their WHERE clause at least, be written in SQL view, in which the logic is readily apparent.  The query should be saved in SQL view.  If saved in design view Access will move things around considerably; you'll see this if you switch the above query in my demo to design view.  It will probably still work, but the possibility of the query becoming too complex to open in some cases cannot be ruled out, and adding any further parameters to the WHERE clause will be difficult, whereas in SQL view it is a simple task to add one or more parenthesized OR expressions.

    One other interesting thing about this query is that, when testing for Null parameters in this way, parameters of date/time data type can (and should) be declared as such, but parameters of other data types should not as they can then never be Null, and the query will fail.


    Ken Sheridan, Stafford, England

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:38 PM
    Wednesday, December 9, 2015 10:07 PM