Solved Code running report despite default set to not (3 Viewers)

[BlueSpruce]

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

 

[BlueSpruce]

Try:
Dim response As Variant

No difference, it still runs the report. I checked all other form events to see if it was firing the report, but did not find anything

 

[Ken Sheridan]

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

 

[BlueSpruce]

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

VBA implicitly converts Integer values when cast to Strings or Variants, so that's not the problem. I have used that code for years with no issues, and now it suddenly doesn't work. Looks like the Gremlins are at it again

 

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

 

[BlueSpruce]

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.

Nope, I'm using 2010.

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.

Correct. I added a debug.print response after the If statement and got 7. I also did ?response in immediate and also got 7.
The implicit conversion from String to Integer, and vice versa, is properly doing its thing.
I've been using my code "as is" for over a decade with no issues, and now suddenly it stopped working, and A2010 has not received any updates since MS EOL'd it.

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

 

[The_Doc_Man]

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

 

[BlueSpruce]

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

I keep 2010, 2007, 2003, and 97 for portability reasons. There are many users still running legacy apps.

I'm curious to know if the problem is also happening in newer Access versions.

If it is, then I have a nightmare on my hands

 

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

 

[BlueSpruce]

I have tried the OP's code on a later version of Access, and it works without any issues.

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

Could corruption of the control or form be the cause of the problem?

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.

 

[mike60smart]

Hi Can you upload a copy of the Db?

 

[BlueSpruce]

Hi Can you upload a copy of the Db?

I cannot, it's proprietary.

 

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

 

[BlueSpruce]

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

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

 

[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

 

[BlueSpruce]

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

Decompile didn't fix it.
Since vba is not a strong typed language, guess I developed a habit of using strings with msgbox responses, and it's always worked, until now.
I declared response as an Integer and Variant, and it still behaves the same.

 

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