Avatar of Laurence Martin
Laurence Martin
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Command Buttons crash in mdb on Access 2010

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
Microsoft Access

Avatar of undefined
Last Comment
Scott McDaniel (EE MVE )

8/22/2022 - Mon
SOLUTION
peter57r

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott McDaniel (EE MVE )

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 Nasr

Try previous comments. If problem persists then attach a sample database demonstrating the issue. Explain what to do to get the error.
Scott McDaniel (EE MVE )

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Laurence Martin

ASKER
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
peter57r

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 Martin

ASKER
Thanks Peter, do you need the Set keyword at the beginning or not?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott McDaniel (EE MVE )

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 Martin

ASKER
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.
peter57r

'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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott McDaniel (EE MVE )

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 Martin

ASKER
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
peter57r

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Scott McDaniel (EE MVE )

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question