Access 2003 choose selection for report

Posted on 2007-09-28
Medium Priority
Last Modified: 2013-11-28
I have a Access 2003 database. I am creating a report. When the report is opened I would like to be able to have the user select from a drop down what they want. Example: the choices to pick from would be DC DC5 or EW. When they picked from one of those it would bring all the recrods up for that. I could put in the query so that they would have to type in those options but it would be nice to have a drop down.
Question by:Pdeters
LVL 66

Accepted Solution

Jim Horn earned 1000 total points
ID: 19981484
(1)  Create a form that looks somewhat like a message box, with a 'Choose your choices' lablel, and a combo box with these DC, DC5, EW choices.
(2)  In the query that serves as your form's record source, write criteria in the DC, DC5, whatever column that has [Forms]![That Form Name]![That Combo Box Name] where the user made their selection.
(3)  Back on the form, add an Ok button that fires the report.  The report calls the query as the record source, which grabs the value from the form, and returns data to the report accordingly.

Author Comment

ID: 19981524
for the (1) form that the message box is in - do I create a query or jsut a combo box.
LVL 34

Expert Comment

ID: 19982664
you can also pass your DC, DC5 or whatever that you select from the combo box as criteria in your openReport command. Whichever's easier for you.
Hey Ho J-Ho!
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

LVL 66

Expert Comment

by:Jim Horn
ID: 19982847
>do I create a query or jsut a combo box.
The form is unbound, so no query.  Just a form that displays a 'Enter something here' label, and a combo box/text box that holds it.
(hey jw)

Author Comment

ID: 19983768
Is there a way that what is in the combo box to choose from can be automatically pulled from the query?
I have a query (table) that has a fiield that ony holds thos options DC or EW, but if another option is added can it automatically be a choice in the combo box? For exampel if I were to use th is for a choice of a person that may be in a field more than once.

Author Comment

ID: 19984593
I have the combo box made with the DC EW selections but I am not getting the next step.

2)  In the query that serves as your form's record source, write criteria in the DC, DC5, whatever column that has [Forms]![That Form Name]![That Combo Box Name] where the user made their selection.

LVL 19

Assisted Solution

frankytee earned 1000 total points
ID: 19987035
normally you would just want to filter the report to the relevant records so you would not need to rebuild or redefint the query sql unless you are getting the reports records from a different table or sets of table altogether
if its the former, then you just need to use the "WHERE" argument of the docmd.openreport method as shown below. if you need to display numerous "unrelated" records from the underlying query you can insert your combo values into a temp table and reference that in your "where" clause.

for eg, if your DC, DC5 and EW are values for a field called "myField",
and this field is naturally one of the fields in your report, try below and replace names accordingly

put this code in the form where your dc, dc5 combo box is.

Dim sWhere As String
    sWhere = "myFieldID = '" & me.mycombo & "'"  'whatever combo storing DC DC5 or EW etc
    'or you could insert these combo values into a "temp" table and then set your "where" clause to this temp table
    sWhere = "myFieldID in (select myFieldID from mytempTable)"
    DoCmd.OpenReport "myReport", acViewPreview, , sWhere

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question