Code running report despite default set to not (1 Viewer)

[BlueSpruce]

he incorrectly wrote the second argument of MsgBox using the Addition operator, rather than the correct logical disjunction operator.

So you're saying vbYesNo + vbDefaultButton2 is what's causing the problem, and I should've used ampersand to concat, or a double pipe, instead of the plus sign?... Then why did my code run correctly when all who repro'd it did not experience the anomaly?

I do not see any changes in the msgbox constants: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/msgbox-constants

 

[xavier.batlle]

Decompiled, still not solved. Imported all objects to new blank accdb, no luck

The code also works properly for me.
Have you tested to create a new database with only one form with only one button with this code?
Does it work?, If not upload this database.

 

[BlueSpruce]

Have you tested to create a new database with only one form with only one button with this code? ... Does it work?

Yes that worked! So the problem is somewhere else in the full version. I searched all other events for that form and nothing else is telling it to open the report.

 

[tvanstiphout]

You can set a breakpoint in Report_Open event, and then inspect the call stack to see who opened it.

 

[mike60smart]

Which Ac version did you test it on?
I am using 32-bit Office 2010 Professional MSI (Non-ClickToRun version), with Service Pack 2 update, version 14.0.7268.5000



I CR'd the FE and the problem continued, so I copied the FE to a new blank accdb and still have problem. I checked the library references and nothing has changed.
Paranoid me, thought MS might have snuck in a change to sabotage my 2010 to get me to update to a newer supported edition, but I have Windows Updates, Office updates, and Remote Registry disabled, so that's impossible, unless they figured out a way to remote into my box and change things around.

Private Sub cmdPrintReceipt_Click()
Dim response As VbMsgBoxResult
response = MsgBox("Print Customer Receipt?", vbYesNo + vbDefaultButton2)
If response = vbYes Then
DoCmd.OpenReport "rptPrintCustomerReceipt", , , "[ContractNo] = " & Me.ContractNo
End If
End Sub

 

[BlueSpruce]

You can set a breakpoint in Report_Open event, and then inspect the call stack to see who opened it.

VBE's ability for seeing the call stack is sufficient. I checked and it's not being opened anywhere else.

Call Stack says [Debug].[frmContract].cmdPrintReceipt_Click()

 

[cheekybuddha]

So you're saying vbYesNo + vbDefaultButton2 is what's causing the problem, and I should've used ampersand to concat, or a double pipe, instead of the plus sign?...

No he did not say to use ampersand

 

[isladogs]

Perhaps Tom was too subtle OR perhaps not

 

[BlueSpruce]

he incorrectly wrote the second argument of MsgBox using the Addition operator, rather than the correct logical disjunction operator.

vbDefaultButton2 is a valid msgbox argument, so then what's Tom referring to?

No he did not say to use ampersand

 

[Ken Sheridan]

What happens if you drop the variable and use the following?

If MsgBox("Print Customer Receipt?", vbYesNo + vbDefaultButton2) = vbYes Then
    DoCmd.OpenReport "rptPrintCustomerReceipt", WhereCondition:="[ContractNo] = " & Me.ContractNo
End If

 

[BlueSpruce]

What happens if you drop the variable and use the following?

If MsgBox("Print Customer Receipt?", vbYesNo + vbDefaultButton2) = vbYes Then
    DoCmd.OpenReport "rptPrintCustomerReceipt", WhereCondition:="[ContractNo] = " & Me.ContractNo
End If

The variable is not the problem, as my original code works after several members tested it on different Ac versions. It must be some sticky corruption in my project that persists even when doing a CR and importing all objects to a new accdb.

 

[tvanstiphout]

vbDefaultButton2 is a valid msgbox argument, so then what's Tom referring to?

I meant to say that since in the second argument you used +, that indicates you know that vbYesNo and its cousins are integers. It then stands to reason that the return value of MsgBox is integer too, since in the next line you are comparing it to vbYes (an integer).
But + is still incorrect, since vbYesNo and its cousins are bit masks, and therefore they should be Or-ed together.

The use of + or Or does not explain the weird behavior you are seeing in the full version of your app.
You could Application.SaveAsText and inspect it for any signs of corruption.
Or try it on another machine.

 

[The_Doc_Man]

Regarding LOGICAL DISJUNCTION vs ADDITION:

Strictly speaking, using + rather than OR in response = MsgBox("Print Customer Receipt?", vbYesNo + vbDefaultButton2) has no particular effect, since the simple "OR" is the INCLUSIVE version. When I took CompSci in college, we used an older name for those operators. "OR" was called "LOGICAL ADD"; "AND" was called "LOGICAL MULTIPLY". In FORTRAN II days, we learned that using "+" on a bit-mapped Boolean (Logical) was the same as using "OR" on an INTEGER - when the bitmapped patterns don't overlap. Which, in the expression in question, they do not. And yes, if you were paying attention, you would have caught that I reversed the operations in my discussion to emphasize that there was no difference.

Remember also in VBA that BOOLEAN is a typecast of INTEGER, so type coercion hardly has to do anything at all. For vbYesNo you get 4 (0x0004) and for vbDefaultButton2 you get 256 (0x0100). No bits overlap, so "+" and "OR" have no difference in result and the value's type will be INTEGER either through coercion or as the result of typecast. (Granted, if the bit values DID overlap, it WOULD make a difference.)

 

[BlueSpruce]

Regarding LOGICAL DISJUNCTION vs ADDITION:

Strictly speaking, using + rather than OR in response = MsgBox("Print Customer Receipt?", vbYesNo + vbDefaultButton2) has no particular effect, since the simple "OR" is the INCLUSIVE version. When I took CompSci in college, we used an older name for those operators. "OR" was called "LOGICAL ADD"; "AND" was called "LOGICAL MULTIPLY". In FORTRAN II days, we learned that using "+" on a bit-mapped Boolean (Logical) was the same as using "OR" on an INTEGER - when the bitmapped patterns don't overlap. Which, in the expression in question, they do not. And yes, if you were paying attention, you would have caught that I reversed the operations in my discussion to emphasize that there was no difference.

Remember also in VBA that BOOLEAN is a typecast of INTEGER, so type coercion hardly has to do anything at all. For vbYesNo you get 4 (0x0004) and for vbDefaultButton2 you get 256 (0x0100). No bits overlap, so "+" and "OR" have no difference in result and the value's type will be INTEGER either through coercion or as the result of typecast. (Granted, if the bit values DID overlap, it WOULD make a difference.)

Thanks for that thorough explanation. As noted, the issue lies elsewhere. I will continue on my wild goose chase a little longer and if necessary, use workaround logic.

 

[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.

 

[BlueSpruce]

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

There you go! and I've been using the same operand since 2012 and have never encountered any issues by using it. If it works for everyone else, but not for my specific project, then something else is causing the problem.

 

[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.

 

[BlueSpruce]

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?

After backing up a problematic accdb, I CR and close it, then I always create the new accdb separately and import all the objects to it.

1. [and 2.] In a VBA editing page, check Tools>>References to be sure that no library has "Missing" or "Broken" ...

That's one of the first things I checked. No library was missing, broken, unchecked, or out of order in the original and new accdb's.

3. Uninstall and re-install that version of Access.

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

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, ...

?Explain

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.

Again, other projects with same printing code work fine, no need to go to this 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.