[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

DCount in subform crashes Access 2003

I have some text fields in a subform that need to show a few counts - it works great in Access 2007, but crashes Access 2003...here is what I have in the control source for one of the text boxes:

=DCount("[ID]","Note","[ContactID] = Forms![Customer Edit]![Contact List subform].Form![ContactID]")

The problem is with Forms![Customer Edit]![Contact List subform].Form![ContactID], because if I change it to an integer the count processes as it should.  Can anyone tell me what should be there so this function will operate in all versions of Access?

Thanks!
0
aesdtt
Asked:
aesdtt
  • 10
  • 7
  • 5
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try this change:

=DCount("[ID]","Note","[ContactID] = " & CLng(Forms![Customer Edit]![Contact List subform].Form![ContactID]))

mx
0
 
GrahamMandenoCommented:
Yes, this should operate in all versions, but the DBEngine might be baulking at the complexity of the reference.
Is this textbox actually on the referenced form (Contact List Subform)?  If so, try this:
=DCount( "[ID]", "Note", "[ContactID] = " & [ContactID] )
--
Graham
0
 
aesdttAuthor Commented:
Thanks for the suggestions, but both also cause Access 2003 to crash...one thing to note, though: if I open the "Contact List Subform" by itself using GM's suggestion, it works; however, when it opens as a subform, crash city.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What do you mean by 'crash' ?  Error message? Access closes?

mx
0
 
aesdttAuthor Commented:
Sorry, I should have mentioned that in the original post...here is the error:

"Microsoft Office Access has encountered a problem and needs to close.
We are sorry for the inconvenience."

Then it gives me the option to "Repair my open database and restart Microsoft Office Access"

Details of the error are:

AppName: msaccess.exe      AppVer: 11.0.8166.0     AppStamp:46437912
ModName: oleaut32.dll      ModVer: 5.1.2600.5512      ModStamp:4802a112
fDebug: 0       Offset: 000048f9

Also, attached is the crash dump if you can do anything with it (I changed the extension from CVR to TXT so I could upload it)...and sending the error report to MS does not offer any more information.
13920515.txt
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Nasty.  Well, I know there are issues when converting back from A2007 to A2003.  wow!  

There is really nothing wrong with the original code ... should work in A2003.  Not much help I know, but ...?

mx
0
 
GrahamMandenoCommented:
Sounds like your database (or at least part of it) is corrupted.
Try going through the steps here to resurrect it...
http://allenbrowne.com/ser-47.html
Try the compact/decompile/compact method first, as this is less hassle.  It's listed under the heading:
Symptom: "An error occurred while loading Form_FormName"
--
Graham
0
 
aesdttAuthor Commented:
GM: tried that, and while it did reduce the size of my database (frontend - I should mention that my database is split, but don't think it affects this error), the crash problem persists.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

NOTE:  If you have an A2007 ACCDB, please convert to A2003 MDB.
mx
0
 
GrahamMandenoCommented:
It sounds like your main form is badly enough corrupted so as not to be recoverable by a compact/decompile.  It appears that the subform is OK though, so that is good news.
By all means attach your database as MX suggests, but if you wish to learn how to resolve this yourself then the next step is to try to rebuild the form...
If the form is not too complex, it may be easiest to recreate it from scratch.  If that would be a major job, then the next step is the procedure entitled "Symptom: Cannot open a form or report" on Allen Browne's page.
First create a new database (remember to turn the AutoCorrupt option off) and import all the objects EXCEPT the broken main form.  Then save a backup copy of your new database and try importing the broken form.  If it now works, excellent, otherwise try the next step.
Delete your new database and revert to the backup copy created after the initial import of the other objects.  Now open a copy of the original database and use SaveAsText to export the broken form to a text file (steps 1-4 under "SaveAsText / LoadFromText" on Allen's page).  Now open the new database that you restored from backup and use LoadFromText to import the form from the saved text file (step 7).
 When (if!) you get it all working, finish off with a compact/decompile/compact/compile to clean everything up.
Good luck!  Please report back with your progress and the outcome.
--
Graham
0
 
aesdttAuthor Commented:
OK, I was about to attach the database - I stripped it down to only the components involved with this issue, and the problem went away...I'm going to try to figure out exactly what fixed it, and I'll report back.
0
 
aesdttAuthor Commented:
OK, I was able to pin down the problem - apparently, setting the control source for a few text fields in the main form's OnLoad event was causing some kind of problem...the code was a few lines similar to this:

Text1.ControlSource = ""

Once I took those lines out, the problem went away...go figure.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Cool.
0
 
GrahamMandenoCommented:
It would have been some corrupt code around those lines.  Deleting the lines also deleted the corruption.
Probably you will find that if you put the lines back again, everything will be OK.
--
Graham
0
 
aesdttAuthor Commented:
OK, I have an update...apparently setting any fields on the parent form causes this problem...

I have a macro for creating new records of the parent form, and when I try to set a value on the form in the macro, I get the same error.  The macro consists of only two lines:

OpenForm
SetValue  (Item: [ID], Expression: =DMax("[ID]","Customer")+1)

...so it appears that Access does not like me setting any values automatically (either in VBA or a macro) as long as the DCount exists in the subform...
0
 
GrahamMandenoCommented:
It still sounds like corruption to me.  Try this:
  1. Delete the [ID] textbox on the main form.
  2. Delete the SetValue action from the macro
  3. Close and save the form
  4. Compact/decompile/compact the database
  5. Recreate the textbox
  6. Set the DefaultValue of the textbox to this:
    Nz( DMax( "ID", "Customer" ), 0 ) + 1
--
Graham
0
 
aesdttAuthor Commented:
Hey guys, sorry it's been so long, I have been caught up with other things at work...

GM: I tried your instructions, but Access still crashes...

I think at this point it may be better to just work around this bug than try to resolve it - that is, unless anyone else has a suggestion :)
0
 
GrahamMandenoCommented:
Well, "working around the bug" is what we've been trying to help you to do. :-)
If your form is corrupted, then the only thing you can do is follow the steps I gave you in http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_25631613.html?cid=1572#a29403556 and attempt to recover it.
--
Graham
0
 
aesdttAuthor Commented:
I guess I shouldn't have said "work around", but rather "avoid", or better yet "submit to the ultimate, painful inevitability of"...

Anyway, I tried importing the forms into a newly created database, and I tried saving the forms to text files and importing them that way, but Access is still crashing...
0
 
GrahamMandenoCommented:
Is the form that is crashing terribly complex?  Would it be feasible to import everything *except* that form (you should have done this and saved a backup anyway) and then recreate the form from scratch?
Another option is to save the form code separately from the other form design:
  1. Open the form's class module, select all the code (Ctrl-A) and cut (Ctrl-X)
  2. Open a new text file in Notepad and paste the code.  You can do a visual check at this time for any obvious corruption in the code
  3. Save the text file.
  4. Change the form's HasModule property to "No" and save the form.
  5. Export the form to a second text file using SaveAsText
  6. Open the saved text file and find any section that starts PrtMip = Begin.  It will look like this:

        PrtMip = Begin
            0x8905000089050000890500008905000000000000492e0000b119000001000000 ,
            0x010000006801000000000000a10700000100000001000000
        End

    Delete the entire section, from PrtMip to End, inclusive.
  7. Repeat this for any PrtDevMode or PrtDevNames sections.
  8. Save the text file as a new name.
  9. Open your new database containing all the other objects.
  10. Import the modified textfile (with the removed Prt*** sections) using LoadFromText.
  11. Open the form's class module and paste the code saved at step 3.
  12. Check each control that has an event procedure to ensure that its associated event property is [Event Procedure]
  13. Save the form, cross all your fingers, and try it.
Good luck!
--
Graham
0
 
aesdttAuthor Commented:
Graham, thanks for all your suggestions, but this one too still crashes Access...at this point I think I'm just going to load the form with the control source for those text boxes as unbounded, and then just have a button to fill in the values if desired...this seems to keep Access happy.

The form is pretty complicated (many pages/tabs with subforms, and lots of controls), and I think enough time has been spent on this problem, so I don't want to rebuild, although that would probably get rid of the issue - I created a database with new forms that did the same thing, and no crash, so I think you are right about some kind of corruption.
0
 
aesdttAuthor Commented:
accepted answer is for rebuilding the form only
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 10
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now