Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Hangs (2000)

    I just can

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

    Re: Access Hangs (2000)

    The "You can't perform this action now" message may be as simple as trying to save a record that isn't dirty. I'd hazard a guess that somehow you are getting into a loop, probably cause by an error handler in your code. Can you show us the code you have in that command button to preview the report?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Hangs (2000)

    I'm confused. I thought you said the subform was unbound. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Do you mean it's unlinked to the parent form? It sounds like you may have some conflicting events in there somewhere. Can you post the code behind the command button that previews the report and also the code that changes the recordsource ... not just a line but the whole event procedure?

    Edited by Charlotte slightly later

    I just tried it on my machine, and I don't have any problem changing the recordsource of a subform after clicking a button to preview a report. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> It has to be something specific to what you're doing.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Hangs (2000)

    Thanks for helping Charlottle and Mark!

    Yes, I did mean unlinked to the parent form not unbound. Sorry.

    The code behind the command button has no error handling on it and is simply:

    Private Sub cmdPreview_Click()
    DoCmd.OpenReport "Report1", acViewPreview
    End Sub

    The code to change the recordsource is this. I've simplified it over and over again to try to find the problem. This is all that's left of it, and yet the same problem happens to me:

    Private Sub ChangeRecordsource()
    On Error GoTo Handler

    Dim strSQL As String

    strSQL = "SELECT qryGRLedgerRecv.txtRanchID" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.txtDescription" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.dtmDate" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.txtTrxType" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.intGLCode" & vbCrLf
    strSQL = strSQL & " , Sum(nz([intQtyBins],0)) AS curUnitPrice" & vbCrLf
    strSQL = strSQL & " , [intUnits]-[curUnitPrice] AS curAmount" & vbCrLf
    strSQL = strSQL & " , 0 AS perCollected" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.curAvailable" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.blnLedger" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.intUnits" & vbCrLf
    strSQL = strSQL & " , Sum(tblPAPackoutTkts.intQtyBins) AS BinsPacked" & vbCrLf
    strSQL = strSQL & " FROM qryGRLedgerRecv " & vbCrLf
    strSQL = strSQL & " LEFT JOIN tblPAPackoutTkts " & vbCrLf
    strSQL = strSQL & " ON (qryGRLedgerRecv.txtVariety = tblPAPackoutTkts.txtVariety) " & vbCrLf
    strSQL = strSQL & " AND (qryGRLedgerRecv.txtRanchID = tblPAPackoutTkts.txtRanchID) " & vbCrLf
    strSQL = strSQL & " AND (qryGRLedgerRecv.txtFromGrower = tblPAPackoutTkts.intGrowerID)" & vbCrLf
    strSQL = strSQL & " GROUP BY qryGRLedgerRecv.txtRanchID" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.txtDescription" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.dtmDate" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.txtTrxType" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.intGLCode" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.blnLedger" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.intUnits" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.perCollected" & vbCrLf
    strSQL = strSQL & " , qryGRLedgerRecv.curAvailable;"

    Me.RecordSource = strSQL
    Me.Requery
    DoEvents

    Exit_Sub:
    Exit Sub

    Handler:
    Select Case Err
    Case Else
    ' Show the error number and description
    UnexpectedError Me.Name, "ShowFormat", "Subroutine", Err.Number, Err.Description
    Resume Exit_Sub
    End Select
    End Sub

    The recordsource part of the problem works just fine on the report. The hang ALWAYS happens when I try to preview the report. But how can I go wrong with a simple DoCmd.OpenReport "Report1", acViewPreview?

    Again, thanks for your help guys!
    Sam

  5. #5
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Hangs (2000)

    One more thing I might add:

    To really keep things simple, "Report1" is a blank report. No recordsource. No controls. Nothing. It's completely empty.

    Go figure, eh? [img]/forums/images/smilies/smile.gif[/img]
    Sam

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Hangs (2000)

    I'm sorry but the SQL baffles me. Since I don't see a WHERE clause in there (or HAVING, since this is a groupby query), I don't see why you're changing the recordsource at all. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Why don't you just requery the subform instead of changing its recordsource?

    You've got nested queries in there, and it's impossible to tell what's going on inside them, so troubleshooting this pretty much has to be hands on. Build another subform if necessary, but start with a SQL string based on just one of the queries. forget the join and the groupby for now, and just see whether you can get down to the point where it works as expected. Then you can start adding stuff back.

    I don't see why you've got all those vbCrLfs in your code. You don't need them for anything and they clutter up the code. You might also want to alias your tables/queries in the SQL since it makes it much easier to read when you've said "FROM MyVeryLongQueryOrTableName as T" because then the fields list can be "SELECT T.Field1, T.Field2 ..." etc.

    You don't need the Me.Requery in there since setting the recordsource will automatically requery the object. You only need one or the other, not both.

    Did you include DoEvents for a reason or was it grabbing at straws? It's mostly harmless (like Earth, for the Douglass Adams fans out there <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>) but it can also bite you, in spite of how useful it looks. I've rarely found a need for it over the years I've been programming Access, so you might want to try taking it out to see if it matters.
    Charlotte

  7. #7
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Hangs (2000)

    Charlotte,

    Thanks again for your time.

    I'll try to answer a few of your questions and concerns with my code:

    The code I sent was VERY stripped down and simplified, and yet it should be all that's needed to solve the problem. It's part of a MUCH larger picture. I'm focusing on that small part of code because that's where the error is happening. I've done away with every other aspect of the project.

    In hindsight, I can see where it's confusing. I should have explained more. Why am I changing recordsources? Well, eventually the SQl statement will be changed based on different criteria set by the user. Right now, however, I'm finding that the error happens when I change the recordsource. So I've focused in on the code that changes the recordsource and no more.

    Good point on using aliases for those long query names. Thanks. All the vbCrLfs are there to help read my SQl statements. Years ago I became frustrated with trying to edit/debug SQl code in the VBA window. So now I format it all that way to make it easier to read. It's become quite a timesaver. Other than that, however, it is quite useless, I'd agree. :-) I found a simple utility in Scott Barker's "Access 2000 Power Programming" book that easily converts SQl code from a query to the easy to read VBA I posted. Sure, I could copy it all into the design view of a query to use the query design grid, but that can be a pain too. I suppose it's all a matter of prefrence.

    Yes, the Me.Requery and DoEvents both were me "grasping at straws." It seems to me like that's where the problem is. Like the code hasn't completly run. So I was wanting to force this with those two lines of code. Originally these lines weren't a part of the code, and the error still happened.

    As for the SQl statement itself, there is no need for a WHERE/HAVING clause because that's all taken care of in the nested queries. (And again, that SQl statement will be changed based on user criteria.)

    I'll just keep dissecting this thing further and further. If I solve it I'll post back.
    Again, thanks for the time.
    Sam

Posting Permissions

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