Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Event Procedure Question (A2K)

    I am trying to design a report that stops for the user to enter dates that will be incorporated into the report. I know that I should use the report property OnOpen and pick event procedure but am new to VBA so I don't know how to build the event. What I would like is something similar to the ASK field in Word. Is this possible? Many thanks for any help.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Event Procedure Question (A2K)

    Jeanne,

    Try this:
    Dim strAnswer As String
    strAnswer = InputBox("Your question", "Tittle of the box")
    Francois

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report Event Procedure Question (A2K)

    >>strAnswer = InputBox("Your question", "Tittle of the box")<<

    I keep telling Eileen she should incorporate the VBA editor into her message editor, otherwise mistakes like the one above keep creeping in. (I make them all the time!)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Event Procedure Question (A2K)

    Mark,
    Where is the mistake ?
    I past it in the vba editor of access and it runs ?
    Francois

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Event Procedure Question (A2K)

    Okay, I know this is gonna sound stupid but... the code works, it brings up the box and all. How do I get it into the report? I put in a text box called strAnswer (in design view on the report) and another dialogue box pops up asking for the parameter value. I put my dates in and that's what gets put into the report.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Event Procedure Question (A2K)

    Jeanne,
    Remove the textbox strAnswer and complete the code in the on open event with this:

    <pre>Private Sub Report_Open(Cancel As Integer)
    Dim strAnswer As String
    Do While IsDate(strAnswer) = False
    strAnswer = InputBox("Your question", "Tittle of the box")
    If IsDate(strAnswer) = False Then
    MsgBox "You have to enter a date formated as mm/dd/yyyy", vbOKOnly
    End If
    Loop
    Me.Filter = "[yourDateField] = '" & CDate(strAnswer) & "'"
    Me.FilterOn = True
    End Sub
    </pre>

    add also code in the No Data event
    <pre>Private Sub Report_NoData(Cancel As Integer)
    MsgBox "No records for your input", vbOKOnly
    Cancel = True
    End Sub</pre>

    so the user don't get an error if there is no data for the selected date.

    I hope that Mark don't see more mistakes <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15> <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Francois

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Event Procedure Question (A2K)

    Jeanne:

    Another way you might go about it is to build what is known as a parameter query, and base the report on the parameter query. In the query, pick a date field and type the following in the criteria row:

    >[Enter data in mm/dd/yyyy format]

    When you run the query, you'll be prompted with a dialog box that displays the text between the square brackets. Enter a date, and the results of the query will be for all records where the date is after the date you entered when prompted. If you base a report on this query, the same thing will happen.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report Event Procedure Question (A2K)

    >>Where is the mistake ?<<

    Francois.

    My mistake! I just glanced at it, and my mind saw "msgbox" not "inputbox". Trying to do too much, too quickly, I guess.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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