Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    subform killing objects

    In A2K, I have a form with fields that can be filled in. There is a 'Find' button that takes the entries in the fields and builds an SQL statement. Then it opens a workspace, database, and DAO recordset objects, sets the recordset to the SQL statement, opens a subform in datasheet view, and starts a transaction. [What is happening is that the operator can select several records for 'processing' by clicking a checkbox; a 'Save' will process all selected records at the same time.]

    The problem is, when I click a checkbox on a record in the subform, the workspace, database, and recordset objects are being destroyed.

    Any ideas or suggestions?

    TIA
    Donald

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

    Re: subform killing objects

    Do you really mean a subform or are you talking about a popup form? In either case, you can't interrupt a transaction and you can't make a transaction cross forms or routines. It has to start and end in the same routine, form, workspace, etc. And once a transaction is started, you have to complete it or roll it back. You can't relinquish control back to the interface, which sounds like what you're trying to do.

    Maybe if you post the code you're using someone can suggest an alternative.
    Charlotte

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

    Re: subform killing objects

    This is not a popup form. It is a subform in datasheet view as a control on another form. The purpose of the transaction is to "collect check marks" and then post all of them at once.

    In order for you to see it, I would need you to see the form too. How can I send you a form and code? I could strip everything out of the mdb except the two forms.

    Donald

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

    Re: subform killing objects

    If you can zip it down to no more than 100Kb, you can post it as an attachment.

    A transaction doesn't collect anything, although it is certainly capable of looping through records in a recordset and processing them sequentially, which sounds like what you want to do. However, I don't really see what the subform has to do with it. If you need to process the records, use the recordset that populates the subform. If you're waiting for the user to do something in the subform before completing the transaction, then the simplest way is to not start the transaction until they click a Save button.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform killing objects

    I deleted everything except the form and subform, compacted and zipped -- it's still 1.5MB.

    Any suggestions?

    Thanks,
    Donald

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

    Re: subform killing objects

    Hmm. Try doing a decompile on the stripped down mdb, then, without recompiling, run a compact and repair. That usually sheds a lot of bloat. It comes back of course, but it's handy for zipping it down further. That probably won't get it down to 100Kb, but it might get it down to a size you could email directly to me. I use a dialup connection, so I don't appreciate large attachments. You can use the email address in my profile. Once I see what it is that you're doing, we can continue the discussion here so other loungers can benefit from it.

    If you aren't familiar with the "undocumented" decompile switch, the easiest way to use it is, with the Windows Explorer window open, to click on the Windows Start button and select run. Then drag the Access executable onto the run dialog. You'll see it take up residence in the textbox. Then go to the folder your database is in and drag it onto the same dialog. It will go in after the Msacces.exe and then all you have to add is a space and then the switch, /decompile (no quotes around the command line argument, please). When you click on OK, it will open the database and decompile it. Then just run the compact and repair from the menu without closing the database first.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform killing objects

    Well, as I continue to work on this, the nature of the failure is shifting. Now it manifests like this:

    I can start, commit, and rollback changes seemingly endlessly as long as I don't close the form and go back to my switchboard (my form, not an Access Switchboard). If I restart the form, I will get a failure either on the first Save or the second Find.

    I am explicitly closing the workspace, database, querydef, and recordset objects and setting them to nothing after every commit or rollback.

    The nature of the failure also varies as I:
    - restart my program
    - close and reopen Access, then start my program
    - reboot the machine, then start my program

    I went through something like this several months ago and I *thought* that explicitly closing and "nothinging" everything cleared it.

    Any ideas or suggestions? It would be way too big to ship you enough to run it yourself because this is an FE/BE system and the form in question accesses two mdbs besides the one it resides in.
    Donald

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

    Re: subform killing objects

    Do you have some OTHER objects that are being created and opened without being destroyed? Querydefs and Recordsets are two that come to mind as common culprits. Usually, though, those manifest themselves in being unable to close Access completely.

    It sounds like your objects may not be properly destroyed when you close the form. Are they local to a routine or module level variables? If module level, you can always put some code into the form's OnClose event to destroy any lingering object variables. I usually do this just as insurance, putting an On Error Resume Next ahead of those lines to avoid error messages because the object has already been destroyed. If they're local, they should go out of scope by themselves if they weren't specifically set to nothing in the routine, but I wouldn't take that for granted. When the save fails, what particular error message are you getting now?
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform killing objects

    All of this code is in the form module. It is my understanding that the only objects I need to destroy are ones that require a "Set" statement. The other variables in the module are booleans, strings, and integers. I did miss setting a querydef = nothing, but even when I got that in I'm still having the problem.

    I'm working on the same line of thought: something isn't properly destroyed. The objects requiring '=nothing' are module level. I added another set of '=nothing's in the form close event, but it didn't help.

    I have put in extensive Debug.Print statements and have located the point where the problem arises, but I don't understand why. I am going to attach some code to this post and here is a sequence to follow.

    The form loads.
    cmdFind_Click
    click a field on the subform which fires subFormDirty
    cmdExit_Click
    MsgBox = Yes

    At this point the form will disappear (theoretically completely destroyed) and on the switchboard, immediately restart the form. Then click

    cmdFind_Click
    click a field on the subform which fires subFormDirty
    cmdSave_Click
    Msgbox = Yes

    At this point I get the following error:

    "Run-Time error '91':
    Object variable or With block variable not set"

    I know that the problem occurs when I click on the subform the second time. As the cmdFind_Click ends, all the workspace, database, querydef etc are ok, as is blnInTrans. However, on entry to the subformDirty event they are all gone and blnInTrans is false.

    I'm stumped! I'll let you look at this for a bit and ask questions.

    Thanks for all your help.
    Donald
    Attached Files Attached Files

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

    Re: subform killing objects

    This
    <hr>
    wk.BeginTrans
    blnInTrans = vbTrue
    txtParkCursor.SetFocus
    cmdFind.Enabled = False
    cmdCancel.Enabled = True

    cmdFind_Click_Exit:
    On Error GoTo 0

    Exit Sub<hr>
    shouldn't work because you're trying to start a transaction in one routine and commit it in another. I suspect you're leaving some dangling objects out there by trying to do this that way.
    Charlotte

  11. #11
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform killing objects

    Are you telling me that the fact that it work at all was a fluke?

    How can you tell what is loaded behind the scenes at any point in time?

    Donald

  12. #12
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform killing objects

    On doing some further research, I repectfully question whether you are correct that my code shouldn't work. I direct your attention to Litwin/Getz/Gilbert "Access 2000 Developer's Handbook", Volume 1, Chapter 6, 'Subforms and Transactions' on page 578. The transaction is started; changes are made to several records in the subform; commit/rollback occurs in the main form unload event. This is approximately the operation I am trying to implement, although I want the operator to indicate whether to commit or rollback. I have tried to emulate their code, but something is haywire.

    Donald

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

    Re: subform killing objects

    This is a new feature in Access 2000. I hadn't used it yet, so thanks for pointing that out. However, they aren't exaggerating when they say it's tricky code, as you're discovering. You'll notice that their form has a Cancel Changes button. Is that what you're trying to do as well? Did you test the code in their sample to see that it works properly under those circumstances?
    Charlotte

  14. #14
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform killing objects

    I haven't exercised their code myself -- I tried to apply it to my situation.

    The weird thing is that as long as I enter and stay in the form, I can execute a seemingly endless number of changes, saves, and cancels in any order. But if I close the form and re-enter it, without completely shutting down the app, I will get the failure. I have tried to be sure and explicitly close *everything* after every 'Find', but the form exit does something weird.

    In all the work I have done with this technique, I have also experienced different behavior the first time the code is executed after rebooting the machine. That is, I can get to a point where I can repeat the problem, but sometimes I have to go through an "unrepeatable" one to get to that point. It is all very weird -- and very frustrating.

    The people I am working for want this app yesterday (with this subform/transaction behavior) and I have been stuck at this point literally for weeks. Do you have any suggestions of who else I could talk to? I am a member of a couple other listgroups, but none of them seem to know *anything* about transaction processing.

    Thanks,
    Donald

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

    Re: subform killing objects

    I don't think it's the transactions, per se. I think you're somehow not closing down the workspace or recordset object. The only thing I can suggest is to block comment out all of the code except what begins and ends the transaction and see if everything works that far. Then uncomment the next level down, entering and exiting the subform, etc. Then keep working down until you find the level that cause the problem. It's a timeworn but useful approach to troubleshooting a complex problem.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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