locked
I need help with a query RRS feed

  • Question

  • Is there a simple way to create a query to find data, However I want the parameter to have a drop down list so the user can select a answer rather then typing. I have already set up a table which contains the data, I then set up a form using the data, However when I create a select query based on the form i.e Criteria = [Forms]![Formname]![Field to lookup] , the query runs but I still need to type a response...Please help...Thanks in advance.
    Thursday, June 24, 2010 12:20 AM

Answers

  • Adam,
     
    A query can't drop a list down; it has no active functionality at all. All the query can do is query data sources; in this case a table and a form's combo box. Let's start again.
     
    I don't really see why you need to use parameter queries in this case. If you have a form on which you can select an order and an office (that's what I thought "OriginOfficeForm" was), you can have a button called maybe "Print Order". Code behind the button calls the report and passes in the OrderID and OfficeID. The report's query will not need parameters and will not need to go looking for anything on a form. You'd just need to modify the query to include the table that contains the office information.
     
    The query would be generic, and would look something like this:
        SELECT Orders.field1, Orders.field2, OriginOffices.fieldx, OriginOffices.fieldy
        FROM Orders, OriginOffices
     
    The button' code would look something like this:
        Private Sub PrintOrder_Click()
            DoCmd.OpenReport "rptOrder", , , "Orders.OrderID = " & Me!cboOrderID & " AND OriginOffices.OfficeID = " & Me!cboOfficeID
        End Sub
     

    Hi Graham,

    I think I have worked out what I am doing incorrectly. Rather then trying to get the query to drop down a list I think I need to create another form which has the parameters and drop down lists on it and then send this info to the report....It makes sense in my mind now I will try to put it to practical...I will advise outcome.

    Adam.


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    • Marked as answer by adam_syd Friday, June 25, 2010 5:46 AM
    Friday, June 25, 2010 2:21 AM
  • Adam,

    Graham suggestion is a good one.

    You create a query - call it qryOrderRpt.  It contains the query that Graham suggested:
        SELECT Orders.field1, Orders.field2, OriginOffices.fieldx, OriginOffices.fieldy
        FROM Orders, OriginOffices

    You can expand it as you need it.

    This query is the Record Source to your report (call it rptOrder).

    In your form OriginOfficeForm, you have the process that gets the Order Number and the combo box that allows the user to specifiy the Office Number from the combo box.  From within OriginOfficeForm, you run the code Graham provided.  The DoCmd.OpenReport's "Orders....." clause will select the specified order and office record. and present the data to the rptOrder report.  You can then get the data as normal for the report.

    Good luck.

    Dennis

     

    • Marked as answer by adam_syd Friday, June 25, 2010 5:46 AM
    Friday, June 25, 2010 3:16 AM

