Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Location
    Adelaide, South Australia, Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run report from Combo box selection (Access 2003)

    I am using an exisiting database that is based on one table and lots of queries. I am trying to generate a report based on one field (Named Day 1) which has about 300 records already but is only 5 dates. (but these are not generated from a look up table)

    I would like to run a report that lists all of the records for one of these dates without using the open parameter box but a drop down box (combo box) that displays only these 5 dates for them to choose and then generates the report.

    I hope this makes sense. I am not that VB literate so dummy instructions would be greatly appreciated.
    Thanks
    Mad Penguin
    Everything is Possible, the Impossible just takes Longer
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run report from Combo box selection (Access 2003)

    1) Create a report without bothering about the date restriction.
    Make sure that the relevant date field is included in the record source of the report.
    Let's say that the report is saved as rptMyReport (this is just an example.)

    2) Create a query based on the table.
    Add the date field to the query grid.
    Click in an empty part of the upper half of the query design window.
    Activate the Properties window.
    Set the Unique Values property of the query to Yes.
    Save this query as qryDates (for example, you can specify a different name if you prefer).
    This query returns the list of unique dates (there should be 5 of them).

    3) Create an unbound form (i.e. the record source of the form is blank.)
    Place a combo box on the form.
    Name it cboDate.
    Set the Row Source property of the form to qryDates.
    Set the Limit to List property to Yes.

    Place a command button next to the combo box.
    Name it cmdOK.
    Set its Caption property to OK.
    With the command button selected, activate the Event tab of the Properties window.
    Click in the On Click box.
    Select [Event Procedure] from the dropdown list.
    Click the ... button to the right of the dropdown arrow in the On Click box.
    This will open the Visual Basic Editor.

    Make the code look like this, with the appropriate names substituted:
    <code>
    Private Sub cmdOK_Click()
    Dim strWhere As String
    If Not IsNull(Me.cboDate) Then
    strWhere = "[Day 1] = #" & Format(Me.cboDate, "mm/dd/yyyy") & "#"
    End If
    DoCmd.OpenReport ReportName:="rptMyReport", _
    View:=acViewPreview, WhereCondition:=strWhere
    End Sub
    </code>
    Switch back to Access (using the task bar or Alt+F11).
    You can set properties of the form, such as the caption, the way you like.
    Save the form, then switch to form view.

    If you leave the combo box empty, clicking the OK button will open the report with all dates included.
    If you select a date from the combo box, the OK button will open the report for that date.

  3. #3
    New Lounger
    Join Date
    Nov 2006
    Location
    Adelaide, South Australia, Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run report from Combo box selection (Access 2003)

    Hans,

    Thankyou for your assistance and once i have had a chance to give this a go will let you know how it all went.

    The wording in my original post was a little off I do know a bit about VBA, but most of my experience is in Excel. I know enough to get through in ACCESS but are still learning.
    Mad Penguin
    Everything is Possible, the Impossible just takes Longer
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

  4. #4
    New Lounger
    Join Date
    Nov 2006
    Location
    Adelaide, South Australia, Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run report from Combo box selection (Access 2003)

    Hans,

    I have manage to get your instructions to work, however i have got a little stuck. Because the data comes from a text file the date field is in text format.
    Changing the field format is not really an option because I have a user using it with limited Access knowledge and have had to automate a lot of the forms for them to use it easily.

    From the VBA that you gave me what change would i have to make to adapt to reading the file as a text field.

    I have tried taking out the # 's and just using the name of the field but it keeps falling over.

    Many Thanks in Advance
    Mad Penguin
    Everything is Possible, the Impossible just takes Longer
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run report from Combo box selection (Access 2003)

    Try this:

    strWhere = "[Day 1] = " & Chr(34) & Me.cboDate & Chr(34)

    Text values must be enclosed in quotes; Chr(34) is the double quote character "

    Replace Day 1 with the correct name of the field, and cboDate with the name of the combo box.

  6. #6
    New Lounger
    Join Date
    Nov 2006
    Location
    Adelaide, South Australia, Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run report from Combo box selection (Access 2003)

    Hans,

    Thankyou for all your assistance with this. It works lovely now.

    regards
    Mad Penguin
    Everything is Possible, the Impossible just takes Longer
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

Posting Permissions

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