Command Buttons crash in mdb on Access 2010

Laurence Martin
Laurence Martin used Ask the Experts™
on
Error prompted by clicking command buttonI have an Access .mdb that is now running in Access 2010.

The problem is that when I create new forms command buttons won't open them.

When I click the button (created using the wizard) I get the attached error.

I have tried to de-bug but this error seems to pop-up before any of the code is run.

I have avoided upgrading the file to accdb because the client doesn't want to fund the extensive testing that would be required.

I have removed the module from the target form, but this made no difference.

Any clues about what is happening and how to get round it would be gratefully received.

LJKM
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
There are many causes for this error.
Could be the regional language settings, could be conflicts with virus checker, embedded Word documents, faulty relationship settings, amongst many more, and of course it could be a genuine message if you have a problem with an unregistered ocx that is being used.
I don't know of any method of pinpointing the cause, other than checking everything.

If you create a northwind database on the problem machine do you get the same problem?  If so , it would suggest a basic conflict with the environment rather than something within the application.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Could also be corruption - try building a new, blank database and moving everything into that database.

Do this in Access 2010 - you can use the older .mdb format when doing this, so you don't have to convert to .accdb.

After doing this, perform maintenance on the db:

1) Compact the db
2) Compile the db - from the VBA Editor click Debug - Compile. Fix any errors that are found. Continue doing this until the menuitem is disabled.
3) Compact again.

Make a BACKUP before doing ANY of this.

You might also try a Decompile if the above doesn't work. Again, make a backup and then build a shortcut like this:

"full path to msaccess.exe" "full path to your db" /decompile

Run the shortcut, and then do the 3 steps above again.

And don't forget about the BACKUP :)
Hamed NasrRetired IT Professional

Commented:
Try previous comments. If problem persists then attach a sample database demonstrating the issue. Explain what to do to get the error.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Note too this could be a missing Reference. If you can show the references used in the db, we might be able to determine if you need to look further into that.
Laurence MartinTraining Director

Author

Commented:
List of references
Thanks everyone.

Here is the list of references.  Any ideas?

Northwind (accdb) seems to work perfectly.

Have copied into another mdb, but I've not had time to debug all the problems.  Those I have fixed have not helped.

btw: why would set recOrders= db.openrecordset("qryname").sql=strSQL throw up an error (invalid property)?  DAO is referenced.

The file is a front-end to an SQL database so I'm not sure I could upload a copy.

I'm back at this client next week, so please don't think I'm ungrateful if I don't do much with this for a while.

Cheers
set recOrders= db.openrecordset("qryname").sql=strSQL

is not a valid command ( except in the sense that it might compare the sql strings in qryname and strsql and return true or false)

If you are trying to open a recordset based on the contents of strsql then you don't need the reference to qryname.
set recOrders= db.openrecordset(strsql)

If you are trying to set the sql IN  tha saved query called "qryname" then you do

currentdb.querydefs("qryname").sql = strsql
Laurence MartinTraining Director

Author

Commented:
Thanks Peter, do you need the Set keyword at the beginning or not?
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Note that newer versions of Access are less forgiving of poor VBA coding than were earlier versions, so statement like you posted earlier will need to be clean up and standardized.
Laurence MartinTraining Director

Author

Commented:
Understood, I will post another question about that statement.  Would that go any way to explaining the original error?

We are not using any ocx files, as far as I know.

The command buttons are created using the wizard, so all the coding should be standard.

It's not just this one.  It seems to be any button that opens a form created since the switch to 2010.
'Set' is required for setting the value of object variables.

Originally we had...

Let <simpleVariable> = <data value>

Set <ObjectVariable> = <an object>

'Let' was never required and so just fell into disuse.
'Set' was and and still is required.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
The command buttons are created using the wizard, so all the coding should be standard.

The code created by the wizards is far from standardized. The wizards don't create that OpenRecordset code to open a form - I assume you or someone else added that after the fact.

If you clean up that code for a single form, and move to a proper code construct, does that single form work?

If it does, then the problem is definitely the code, and it will have to be fixed. Whether the client will pay for the fix would be determined by who actually wrote that code - if you did, it would seem to be on your dime to convert the code to correct syntax.

If it does not, then you have other problems, and using the steps above (i.e. compact/repair, etc) should take you a long way toward resolution.
Laurence MartinTraining Director

Author

Commented:
OK, let's set the record straight :

a: I transposed the line defining a recordset incorrectly - apologies - I will raise a different question about the problem.  It was thrown-up as I was doing the Debug - Compile.

b: the code that is producing the error for this question is:

Private Sub cmdOpenfrm14a_Click()
On Error GoTo Err_cmdOpenfrm14a_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm14amaterial"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenfrm14a_Click:
    Exit Sub

Err_cmdOpenfrm14a_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenfrm14a_Click
   
End Sub

I will proceed with the Compile but if this gives you any clues I'll be grateful.

L
This code is not the cause of the problem, even if it is where the problem is arising.

Does the form frm14amaterial open normally if you open it by double-clicking it in the navigation pane?
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Agreed. The code is not the issue. There's something else going on.

Does this database produce the error if run on a different machine?

Do other databases exhibit odd behavior if run on the same machine (not Northwinds, but one you developed)?

What version of Access are you using to develop? What version of Access are your target machines using?

Have you tried a repair or re-install of Access? Errors like this can sometimes mean that a dependent file has become de-registered.

Are you certain the target enviroments are all up to date re Office and Windows?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial