Solved Code running report despite default set to not

[DaveMD]

Good computer science concepts discussion. @The_Doc_Man explains it well -- addition works in this case only because the bit patterns don't overlap. But, if you mistakenly add the same constant value more than one time, addition would corrupt the bitwise representation but using bitwise OR would work fine. For example:

if you mistakenly added vbYesNo twice, you would get unexpected results:
response = MsgBox("Print Customer Receipt?", vbYesNo + vbDefaultButton2 + vbYesNo)

but if you made the same mistake but used OR, this would still work (you would get your YesNo buttons with No button as the default):
response = MsgBox("Print Customer Receipt?", vbYesNo Or vbDefaultButton2 Or vbYesNo)

With that said, I do use the addition representation for MsgBox'es as it does work fine as long as you don't repeat any values.

 

[DaveMD]

If you haven't already tried Compact & Repair after you decompiled, give that a try.

If that still doesn't work, you could try the MZ-Tools (MS Access add-in). It is a 3rd party add-in (I have no affiliation, just a customer myself) and you can download a trial version. Once installed, right-click as shown below and try the "Clean" option (shown in the attached image).

Be sure to backup your current copy first in case anything goes wrong. After you run "Clean", you may be prompted several times to save your modules and just click OK/Yes. This recently remedied some unusual errors I was getting in an established MS Access front-end.

 

[The_Doc_Man]

Based on the discussion so far, I can only think of two or three things to try to fix this, and one of them is so extreme that I imagine it would not be considered practical.

When this fails in the way described, and considering that
a) Changing data type for response didn't help
b) Compact & Repair didn't help
c) DECOMPILE didn't help
d) Creating a new FE file to import the stuff from the old file failed,

One question: When you copied to the new, empty DB, did you create the empty file separately and then open the old file to EXPORT, or did you create the empty file and from it, opened the old file to IMPORT? I have found that sometimes, when corruption is involved, the difference between a push and a pull is significant. IMPORT provides better results if corruption is prevalent.

This is behaving like a bizarre type of corruption. I'm going to blue-sky solutions for BlueSpruce. In increasing order of severity:

1. In a VBA editing page, check Tools>>References to be sure that no library has "Missing" or "Broken" somewhere on the page. If you find such a thing, remove the check mark. Close and then re-open the DB and proceed to reselect the library - if you can. If this doesn't work, ...

2. In the VBA editing page, check Tools>>References and note the name AND ORDER of each library. Publish that so that we can see if the library providing MSGBOX is in standard order. I have seen some problems with library order of appearance if there is an inadvertent name overlap. If this also goes nowhere, ...

3. Uninstall and re-install that version of Access. If the "Repair" option is available, use it. But if not, or if Office repair doesn't work, or if Office re-installation fails...

4. IF there is a built-in set of diagnostic tools already on the system provided by the vendor, verify that you have no CPU, disk, or memory failures. You probably don't, but as you can tell, we are getting to increased severity. If this also draws a blank, ...

5. Make a full backup of your system. Try to do a "repair" style of installation of Windows if available. If not {shudder} - re-image the machine and rebuild it, software-wise. Told you one of the suggestions would be extreme.

 

[The_Doc_Man]

I have other projects with the same exact printing code and they work fine, so no need to re-install O2010.

Are these other projects on the same machine? If they are, then you are right. But if the error only occurs on one machine,... well, I don't like what appears to be a coincidence. At the macro level, this is a cause-and-effect world. Also, if the same program is run on a different machine, does the error occur in that alternate venue? (Trying to isolate the problem ...)

?Explain

(Regarding diagnostic tools): You COULD have a situation where an app does something that no other app does.

When I was at UNO in grad school, our KI-10 processor had a really great set of DOUBLE instructions. Nobody had problems until our resident theoretical chemist started simulating wave functions of 15-25 molecule organic compounds, to map estimated electron density diagrams as a comparison of theory vs. crystallography - and he repeatedly crashed the O/S. To make the long story short, he was the only person whose code exercised the floating DOUBLE divide instruction, and we had to run extended diagnostics to recognize that we had a faulty DOUBLE floating-point card. Once DEC identified the problem, they sent us a replacement card and we were back in business.

You might remember the Pentium processors, Intel's i80585 chip, that had a faulty math support chip that forced MS to download a hardware abstraction layer to bypass and emulate a faulty FDIV instruction.

In any case, on the odd chance you have one unique computational sequence in the program that is failing, CPU diagnostics would rule that out. A memory sweep would also tell you if you have a faulty memory that just happens to overlap some crucial code segment. Again, the specificity of the problem should make it possible to identify where it is.

 

[LarryE]

Yes

That's the case with this app. I did the Application.SaveAsText that Tom suggested and did not see any corruption. I also examined that forms code with a hex editor, but that was useless. So instead of beating this dead horse I did not use the vbDefaultButton2 constant, inverted my logic to the default Yes button, "DON'T print customer receipt?", and that works ugh.

Maybe some gremlin changed the vbNo constant to vbYes

