Command Button msgbox only works once (1 Viewer)

Hamish 237

New member
Local time
Today, 13:04
Joined
Jun 19, 2019
Messages
16
Hi Have a small issue with a command button it works fine and gives the desired message if there is no content in the referenced control but the message box changes to a syntax error warning if the button is pressed a second time without data in the control is there and easy code line to reset this warning msgbox

Private Sub OPTOSO_Click()
On Error GoTo Err_OPTOSO_Click

'Open the form List using the data in SOTV text box selection to filter the records


If IsNull([SOTV]) = True Then

MsgBox ("Please Enter Sales order number to search for")

Else

DoCmd.OpenForm "list", acNormal, , "[Sord] = " & [SOTV], acFormEdit


End If

[SOTV].Value = ""

Exit_OPTOSO_Click:
Exit Sub

Err_OPTOSO_Click:
MsgBox Err.Description
Resume Exit_OPTOSO_Click

End Sub

Any help / suggestions would be appreciated
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:04
Joined
Sep 21, 2011
Messages
14,048
Test for "" as well as that is what you set the control to?

Or use
Code:
If NZ(SOTV,"") = ""

HTH
 

Hamish 237

New member
Local time
Today, 13:04
Joined
Jun 19, 2019
Messages
16
Ok I can see that thank you... I didn’t cotton on that it changed when I added the set value command I will try that in the morning
 

Hamish 237

New member
Local time
Today, 13:04
Joined
Jun 19, 2019
Messages
16
[SOLVED] Re: Command Button msgbox only works once

Thanks Gasman NZ() worked a treat
 

Micron

AWF VIP
Local time
Yesterday, 22:04
Joined
Oct 20, 2018
Messages
3,476
maybe this will help going forward - you neglected to control code execution for when the field had no value. While adding a handler for zls is fine, prevention is the preferred solution for what your problem was. For this case, you should have an exit point...

Code:
If IsNull([SOTV]) = True Then
  MsgBox ("Please Enter Sales order number to search for")
  [B]Exit Sub[/B]
Else
  DoCmd.OpenForm "list", acNormal, , "[Sord] = " & [SOTV], acFormEdit
End If
...otherwise what comes after will also run...

[SOTV].Value = ""
 

Hamish 237

New member
Local time
Today, 13:04
Joined
Jun 19, 2019
Messages
16
Thanks Micron, I wanted the code to also clear the control SOTV.Value TO “” so users didn’t have to clear the field on return to the search form which has multiple search criteria they can select from ...the IF NZ (SOTV,””)= “” worked a treat in this case thanks
 

Micron

AWF VIP
Local time
Yesterday, 22:04
Joined
Oct 20, 2018
Messages
3,476
If you want to remove the contents of control, it matters if you choose "" or Null - they are not the same thing. SOTV=Null could suffice. Note that Value is the default property of a textbox, so you don't have to refer to it but it's not wrong either.

Sometimes other methods are more appropriate, such as Undo or Cancel on either the form or a control
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:04
Joined
Sep 21, 2011
Messages
14,048
Fair point, but if it was Null or now "", then you want to exit immediately after the message.?

Thanks Micron, I wanted the code to also clear the control SOTV.Value TO “” so users didn’t have to clear the field on return to the search form which has multiple search criteria they can select from ...the IF NZ (SOTV,””)= “” worked a treat in this case thanks
 

Micron

AWF VIP
Local time
Yesterday, 22:04
Joined
Oct 20, 2018
Messages
3,476
Fair point, but if it was Null or now "", then you want to exit immediately after the message.?
If you're asking me - it depends. In this case I venture to say yes, assuming the intent is to cancel any subsequent actions until the user inputs something in a field that is missing data. That is not always the case
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:04
Joined
Sep 21, 2011
Messages
14,048
@Micron,

I was directing that to the o/p and agreeing with you.

Apologies for the confusion.



If you're asking me - it depends. In this case I venture to say yes, assuming the intent is to cancel any subsequent actions until the user inputs something in a field that is missing data. That is not always the case
 

Hamish 237

New member
Local time
Today, 13:04
Joined
Jun 19, 2019
Messages
16
Thanks guys i’ll Certainly Try the SOTV=Null as well and I’m thinking that could be very useful in future for me ... I have always referred to the control value previously and had my share of hurdles now I think those were likely caused by the “” value
 

Users who are viewing this thread

Top Bottom