Solved Code running report despite default set to not

[Deleted Bruce 182381]

I cannot understand why the code below is running the report when pressing the enter key despite setting the default button to "No" .

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

 

[LarryE]

Try:
Dim response As Variant

 

[Ken Sheridan]

The vbYes constant is an integer data type. Its actual value is 6.

 

[The_Doc_Man]

I cannot understand why the code below is running the report when pressing the enter key despite setting the default button to "No" .

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

View attachment 122273

Is there a chance that you enjoyed (? suffered) a Microsoft update recently. I know I did, on Wednesday of this week. With MS poking around in your libraries, all bets are off.

It is true that your "response" string should undergo "LET coercion" to turn the return value of vbNo (from the MSGBOX) to "7" (the value of vbNo, which is what you should get when you click No). That "=" between the variable and the function guarantees that coercion for assignment statements (a.k.a. LET statements). However, I wonder what happens inside the IF statement. In theory the relational expression should perform LET coercion again since vbYes = 6 and response = "7" - but what I don't understand is why you originally programmed something documented to return an INTEGER and stored it as a string. MSGBOX has returned that INTEGER value since I can remember, and that's since Access 2.0 - a couple of decades ago.

I looked at the VBA Language spec from 2014 (the most recent one I have). The syntax of "IF left-hand-operand relational-operator right-hand-operand" is covered in 5.6.9.5 Relational Operators. For the case where the left operand is a string and the right operand is an INTEGER, the manual says LET coercion will yield INTEGER, so your response variable will get converted back to INTEGER for the comparison.

I think it would be a valid and fairly simple experiment for you to just declare response as an INTEGER and see if that works. It's a one-line edit with a binary result: works/fails.

 

[DHookom]

I never program my If statements on a single line. I would dim response as integer, split the lines, set a breakpoint, and walk the code to confirm the values.

 

[The_Doc_Man]

I no longer have Ac2010 since I replaced my previous machine. It would not be an apples to apples test.

 

[bob fitz]

I have tried the OP's code on a later version of Access, and it works without any issues.
Could corruption of the control or form be the cause of the problem?

 

[mike60smart]

Hi Can you upload a copy of the Db?

 

[bob fitz]

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.

I'm using Office Professional 2019 (32 bit)

 

[tvanstiphout]

Can anyone try my code to repro the problem in one of your test db's?

I do not see your problem with my copy of A2010-32, v14.0.7268.5000
Decompile your app.

 

[isladogs]

The code also works for me in both A365 v2512 and A2010 v14.0.7268.5000
As Tom said, decompile to hopefully fix likely corruption. (EDIT - too late)

Next try reinstalling Office 2010

Even though the code casts string response correctly as integer, I also see no reason why you would have used the wrong datatype when it has clearly been documented as Integer since VBA first appeared in Office 95

 

[tvanstiphout]

> Even though the code casts string response correctly as integer, I also see no reason why you would have used the wrong datatype when it has clearly been documented as Integer since VBA first appeared in Office 95
While we are needling the OP: he clearly knew that, because he incorrectly wrote the second argument of MsgBox using the Addition operator, rather than the correct logical disjunction operator.

 

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

 

[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

 

[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

 

[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

 

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