Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Capture User Response (97)

    Hi Everyone!

    I have a query that has a paremeter in it requesting a "Name", this query is the the underlying recordsource for a report.

    In the "No Data" for the report, I would like to capture the users response and include it in my msg.

    For instance...I open form, query requests name, user types "Roberta", if no data is found for "Roberta", the NoData should return the following message: There is no data found for Roberta, Try Again....

    I know the coding for the NoData, I just don't know how to capture the users Response,,,,,,please advise.

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture User Response (97)

    Check out Post 141828. It contains two options to get a parameter value on the report.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture User Response (97)

    Thanks for the suggestion, however, I don't think either of those suggestions will work....as this code is in the onNoData event of the report...i.e. ... if the parameter value doesn't return data for the report, the report closes....so there isn't an opportunity to pass the value to a text box. If I'm missing something...this isn't unusual....LOL

    Again thanks for the suggestion....hope there are others out there.

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture User Response (97)

    Hi Roberta,

    I have often used a different method to check for data. I build a duplicate of the Report's Recordsource using DAO in the OnClick event of the button that will launch the report. I check the record count property before even opening the report. This is the place where I would show a message box with the parameter rather than attempting to open the report first.

    The disadvantage is that you have to maintain the Report's recordsource in two places - unless you set the SQL Statement (in the form) to Report_<ReportName>.Recordsource...you'll have to see if it will work.

    Let me know if you need me to elaborate on my method.

    HTH

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture User Response (97)

    Mark,

    You have my undivided attention!

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture User Response (97)

    Does that mean you need more info? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Capture User Response (97)

    If the user is opening a form before generating the report, you could get the user to enter their name into a textbox (or perahps a combobox) on the form, and have the query get the name from this textbox. the "Nodata" event could also get the name from the same textbox, and include it in the msg.
    Regards
    John



  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture User Response (97)

    Mark,

    Yes, if you don't mind.....I didn't think it would be difficult...but perhaps it is...

    It's almost as if I have to pass the value to another form and then reference that form and control to the report....am I on the right track?

    As always Mark, thanks for your assistance.

    Roberta
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture User Response (97)

    Hi Roberta,

    Here is a brief example. It's not as hard as it may seem. Assume you have a text box (txtOrderNumber) where your user will enter criteria for your report. Assume you're checking tblOrders for OrderNumber. Keep in mind the SQL statement must be the same as the RecordSource of your report. You can probably get creative and use your Report's RecordSource property (as long as it is pulling its criteria from the same text field in question and as long as it has the HasModule property set to Yes - so you can reference its properties in code). That might look like ...db.OpenRecordset(Report_OrderReport.RecordSourc e, dbOpenSnapshot)...but I haven't tried it...

    Here's the OnClick code for the button (btnOpenReport):<pre>Private Sub btnOpenReport_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM tblOrders " _
    & "WHERE OrderNumber=" & Me!txtOrderNumber & ";",dbOpenSnapshot)

    If rst.RecordCount < 1 Then
    MsgBox "There are no orders with Order Number " & Me!txtOrderNumber
    Else
    DoCmd.OpenReport "OrderReport", acViewPreview
    End If

    rst.Close
    db.Close
    set rst = Nothing
    set db = Nothing
    End Sub</pre>

    HTH

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Capture User Response (97)

    I don't see why you need to create another recordset for this.
    Why can't the nodate event for the report be like this:

    Private Sub Report_NoData(Cancel As Integer)
    msgbox ("There were no orders for " & forms!frmcriteriaform!txtordernumber)
    Cancel = True
    End Sub
    Regards
    John



  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture User Response (97)

    OK All,

    I took what I thought as a simpler approach and all works well with one exception...

    The following is my OnNoData event:

    On Error GoTo NoDataClose_Err

    Dim strResponse As String

    strResponse = Forms!frmActSearch!txtActSearch & " " & "Wasn't Found"

    MsgBox (strResponse)

    Cancel = True

    NoDataClose_Exit:
    Exit Sub

    NoDataClose_Err:
    MsgBox Error$
    Resume NoDataClose_Exit

    All is well...work great.....I am passing the User Response Value from a form to the underlying recordsource (query) for the report....

    My one small problem is this error message at the end: The OpenReport Was Cancelled, You used a method of the docmd object to carry out an action in visual basic, but then clicked cancel in the dialog box, etc....

    Why...I've included error handling...the report should just close...that's it...why do I get this message..

    Thanks everyone.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Capture User Response (97)

    I suppose you open the report from a command button on the form with OnClick code like

    DoCmd.OpenReport "rptMyReport", acViewPreview, , ...

    If the opening of the report is canceled, an error 2501 occurs. So in your error handler in the OnClick event procedure of the command button, just ignore error 2501:

    Private Sub cmdReport_Click()
    On Error Goto Err_Click
    DoCmd.OpenReport "rptMyReport", acViewPreview, , ...
    Exit Sub

    Err_Click:
    If Err <> 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture User Response (97)

    HansV,

    Thanks so much...works great....
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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