Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    34

    Unanswered: Can This Be Done In Access?

    I have 5 different locations (sites) we'll call A, B, C, D, E. The location
    is used in all my queries. I have 5 different command buttons that when I
    click on one it takes the user to a form with information pertaining to that
    location. Example: Click on command button A takes user to location A.
    Instead of writing 5 different queries for each location, is there a way to
    pass the location to the query once the command button is clicked?

    Obviously I am a newbie at this and any help is appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    if your parameterise your query. Assuming that you are usign the query designer place something in the criteria for the relevant column which identifies what the parameter you want is.

    I'm not to sure how you would use the 5 buttons but you need to lace some code in there which is common to all 5 buttons. you could use a radio button in place of a command button, you could use the command button to set a value in the form

    When I've hot this problem beofre I tend to use a combo box, listing the options availabel to the user. That way round if the organisation adds another site it automatically comes through on the combo, so it doesn't require any redesign by you.

    lets assume that you have a form called frmSelectSite, on it you have a combobox called cmbSelectSite
    then your criteria in the query designer is
    =forms!frmSelectSite!cmbSelectSite
    Note the form containing the combobox would have to be open certainly at the start of running the query, but it would probably be best to ensure tha tthe form is open whilst the query (and any thing running off that query) is running.
    HTH

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    I have 5 different command buttons that when I click on one it takes the user to a form with information pertaining to that
    location.
    Here's something I put together that may give you some ideas about making time periods and other criteria dynamic.

    The free downloadable sample database at www.bullschmidt.com/access uses the query by form concept so that on the invoices dialog one can optionally choose a rep, a customer, and perhaps a date range, click on a button that says "Input," and then have the invoice form open up showing all the invoices that match the criteria.

    And here is how the query by form concept can work.

    On the invoices dialog there are the following controls:
    InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
    InvDateMax with DefaultValue of =Date()
    InvRepNum with DefaultValue of *
    InvCustNum with DefaultValue of *

    Also on the invoices dialog there is a command button called cmdInput to open the invoices form with the following code behind the OnClick property:
    DoCmd.OpenForm "frmInv"

    And of course there could be a button to open a report the same way:
    DoCmd.OpenReport "rptInv", acViewPreview

    The invoices form (frmInv) has RecordSource property of qryInv.

    And the qryInv query's criteria for the InvDate field has:
    Between [Forms]![frmInvDialog]![InvDateMin] And [Forms]![frmInvDialog]![InvDateMax]

    And the qryInv query's criteria for the RepNum field has:
    Like [Forms]![frmInvDialog]![InvRepNum]

    And the qryInv query's criteria for the CustNum field has:
    Like [Forms]![frmInvDialog]![CustNum]

    One related point is that you probably wouldn't want to allow blanks (i.e. Nulls) in fields that are going to be used with Like in any criteria for that field. Otherwise the blanks wouldn't be shown.

    For example (based on what is entered into a last name search field):

    Like 'Smith' would show Smith records

    Like '' would show no records (probably not what one would want)

    Like '*' would show all records

    And to counter that I like to have the search fields have a DefaultValue of * and not allow the search fields to be blank.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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