none
Report never shows up but no errors RRS feed

  • Question

  • I've never had this happen before. I've been developing in Access for nearly 20 years.

    Using Access 2010 (ADP file) and SQL Server 2005.

    I have a form with buttons on it that runs some reports. They all work fine. They are all set up in a similar way. When reports appear in preview I hide the form with the report buttons. This new report's code takes about a minute or so to generate the result set. Sometimes the preview shows up as expected. But with the EXACT SAME criteria, sometimes it never appears at all and we go right back to the report form. When it doesn't appear, it does NOT generate any errors. When I run the SQL query in SSMS it runs fine and returns exactly what I expect. The problem only occurs when strGetScannedDocTypeCode = "ADJ"

    Here's my code. I've been using similar code for a long time and have never run into this problem.

    Private Sub Report_Open(Cancel As Integer) On Error GoTo Err_Report_Open     Dim rs As ADODB.Recordset     Set rs = New ADODB.Recordset     Dim sSql As String     'cnnCurrProj.CommandTimeout = 300          If strGetScannedDocTypeCode <> "ADJ" Then              sSql = "EXEC sp_FortisDocumentSearch_Simple " _
                & " @ScannedDocTypeCode = '" & strGetScannedDocTypeCode & "', " _
                & " @DocTypeStartDate = '" & Format(dteStartDate, "mm/dd/yyyy") & "', " _
                & " @DocTypeEndDate = '" & Format(dteEndDate, "mm/dd/yyyy") & "', " _
                & " @AdjDates = '" & strAdjDates & "', " _
                & " @ScannedOnOrAfterDate = '" & Format(dteGetDate, "mm/dd/yyyy") & "', " _
                & " @Counties = '" & Replace(strGetCounties, "'", "''''") & "'"

        Else              sSql = "EXEC sp_FortisDocumentSearch_Simple " _
                & " @ScannedDocTypeCode = '" & strGetScannedDocTypeCode & "', " _
                & " @DocTypeStartDate = NULL, " _
                & " @DocTypeEndDate = NULL, " _
                & " @AdjDates = '" & strAdjDates & "', " _
                & " @ScannedOnOrAfterDate = '" & Format(dteGetDate, "mm/dd/yyyy") & "', " _
                & " @Counties = '" & Replace(strGetCounties, "'", "''''") & "'"          End If          rs.Open sSql, cnnCurrProj, adOpenStatic     Me.RecordSource = rs.Source     DoCmd.Maximize          rs.Close      Exit_Report_Open:     Set rs = Nothing     Exit Sub Err_Report_Open:     MsgBox Err.Description & ", # " & Str(Err.Number)     Resume Exit_Report_Open End Sub


    I'll note that when strGetScannedDocTypeCode is NOT "ADJ" it ALWAYS runs correctly. It runs a lot faster, but it always shows up. it's only when strGetScannedDocTypeCode = "ADJ" that we have a problem. And again, it's not predictable. I can run it 10 times in a row using the same criteria each time and sometimes it works and sometimes it doesn't.

    When I create a new report for this client (of 16 years) I generally just copy and paste and make the appropriate changes to the sSql variable. And like I said, it works fine for all the other reports in the application (there are dozens).

    I've changed the timeout. It's been 60, 180, 300. I've commented it out too as you can see. Interestingly, even with it commented out, I can get the thing to run sometimes and it always takes about one minute to run.

    Here's the other code in the report:

    Private Sub Report_Close()
    On Error GoTo Err_Report_Close
    
        If strPrintPreview = "Preview" Then
            Forms(strReportCallingMenu).Visible = True
        End If
        
        cnnCurrProj.CommandTimeout = 0
        
    Exit_Report_Close:
        Exit Sub
    
    Err_Report_Close:
        MsgBox Err.Description & ", # " & Str(Err.Number)
        Resume Exit_Report_Close
        
    End Sub
    
    Private Sub Report_NoData(Cancel As Integer)
    On Error GoTo Err_Report_NoData
    
        MsgBox "No data for the criteria specified."
        Cancel = True
        
    Exit_Report_NoData:
        Exit Sub
    
    Err_Report_NoData:
        MsgBox Err.Description & ", # " & Str(Err.Number)
        Resume Exit_Report_NoData
        
    End Sub

    I have created a blank new adp and imported all the objects into it to test to see if there was any adp corruption. That didn't solve the problem.

    So any ideas about this business of the report never appearing and then just making the calling form visible again?

    Thanks,

    Keith


    • Edited by keithrh Thursday, October 20, 2016 5:11 PM
    • Moved by Steve Fan Friday, October 21, 2016 6:27 AM
    Thursday, October 20, 2016 4:58 PM