Private Sub cmdPrintReceipt_Click()
Dim response as String
response = MsgBox("DON'T Print Customer Receipt?", vbYesNo)
If response = vbYes Then Exit Sub
DoCmd.OpenReport "rptPrintCustomerReceipt", , , "[ContractNo] = " & Me.ContractNo
End Sub

This worked for me:

Dim Response As Variant
Response = MsgBox("Run Report", vbYesNo + vbQuestion + vbDefaultButton2)
If Response = 7 Then Exit Sub
MsgBox "Run Report"
Exit Sub

The default is No so if you hit Enter, No (7) is selected and then Exit Sub, otherwise the message box shows "Run Report" and continues to Exit Sub.
I don't use single line If-Then statements but since you did, I did too. Can you run this routine OK?

 

[DHookom]

I have found that an Access VBA function name or other reserved word might have been defined elsewhere in the code but it doesn't seem plausible with vbDefaultButton2.

 

[LarryE]

I tried your code. Anything that involves the use of vbDefaultButton2 does not work for me in this particular app. I find that very strange, so I'm sticking with my default Yes "DON'T Print Receipt" code.

vbDefaultButton2 also has a numerical value of 256. Try that instead and see if that works. I have never heard of this kind of error happening, so I am curious.

 

[xavier.batlle]

The only way I can replicate the OP's issue is if the Public constant vbYes is assigned to 7 somewhere in the code

Const vbYes = 7  ' Text to search.

 

[DaveMD]

Wouldn't Access automatically flag a variable name starting with vb? Using 256 works, so something's up with using vbDefaultButton2.

Try checking the definition of vbDefaultButton2 in the context of your database. You can right-click on vbDefaultButton2 from your MsgBox call then click Definition. It should bring you to to the definition where it will indicate the value of 256. If vbDefaultButton2 is defined locally somewhere in your codebase, it will bring you there. This should make it clear how that constant is defined in your database and if it is overridden somewhere.

.

 

[LarryE]

Wouldn't Access automatically flag a variable name starting with vb? Using 256 works, so something's up with using vbDefaultButton2.

Who knows what ACCESS is doing behind the curtain. Here's a link to the Msgbox function in case you need it again:

MsgBox Function - Microsoft Support

support.microsoft.com

 

[tvanstiphout]

Yes, that is messed up. Wow.
Do you have any funny references that could interfere? Or have references in a non-standard order? The default 4 should always be first and in that same order.

EDIT: It is also suspicious that the hex value does not display. This is my A2010, with Northwind1 loaded:

These are the 4 references, plus a custom one.

 

[The_Doc_Man]

Seeing that the definition of vbDefaultButton2 is wrong means you have a conflicting definition. When you go to the Project Explorer in the VBA screen, you should be able to search for where it is defined. If I recall that feature correctly, you will get a list of places where the definition appears, specifically from the library files. But then you can ALSO do a search of the project to see where vbDefaultButton2 appears - and the question is whether it ever appears to the left of an equals sign in a LET statement (or equivalent). There is also the question of whether it ever appears as an argument in a function / sub where it is used ByRef AND that argument is read/write.

This would SURELY explain the bizarre behavior but still doesn't explain how this erroneous definition came about.

 

[tvanstiphout]

When I defined a public const vbDefaultButton2 as integer = 6, in the object browser it appeared without its hex value.
I gotta believe the constant is defined more than once.
Also as evidenced by the absence of the problem in a new empty database.
I hope you have good tools to do a deep search.

 

[tvanstiphout]

> I thought it being a vba reserved word with a fixed constant that you cannot define a variable with that same name and assign it a different value?
We have to use precise language. I did not define a variable, but a constant.
That constant is in a different library (app_name) than the official one (VBA).
Just like the Recordset object is in two libraries (DAO, ADODB).

If the code is not explicitly stating the library, then which one wins probably depends on some complicated logic. I would not count on that.

Of course we understand you did not define such constant on purpose. Yet we see the value with our own eyes. SOMETHING fishy must be up.

 

[tvanstiphout]

With "good tools" I do not mean Ctrl+F in the VBA window, but ones like this: https://github.com/strive4peace/Analyzer and https://accessdevtools.com/ (although that site seems to be down atm).

 

[The_Doc_Man]

I thought it being a vba reserved word with a fixed constant

Things defined via external libraries are not usually reserved words. I'm running Ofc2021, and when I check, it is defined from the VBA library - once and once only. It SHOULD be a fixed constant, I agree, but it is possible for identifiers to be overloaded.

However, this thought just popped into my head. Go into that app, opened for development. Uncheck the VBA library. DO NOT COMPILE. Just save and close. Now open the app and re-select and check that library. Then decompile. Then manually compile. See if that makes a difference.

 

[arnelgp]

maybe your db apps get Stoned

 

[isladogs]

The VBA library cannot be removed

 

[isladogs]

True but not relevant in this case as the constants are part of the VBA library.

 

[Minty]

What if you declare it separately as the correct value e.g.

Public Const vbDefaultButton2 = 256

Then compile, compact and repair, and check it's value again?
Then remove the declaration and see if the correct value sticks?