All replies

  • Hi Adam,
     
    Access shouldn't be asking you for a value. Show us the entire SQL statement.
     
    Is there a simple way to create a query to find data, However I want the parameter to have a drop down list so the user can select a answer rather then typing. I have already set up a table which contains the data, I then set up a form using the data, However when I create a select query based on the form i.e Criteria = [Forms]![Formname]![Field to lookup] , the query runs but I still need to type a response...Please help...Thanks in advance.

    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Thursday, June 24, 2010 12:25 AM
  • Hi Graham,

    Maybe I can't do what I am trying to do? The end result that I am trying to achieve is to run a report and part of this report is to include contact details for one of my 6 different offices. I thought the best way to do this is create a table that contains the 6 offices and a query that looks up the country then returns other fields. This works and was easy to do however I now want to simply click on a drop down list that contains the values and select the correct one. If I simply put the query in I can still type the country and everything works however if a user types the country incorrectly then of course the result is a blank report. This is why I was trying to create the drop down list inside the query. Does this make sense? P.S - I am only a beginner in access so please be patient with me.

    Thursday, June 24, 2010 12:35 AM
  • You can do what you want, but we need more information about your design. If you can more fully describe your table, your form, which data you want and and where you want it to go, we can certainly help you do it.
     

    Hi Graham,

    Maybe I can't do what I am trying to do? The end result that I am trying to achieve is to run a report and part of this report is to include contact details for one of my 6 different offices. I thought the best way to do this is create a table that contains the 6 offices and a query that looks up the country then returns other fields. This works and was easy to do however I now want to simply click on a drop down list that contains the values and select the correct one. If I simply put the query in I can still type the country and everything works however if a user types the country incorrectly then of course the result is a blank report. This is why I was trying to create the drop down list inside the query. Does this make sense? P.S - I am only a beginner in access so please be patient with me.


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Thursday, June 24, 2010 12:50 AM
  • Adam,

    Yes, you can do what you want.

    1.  Create a form that a small unbound form.  (An unbound form does not have a Row Source for the form - ie - the form is not associated with any table.)
      - This form has a couple of controls on it.
         - A title label and maybe some instructional text.
         - A combo box that selects and displays the 6 offices from the Office table.  The user will then need to choose one of the offices.
         - A "Generate Report" or "Print Report" button.

    When the form is run, it will allow the user to choose one of the offices from the combo box.

    When the user clicks on the Generate Report button, I would check to make sure tjat the user choose an office.  If not, display an error message with MsgBox and set the focus on the combo box.

    Once the user has choosen an office and clicked on the Generate Report button, you would execute the following code from within the OnClick of OnDblClick event of the Generate Report button(assuming me.cboOffNo is the name of the combo box):

    Const cstrRptName as string = "rptRptName"
    Dim strWhere as string
    Dim strFilter as String

    strWhere = "OfficeNo= " & me.txtOffNo            ' use this if txtOffNo contains numberic data
    strWhere = "OfficeNo= '" & me.txtOffNo & "'"    ' use this if txtOffNo contains a string data

    docmd.openreport cstrRptName, acViewPreview, , strWhere

     

    This will run your report using the strWhere to select all the records where OffNo = the office number the user choose.  This ASSUMES that you have a working query that joins you office table to your data table and the field name in the query is OffNo.  (I think you do because you said "I thought the best way to do this is create a table that contains the 6 offices and a query that looks up the country then returns other fields.")

    You can also user strFilter in place of strWhere.  In that case, the DoCmd would be:

    docmd.openreport cstrRptName, acViewPreview, strFilter

    Personally, I have had less problems using the Where than the Filter parameter.  I don't know why.

    Note: '" is a single quote followed by double quote
    Note: "'" is a doube quote, single quote, double quote - you are surrounding the text with a single quote


    Maybe I can't do what I am trying to do? 
    Yes you can.  I do it all the time.  The above is the simple way to do it.  You can also have a query that retrieve a variable from an open form.  In the criteria row of query, for any field you put in the following code:

    = [forms]![formname]![controlname]

    the word '[forms]' must be entere as stated.  The formname is the actual name of your form, and the controlname is the name of the actual control on the form.

    You can also pass the selected value as an OpenArgs in the DoCmd statement and have your report retrieve that OpenArgs value  (look it up in the Access Help).  Your report can then set Me.ReportFilter = xxxxx and Me.ReportFilterOn = True (to turn on the filter).

    There are many ways to accomplish what you want.   Don't let the learning curve get to you.  Use this forum!!  There are a lot of very knowledge people here (I've still learning, but try to pass on the lessons I've learned from the MVPs.)

    If some of the stuff above was a little advanced, we can take it one step at time.   Just post your question and I'll try to respond.  Believe me, most everyone on this forum is pretty patient. 

    No worries mate.

     

    Dennis

     

    Thursday, June 24, 2010 7:09 AM
  • Hi Dennis,

    Thanks for your post. I'll try to give more detail as there may be a easier way to accomplish what I am trying to do. I use a form to enter details into which stores into a table then I use a report to print this information. What I have done as an addition is i want to include my other offices address and contact details on just the report only i.e not in the form or table, So I created a query that contains all the fields I want displayed then in the query I set the criteria as [Enter office location] this works and returns to the report the correct information. What I wish to do now is still run the same report & query but instead of typing the name of the office just select it from a drop down list. There is another query running at the same time which simply selects 1 record at a time (A ref number) Hope this makes sense??? Your answer above may be the correct answer I have not tested yet. I thought if the field I am trying to look up is a combo box on the form then the query should also treat it as a combo box?

    Rgds Adam.

    Thursday, June 24, 2010 7:38 AM
  • Hi Adam,
     
    What you;ve done so far seems right. The query should pull the data from the combobox, but I suspect you still have the parameter in the query, which would explain why it's still asking you for it.
     
    Open up the query in SQL mode. The first part of the query should look something like "PARAMETERS parametername Text ( 255 );". Just delete that text and all should be well with the world.
     

    Hi Dennis,

    Thanks for your post. I'll try to give more detail as there may be a easier way to accomplish what I am trying to do. I use a form to enter details into which stores into a table then I use a report to print this information. What I have done as an addition is i want to include my other offices address and contact details on just the report only i.e not in the form or table, So I created a query that contains all the fields I want displayed then in the query I set the criteria as [Enter office location] this works and returns to the report the correct information. What I wish to do now is still run the same report & query but instead of typing the name of the office just select it from a drop down list. There is another query running at the same time which simply selects 1 record at a time (A ref number) Hope this makes sense??? Your answer above may be the correct answer I have not tested yet. I thought if the field I am trying to look up is a combo box on the form then the query should also treat it as a combo box?

    Rgds Adam.


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Thursday, June 24, 2010 12:14 PM
  • Adam,

    Just out of curiosity and because I don't get to ask this questions too often, "Where in the world are you located?"

    -------------------

    Your comment : "I use a form to enter details into which stores into a table then I use a report to print this information.  What I have done as an addition is i want to include my other offices address and contact details on just the report only i.e not in the form or table."

    My Question:  How is the input table joined to the office address table?  Is there an office number in the input form / table? Are you only running the query as a stand alone query? Or are you using the query as a report's Record Source?

    -----------------

    Your comment:  So I created a query that contains all the fields I want displayed then in the query I set the criteria as [Enter office location] this works and returns to the report the correct information.

    My Question:  Are you saying that you enter an office location and there is no connection to the data in the table?  Is there an office number in the table?

    -------------------

    Your comment:  There is another query running at the same time which simply selects 1 record at a time (A ref number)
    My Question - What is does this comment have to do with the above comments?

    -------------------

    Your comment:  "I thought if the field I am trying to look up is a combo box on the form then the query should also treat it as a combo box?"

    Response:  Not really because you can not put a combo box in the Criteria section of the query.


    As I read you comment, I am begging to get the feeling that there is no Office Number field in the input form / data table.  Is that correct?

    I get the feeling that you are trying to enter the office number at report time even though there is no office number in the input data table.  You can do this, but there is a total different approach to doing this.  So this question of whether the office number is in the input data table is a critical question.  The whole approach depends upon the answer to that question.

    Is there or is there not a field for OfficeNo in the data input form / table?

    Dennis

    Thursday, June 24, 2010 3:41 PM
  • Is there a simple way to create a query to find data, However I want the parameter to have a drop down list so the user can select a answer rather then typing. I have already set up a table which contains the data, I then set up a form using the data, However when I create a select query based on the form i.e Criteria = [Forms]![Formname]![Field to lookup] , the query runs but I still need to type a response...Please help...Thanks in advance.


    If you're getting a prompt [Forms]![Formname]![Field to lookup], it suggests that the form named formname is not open, or that it does not have a combo box named [field to lookup]. The query will not automagically open the form for you; you should instead have the form open first; base the desired Report on a query using the form reference as a criterion; and put a command button on the form to open the Report. You do not need to explicitly open the Query, the report will do so.

     


    John W. Vinson/MVP
    Thursday, June 24, 2010 4:48 PM
  • Hi Graham, I have opened the query in SQL View and the following is displayed.

    SELECT OriginOffices.OriginOffice, OriginOffices.Add1, OriginOffices.Add2, OriginOffices.Suburb, OriginOffices.City, OriginOffices.Province, OriginOffices.ContactName, OriginOffices.[Contact Email], OriginOffices.ContactPh, OriginOffices.ContactFx, OriginOffices.ContactCell
    FROM OriginOffices
    WHERE (((OriginOffices.OriginOffice)=[Forms]![OriginOfficeForm]![OriginOffice]));

    So what I am trying to accomplish maybe easier to explain over the phone I am in Sydney as you are is there a way I can contact you? Or am I allowed to post email or msn or phone numbers here?

    Friday, June 25, 2010 12:16 AM
  • Hi Adam,
     
    Your SQL looks OK, but I'm confused - I thought the report was being called from the same form that the combo existed on (OriginOfficeForm). If that's not the case, then John has absolutely hit the nail on the head. Open OriginOffice and try again.
     

    Hi Graham, I have opened the query in SQL View and the following is displayed.

    SELECT OriginOffices.OriginOffice, OriginOffices.Add1, OriginOffices.Add2, OriginOffices.Suburb, OriginOffices.City, OriginOffices.Province, OriginOffices.ContactName, OriginOffices.[Contact Email], OriginOffices.ContactPh, OriginOffices.ContactFx, OriginOffices.ContactCell
    FROM OriginOffices
    WHERE (((OriginOffices.OriginOffice)=[Forms]![OriginOfficeForm]![OriginOffice ]));

    So what I am trying to accomplish maybe easier to explain over the phone I am in Sydney as you are is there a way I can contact you? Or am I allowed to post email or msn or phone numbers here?


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Friday, June 25, 2010 12:39 AM
  • Sorry, that should have been "Open OriginOfficeForm and try again".
     
    Hi Adam,
     
    Your SQL looks OK, but I'm confused - I thought the report was being called from the same form that the combo existed on (OriginOfficeForm). If that's not the case, then John has absolutely hit the nail on the head. Open OriginOffice and try again.
     

    Hi Graham, I have opened the query in SQL View and the following is displayed.

    SELECT OriginOffices.OriginOffice, OriginOffices.Add1, OriginOffices.Add2, OriginOffices.Suburb, OriginOffices.City, OriginOffices.Province, OriginOffices.ContactName, OriginOffices.[Contact Email], OriginOffices.ContactPh, OriginOffices.ContactFx, OriginOffices.ContactCell
    FROM OriginOffices
    WHERE (((OriginOffices.OriginOffice)=[Forms]![OriginOfficeForm]![OriginOffice ]));

    So what I am trying to accomplish maybe easier to explain over the phone I am in Sydney as you are is there a way I can contact you? Or am I allowed to post email or msn or phone numbers here?


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Friday, June 25, 2010 12:41 AM
  • Hi Dennis,

    I am in Sydney.

    Thanks for your reply. I will try to explain in more detail.

    I am using my database to provide quotations for my clients. I use 1 table called "Orders" to capture all the data however use a form to enter it. I then run a report that displays this info + some other text. To run the report I use 2 queries. The 1st query is based on a order number which is automatically generated when I enter a new quote into the "Orders table". Then I run another query which looks at a table that just contains my other offices and address details. The 2 tables are not linked as I do not wish to retain the other offices details inside the Orders table. So when I run my report the 1st parameter query asks for a order number then the second query asks for an office. What I am trying to accomplish relates to the 2nd part to display a drop down menu so I can just select the office Name. The actual query works and displays on the report 100% but I still have to type a name to achieve this result.

    Rgds,

    Adam.

     

    Friday, June 25, 2010 12:46 AM
  • I thought so too. But I tried this it still asks me to enter the office.

     

    Friday, June 25, 2010 12:48 AM
  • Hi Graham,

    I think I have worked out what I am doing incorrectly. Rather then trying to get the query to drop down a list I think I need to create another form which has the parameters and drop down lists on it and then send this info to the report....It makes sense in my mind now I will try to put it to practical...I will advise outcome.

    Adam.

    Friday, June 25, 2010 1:02 AM
  • Adam,
     
    A query can't drop a list down; it has no active functionality at all. All the query can do is query data sources; in this case a table and a form's combo box. Let's start again.
     
    I don't really see why you need to use parameter queries in this case. If you have a form on which you can select an order and an office (that's what I thought "OriginOfficeForm" was), you can have a button called maybe "Print Order". Code behind the button calls the report and passes in the OrderID and OfficeID. The report's query will not need parameters and will not need to go looking for anything on a form. You'd just need to modify the query to include the table that contains the office information.
     
    The query would be generic, and would look something like this:
        SELECT Orders.field1, Orders.field2, OriginOffices.fieldx, OriginOffices.fieldy
        FROM Orders, OriginOffices
     
    The button' code would look something like this:
        Private Sub PrintOrder_Click()
            DoCmd.OpenReport "rptOrder", , , "Orders.OrderID = " & Me!cboOrderID & " AND OriginOffices.OfficeID = " & Me!cboOfficeID
        End Sub
     

    Hi Graham,

    I think I have worked out what I am doing incorrectly. Rather then trying to get the query to drop down a list I think I need to create another form which has the parameters and drop down lists on it and then send this info to the report....It makes sense in my mind now I will try to put it to practical...I will advise outcome.

    Adam.


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    • Marked as answer by adam_syd Friday, June 25, 2010 5:46 AM
    Friday, June 25, 2010 2:21 AM
  • Adam,

    Graham suggestion is a good one.

    You create a query - call it qryOrderRpt.  It contains the query that Graham suggested:
        SELECT Orders.field1, Orders.field2, OriginOffices.fieldx, OriginOffices.fieldy
        FROM Orders, OriginOffices

    You can expand it as you need it.

    This query is the Record Source to your report (call it rptOrder).

    In your form OriginOfficeForm, you have the process that gets the Order Number and the combo box that allows the user to specifiy the Office Number from the combo box.  From within OriginOfficeForm, you run the code Graham provided.  The DoCmd.OpenReport's "Orders....." clause will select the specified order and office record. and present the data to the rptOrder report.  You can then get the data as normal for the report.

    Good luck.

    Dennis

     

    • Marked as answer by adam_syd Friday, June 25, 2010 5:46 AM
    Friday, June 25, 2010 3:16 AM
  • Graham/Dennis.

    I have worked it out...Not sure I have done it your way however it is working and I am very happy. I am sure what you have suggested is correct and what I have done is similar (but not as good) as your suggestion.

    I created another field in my Orders table called Origin Office. Then I created a combo box on the form that the user selects and this stores into the table. Then I added this field to the query and hey presto it works...At least the query anyway... I will now amend the report and be back in business.

     

    Thanks guys for all your help. It was much appreciated.

     

    P.S - Hey Graham do you run training courses for Dummies like me? I want personal lessons not classroom... Let me know.

    Friday, June 25, 2010 5:33 AM
  • No, but I get hired to mentor existing dev teams from time to time. I don't know of anyone doing credible Access training in Sydney.
     
    I convene an annual software developer conference called Office DevCon (www.officedevcon.com.au). It's back in Sydney this year (6th-7th November). There's not much on this year's site yet, but some of the luminaries who walk the halls of this forum will be presenting. I may be able to get some Microsofties to come back too. Take a look at the lineup from last year (www.officedevc on.om.au/2009/presenters.htm), and their sessions (www.officedevcon .om.au/2009/sessions.htm). I'd suggest registering and turning up. I learn a lot from these guys, plus it's a good way to network.
     

    Graham/Dennis.

    I have worked it out...Not sure I have done it your way however it is working and I am very happy. I am sure what you have suggested is correct and what I have done is similar (but not as good) as your suggestion.

    I created another field in my Orders table called Origin Office. Then I created a combo box on the form that the user selects and this stores into the table. Then I added this field to the query and hey presto it works...At least the query anyway... I will now amend the report and be back in business.

     

    Thanks guys for all your help. It was much appreciated.

     

    P.S - Hey Graham do you run training courses for Dummies like me? I want personal lessons not classroom... Let me know.


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Friday, June 25, 2010 6:29 AM
  • Sorry, the keyboard must be on it's way out. Here's that paragraph again with proper URLs.
     
    I convene an annual software developer conference called Office DevCon (www.officedevcon.com.au). It's back in Sydney this year (6th-7th November). There's not much on this year's site yet, but some of the luminaries who walk the halls of this forum will be presenting. I may be able to get some Microsofties to come back too. Take a look at the lineup from last year (www.officed evcon.com.au/2009/presenters.htm), and their sessions (www.officedevco n.com.au/2009/sessions.htm). I'd suggest registering and turning up. I learn a lot from these guys, plus it's a good way to network.
     
     
    No, but I get hired to mentor existing dev teams from time to time. I don't know of anyone doing credible Access training in Sydney.
     
    I convene an annual software developer conference called Office DevCon (www.officedevcon.com.au). It's back in Sydney this year (6th-7th November). There's not much on this year's site yet, but some of the luminaries who walk the halls of this forum will be presenting. I may be able to get some Microsofties to come back too. Take a look at the lineup from last year (www.officedevc on.om.au/2009/presenters.htm), and their sessions (www.officedevcon .om.au/2009/sessions.htm). I'd suggest registering and turning up. I learn a lot from these guys, plus it's a good way to network.
     

    Graham/Dennis.

    I have worked it out...Not sure I have done it your way however it is working and I am very happy. I am sure what you have suggested is correct and what I have done is similar (but not as good) as your suggestion.

    I created another field in my Orders table called Origin Office. Then I created a combo box on the form that the user selects and this stores into the table. Then I added this field to the query and hey presto it works...At least the query anyway... I will now amend the report and be back in business.

     

    Thanks guys for all your help. It was much appreciated.

     

    P.S - Hey Graham do you run training courses for Dummies like me? I want personal lessons not classroom... Let me know.


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Friday, June 25, 2010 6:33 AM
  • Interesting, if not frustrating. The URLs just won't come out properly. I double-checked before posting and they were fine. Anyway, you can see the forum has introduced spaces in the URLs, and removed the "c" from .com.au. I hope you can figure it out.
    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Friday, June 25, 2010 6:39 AM
  • Adam,

    What you did is the proper way to do it.  Actually, I originally though it was the way you had done it initially.  It was not until I re-read one of your posting where I realized that the office number was NOT in the Order table.

    My original suggestion way back at the top assumed (big mistake on my part) that there was an office field in the Order table.

    You can have the report call the form that gather the information or you can have the form gather the information and then call the report (DoCmd.OpenForm or DoCmd.OpenReport).

    If you have report call the form, there is a trick you have to use.  In addition to the form asking for the pertinent informatoin (order number, office number, etc.), the you should also have a Generate Report button.

    I described what the button should do in my original posting.  There is one thing I left out though.  The last line of code in the Generate Report button should be:

    ME.VISIBLE = FALSE

    This makes the form "disappear" but remain "open".  As long as the form is "open". you can access the variables in this form using [forms]![frmName]![ctrlName].  However, in the On Close event of the report you need to include the line:

    DoCmd.CloseForm frmname

    You need to do this because the frmname is till open since we never closed it.

    Good luck with the learning curve.  This forum will help you a LOT.  One year ago I could not spell Aces, now I'm helping others.  I've learned a LOT from the people who contribute to this forum and I have a LONG way to go.

    If you can get the book, the Access 2002 Desktop Developer's Handbook and Enterprise Developer's Handbook set by Litwin Getz Gunderloy are great.  There's also a 2007 version, but I have not seen the 2010 version yet.

    Good luck with your learning curve.

    Dennis

    Saturday, June 26, 2010 5:22 AM