All replies

  • Hi,

    This forum is for general questions and feedback related to Microsoft Office 2010. Since your question is more related to code, I'll move it to the Access for Developers forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Steve Fan


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, October 21, 2016 6:25 AM
  • Does the report open if you try to preview it directly from the navigation pane?  Have you stepped through this with the VBA debugger?  I would first try putting a breakpoint on your rs.Open or just put a debug.print sSql directly in your code and see exactly what's in sSql at that point.  Then copy and paste that directly into SSMS and see what you get.  If that all looks good then perhaps create a temporary table from the output of your exec statement and then try using that directly as the recordsource for your report and see if it opens and runs correctly.

    -Bruce

    Friday, October 21, 2016 11:07 PM
  • Hi Keithrh,

    As other community member already mentioned to debug the code. I am agree with that.

    also I recommend you to add Try Catch in your code. so if some error occurs you can catch the error and can know about that.

    also you had mentioned that ,"when strGetScannedDocTypeCode is NOT "ADJ" it ALWAYS runs correctly"

    can you tell me what's the different between that. are you doing something else when strGetScannedDocTypeCode is "ADJ"?

    I notice that when it is "ADJ" you pass the NULL in start and end date.

    I recommend you to run these both sql statements manually and note the time.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 24, 2016 2:43 AM
    Moderator
  • My two best guess?

    Well, the timeout issue is possible (and you addressed that issue – but it might be that the timeout is not being respected).

    The other issue could be some form/report focus issue. Are there any forms with a timer event running? I would for testing disable that form/code with any timer events.

    I wish had more to suggest then above – but I have seen a “focus” issue occur with a timer event – and thus a report not appearing.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Monday, October 24, 2016 8:06 AM
  • Uh, I thought I did post it appropriately! I've been doing this for a long long time. Usenet long before the forums were a thing. And many forums. I have to say, the MS forums are the most convoluted ones out there. No other company has any forum system that's so screwed up and confusing to use. On  one of your forum pages there isn't even a login button anywhere to be found. You have to "ask a question" just to get to the login page. It's a mess.
    Monday, October 24, 2016 1:37 PM
  • Oh my. I said that I've been doing this for over 20 years and many of those with Access and you're asking me if I did those first year programmer things? Seriously? "Have you stepped through the code?" WHAT? Ugh. First, this report cannot be opened from the design window or navigation pane without providing it with a table or view source. That completely fouls up the test. Whether that works or not tells us nothing. This works and then doesn't work 2 minutes later. It's inconsistent even when the criteria entered are EXACTLY the same each time.
    Monday, October 24, 2016 1:41 PM
  • Correct. It's not a focus issue. That was the first thing I considered. I've seen that many times with MS Access in the over 20 years I've been working with it. It's a common bug in MS Access.

    The only thing I can think of is some sort of weird disconnect with SQL Server that's not being reported to the front end. It can't time out. The backend only takes about 1 minute to generate the data and I have the timeout set to 5 minutes. But oddly, even if I set the timeout to the default sometimes it works and not in under 30 seconds.

    Monday, October 24, 2016 1:44 PM
  • Look folks. I said I've been doing this for a long long time. I'm not  looking for "Did you plug in the computer" types of answers. I attempted to explain all the things I've tried so far so that I wouldn't get those types of answers. I never expected anyone to ask me if I "stepped through the code". LOL. I need some creative thinking here or someone who's seen this exact problem to jump in and tell me what the heck might be going on. Thanks. Keith
    Monday, October 24, 2016 1:47 PM
  • Why don 't my answers nest with the posts I replied to???? I've always wondered that about MS forums. Most forums do a hierarchical thing. So there's no way apart from context to know which of my answers goes with which. Ugh. You'd think with the huge deep pockets MS has that they could do things better in general. :(
    Monday, October 24, 2016 2:52 PM
  • I appreciate your frustration.  I've been doing this for over 20 years myself, since Access 2.0 and including ADP's.  That being said I will still occasionally overlook the obvious or be less than completely systematic in my debugging approach.  I am sorry that the time I took to read your post and step through your code provided you with a less than useful answer. 

    -Bruce

    Monday, October 24, 2016 3:47 PM
  • I noticed you have not defined strGetScannedDocTypeCode. If you:

    Dim strGetScannedDocTypeCode As String

    does that help?

    Also, is strGetScannedDocTypeCode a field in both the report and the calling form? If you use:

     If Me.strGetScannedDocTypeCode <> "ADJ" Then

    does that help?

    I don't want to insult anybodys intelligence, just noting a couple of things.

    Monday, October 24, 2016 4:12 PM
  • strGetScannedDocTypeCode is defined publicly in a different module

    and no, it's not a field at all, it's not a control on a form. it's just a front end variable parameter so the code knows which version of the query to run.

    AFAIC there has to be some sort of bug in Access. If you could see this run and how it's put together you'd agree. It makes no sense whatsoever.

    Just for the helluvit I'm going to have the stored procedure populate a permanent temp table and use that as the source for the report and see if that helps. I'll let  you all know what happens. But even if it works, it still doesn't solve this mystery.


    Monday, October 24, 2016 7:29 PM
  • My posts are nesting just fine.

    And single stepping thought the code can tell significant issues.

    If you single step through the code and it ALWAYS works, then issues of timing, form/report focus most certainly DOES come into play. And that’s why I asked about if any timers are active in any form. (because a timer changes focus for a tiny instant - thus intermittent failures).

    And I can EASY state many posts exist that states code works when single steeping though code, but not when running the code. (and we seen the reverse also!).

    So single stepping though the code can yield some light on this issue – especially if by doing so you NEVER see nor reproduce the error!

    So have you EVER been able to reproduce the problem by setting a break point and single stepping though that on-open code is most certainly a legitimate question and concern. (but of course you would know this given your experience – right?)

    Since the “issue” is intermittent, then stepping though the code would reduce or eliminate a possible timing or threading issue. It might be due to multi-threaded processors and the code not waiting until the rst is finished loading – we don’t know at this point.

    And sure, this threading/timing issue may well be a bug, but the question and suggestion to single step though the code STILL remains a valid question and troubleshooting suggestion.

    So, have you seen the error occur when you single step with a break point in the on-open event?

    Because if you never seen the error by single stepping, then this “may” shed light on this issue. A small delay between the rst loading and the assignment to the forms recordset would explain why single stepping works, but running does not.

    And do keep in mind that people here posting are trying to help. The people here in most cases are volunteer their time. That means you going to have suggestings comming from relative newcomers and also from experts like me.

    And I dare say that not all of the posters here would have the knowledge that single stepping though code could shed light on this issue. I assume you do?

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Monday, October 24, 2016 8:55 PM
  • We don't always know the level of people asking questions here and it never hurts to go back to basics. In many, if not most cases, it is the basics that aren't done correctly that cause problem.

    Beyond which, I'd never knock ANYTHING Albert suggests.  In all the years I've "know" him (known in the sense of seen him online) he has always been one of the most knowledgeable people with quite the sense of understand and figuring out things that most people overlook.  If he suggests doing something as simple as stepping through your code, you can be sure there is a very good reason he is mentioning it!


    Daniel Pineault, 2010-2016
    Microsoft MVP Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, October 24, 2016 10:30 PM
  • I'll add my +1 to that comment hands down!

    Daniel Pineault, 2010-2016
    Microsoft MVP Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, October 24, 2016 10:30 PM
  • No timers on the form.

    Stepping through the code produces the same intermittent results. Sometimes it works, sometimes it doesn't.

    The same thing results on my machine connected remotely to the client's server and on the workstations at the clients office connected locally to the server.

    I guess I figured that by posting in the first sentence that I've been doing this for 20 years that it would be clear that I'm not a beginner. I do appreciate the answers but I've noticed in many forums that there are a great many people that tend to jump in and willy nilly toss around simple programming 101 answers just to get points.... JUST TO GET POINTS. I've seen it over and over and over and it gets tiresome. I loved the usenet groups. No points. Nobody did that. The point systems have caused a rash of that sort of thing. I went to University of Michigan and got my degree in electrical engineering. I've been custom programming for a living for over 20 years (DBase, FoxPro, Access, Delphi, .net, MS SQL Server, MySQL, php). I had a boss once when I first started working for him doing FoxPro (which I'd already done for years) ask me if I'd "SET STEP ON" in a program I was trying to find a bug in. We all got a big kick out of it. It's the very first thing you do. :) I also did state in my very first paragraph that the first thing I did was to paste the query into SSMS to test it.  So asking me that made it pretty clear that someone was just looking for points. I find that endlessly annoying. It's not the way these forums are supposed to work. :/

    • Edited by keithrh Monday, October 24, 2016 10:53 PM
    Monday, October 24, 2016 10:43 PM
  • Ok, all good then. Public forms are challenge!

    And having spent considerable time on UseNet – it was unmoderated in near all cases and I can say that unnecessary chatter was often a problem, and I see MUCH less junk posted here since these forums are moderated. And I don’t think people “post” here for gaining points – or at least I never noticed.

    Ok, back to the issue at hand.

    So no timers, and single stepping can also cause the error.

    And you also suggest the fail occurs in the “ADJ” code block (the else block of code).

    Does the code stop/fail on the rs.Open, or the set of the recordsouce?

    And is the report un-bound, or does it start out with a data source already set? I ask this, since the report could in some cases cause a table lock (assuming the report has a data source before your override it with the recordset assignment). Also, is there an index on the column ScannedDocTypeCode? The reason for this is that a table scan can cause a table lock, but with an index, then a table scan can be prevented and this effects SQL locking of tables).

    To “eliminate” the possible table locking issue, the sql in the SP could include (for testing) the NOLOCK hint. Again, this may not be the issue, but I seen “many” an issues due to “quirky” SQL decisions when it pulls data.

    And you could put a debug.print line to show the record count of the recordset before you assign it to the reports recordsource. That way, when the report fails to render, you at least see in the debug window what the record count was (or if it occurred).

    So adding a NOLOCK hint to the SQL used in the sproc would be a good test.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Tuesday, October 25, 2016 12:18 AM
  • I didn't say it fails IN the "ADJ" block. I said it fails if the variable value is "ADJ". I have no idea where it's failing. You can see all the code in the report. It technically doesn't FAIL. There's no error generated. And it doesn't report "no data" in the no data block.

    When I do this...
        
        rs.Open sSql, cnnCurrProj, adOpenStatic
        Debug.Print rs.RecordCount
        Me.RecordSource = rs.Source

    ...it shows a correct number of rows but the report doesn't display. It just exits out completely. No errors, no warnings, nothing, zip, nada. It neither STOPS nor FAILS anywhere. It finishes all the code in the report. It finishes the code in the Open event and finishes the code in the Close event then exists back to the calling form.

    The report is unbound. The source is set in the Open event as you can see in the code.  I know what you mean I've seen the problem you describe before. When I set sources in code I typically remove sources in the design.

    The table used in the final SELECT in the stored procedure is a # temp table. From my research, NOLOCK seems pretty useless on # temp tables. I tried it anyway and it didn't help. Regardless, if Debug.Print rs.RecordCount renders the correct # of rows but the report doesn't render, I don't see how NOLOCK would accomplish anything.
    Tuesday, October 25, 2016 2:06 AM
  • I miss usenet. I got a lot more done when that was around. Easier to access, no logging in, all in one spot (Outlook Express for me), etc. I got a lot more questions answered a lot more quickly when usenet was still around. I agree about the chatter, but it was a friendlier environment in general (except for the notoriously obnoxious, arrogant Joe Celko). I learned a lot from the people in the usenet groups.
    Tuesday, October 25, 2016 2:12 AM
  • Hi kerith,

    from your earlier posts it looks like you are only complaining regarding forum, interface of the forum, suggestion provided by the community members and so on.

    I want to inform you that all are here to help you and trying to solve the issue. if you think that their posts are not going to help you then simply ignore that post instead of again and again pointing it.

     I would recommend you to use paid support.

    Since the issue is complex, I suggest you contacting Microsoft support to raise an incident so that our engineer could work closely with him to identify the root cause and resolve this issue as soon as possible.
    If the support engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged. 
    Please visit the below link to see the various paid support options that are available to better meet your needs. 

    https://msdn.microsoft.com/subscriptions/bb266240.aspx

    you can give your feedback related to Forum in the link mentioned below.

    Suggestion and Feedback

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 25, 2016 3:13 AM
    Moderator
  • Sorry folks. This has been an incredibly frustrating problem and frustrating to try to explain. I tried hard in my O.P. to be clear about everything but it's often hard to anticipate the types of follow up questions you might get. On the surface it's very simple. There's not really a lot going on and I tried everything my 20 years of experience has to offer. I'm using the same exact strategy in this report that I've used in many others for many years and never encountered this type of thing. I was truly hoping someone would come along who'd seen this sort of thing before and knew it was some weird MS bug with a convenient workaround. I'm not likely to contact MS because I doubt my client will be willing to pay for their time. I'm going to do what I suggested today and use the permanent temp table idea. You know Deepak, the forum issue didn't come up until later on in the thread. I simply commented on the problem that your forums don't nest answers like so many others do (which makes it impossible to know which follow up goes with which) and that turned into a little side conversation. Shouldn't be a big deal. Your forums are not great. They could use some work. Do whatever you want with that information. You should listen to the complaints of long time MS software users instead of reprimanding them. On a third party tool's forum that I use quite often they would have said "Thanks for the feedback about our forum. We'll look into improving it." They'd never scold me for bringing it up.


    • Edited by keithrh Tuesday, October 25, 2016 1:40 PM
    Tuesday, October 25, 2016 1:38 PM
  • Man, you are way off. Just trying to help. If I were getting money instead of points maybe I'd care about accumulating them but I'm not, so I don't. I've been programming for over 20 years too but I humbly accept that sometimes a second pair of eyes illuminate simple details that I might have missed. There are other "basic" things not mentioned here that I would try that would take little effort but as I'm sure you've already tried them and I'll decline the possibility of raising your ire further by stating them.  And for the record, your original post only stated that you'd "run the SQL"...very different than "paste the query into SSMS to test it." Listen to Albert and Daniel.  They're fantastic and more knowledgeable than I'll ever be about the subjects raised in this forum. My apologies for having wasted both our time.  Happily stepping aside now...

    -Bruce

    Tuesday, October 25, 2016 4:17 PM
  • Ok, if the recordcount shows, then it’s not NOLOCK hint that will fix this – but I made both suggests in one post as to shorten another question + response thread on everyone’s part. (it would have helped from the get-go to note you had code test that the reocrdset really was loaded and records are tested but report fails).

    We now grasping for thin air in terms of solutions.

    Perhaps that data has something in it that cases a failure (a value in the report that causes some kind of format error or math error etc.)

    Given that the query completes and we 100% determined the recordset DOES have data and YET the report fails is strange. If this was an access backend, then I would add a blank loop in the recordset that does a .movenext for the whole reocrdset to find a corrupted record (I had that occur in the past).

    You could still try/test that the whole recordset can be iterated but given the data comes from SQL server, then it’s unlikely corrupted data. However, you could try to “narrow” down the number of records in the report even further to the point in which only a few records exist and the report fails (but the challenge here is “intermittent” failures). Perhaps for the “ADJ” dataset it is larger than other criteria(s) that work just fine. (so perhaps during testing keep an eye on the recordcount number WHEN the report fails – this might be the issue). For example, if pictures were in the report - then out of memory could cause the report to fail.

    I can only guess that some data in those records have “bad” characters or something in that given data set is causing the report to fail. Perhaps it just the  total recordcount (so try and make note of failures and record count to note any correlation)

    Given that there is no “common” or even “rare” notes of this issue in the general Access community, then until such time one can narrow down the “criteria” that causes the report to fail, we all still on a wild goose chase as to the problem. I doubt any support can be found to fix or deal with this issue exists at this point in time until such time one can "narrow" down further within the ADJ dataset that causes a failure.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Tuesday, October 25, 2016 9:46 PM