Link to home
Start Free TrialLog in
Avatar of Laurence Martin
Laurence MartinFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Command Buttons crash in mdb on Access 2010

User generated imageI 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.

Avatar of peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 :)
Try previous comments. If problem persists then attach a sample database demonstrating the issue. Explain what to do to get the error.
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.
Avatar of Laurence Martin


User generated image
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.

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
Thanks Peter, do you need the Set keyword at the beginning or not?
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.
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.
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.
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 Sub

    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.

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?
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial