Save Record on A Form Question

[McArthurGDM]

So,



I have a form that takes in an input from the user and a code on the back end to test whether or not they click a button to confirm that the change has been made. This way, they can't just type in a new value and it changes automatically without them confirming with a button click and msg box.

The code is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)

DoCmd.SetWarnings False


     If Not blnSave Then
        Cancel = True
        Me.Undo
     End If
     
DoCmd.SetWarnings True


End Sub

Then, I have another Sub

Private Sub UpdateValue_Click()


    blnSave = MsgBox("Are you sure you want to save this record?", vbQuestion + vbYesNo, "Save Confirmation")
          
End Sub

I need to make it so that if the person clicks "YES" that another series of things happen. I tried,

Private Sub UpdateValue_Click()


    blnSave = MsgBox("Are you sure you want to save this record?", vbQuestion + vbYesNo, "Save Confirmation")
    
    If blnSave = vbYes Then
        whatever
    End If
          
End Sub

But this dosen't work. Any advice?

[ssanfu]

What do you mean "this doesn't work"? Any error messages, the code doesn't execute, .....????

Are the controls that are edited bound or unbound?

In the code for the before update event, you have a variable "blnSave". How/where is it set to TRUE or FALSE?

[McArthurGDM]

Sorry,

No errors. The code does execute. However, the

If blnSave = vbYes Then
whatever
End If

part does not work at all. I need it so that, given that the person clicks yes, that part executes.

The controls on the form are bound to a query, btw.

[ssanfu]

You didn't say, but from the prefix, I would think you have a global variable "blnSave" that is a boolean?

The message box returns a number: 6 = vbYes and 7 = vbNo. If you assign any number other than a zero to a boolean variable, the the variable will ALWAYS be true.

In Boolean terms, 0 (zero) is False, any other value is True (-1, 6, 7, 1000).

Since vbYes = 6 and blnSave can only be 0 (False) or Not 0 (True), this statement (below) will always be true:

    If blnSave = vbYes Then

Try this code:

Private Sub UpdateValue_Click()
    Dim Response
    
    'I added this because I don't have a global variable
    Dim blnSave As Boolean

    Response = MsgBox("Are you sure you want to save this record?", vbQuestion + vbYesNo, "Save Confirmation")

    If Response = vbYes Then
        blnSave = True
    Else
        blnSave = False
    End If


    MsgBox blnSave
    
End Sub

[McArthurGDM]

Weird, I tried your new code and whether or not I click "Yes" or "No", the msg box appears with False.

Sorry, yes, blnSave is a Boolean variable.

[ssanfu]

Since you have a global variable, did you comment out the line in my code "Dim blnSave As Boolean"? Should be only one variable "blnSave".

Did you single step through the code to see the variable values?

[McArthurGDM]

I'm sorry, I feel like I did a terrible job explaining my problem. This code was something I found online, and it works, but doesn't let me run that random code I was talking about. I'll try to explain it better and better illustrate what I have - if that's helpful.

Private blnSave As Boolean

Private Sub Form_BeforeUpdate(Cancel As Integer)

DoCmd.SetWarnings False

If Not blnSave Then
Cancel = True
Me.Undo
End If

DoCmd.SetWarnings True

End Sub

Private Sub UpdateValue_Click()

Dim Response

blnSave = MsgBox("Are you sure you want to save this record?", vbQuestion + vbYesNo, "Save Confirmation")

If Response = vbYes Then
blnSave = True

*random code here

Else

blnSave = False

End If

MsgBox blnSave

End Sub

This is what I have for code. My form looks like this:



Essentially, I didn't want the person to be able to edit/change a value unless they clicked "Update Value". However, as we know, Access will automatically change the value if you click a button or not. Also, I need the click to do some other things (irrelevant in this case).

That code blocks it so that they can't save unless they click that button, but I can't get it so that if someone were to click "Yes", then some code runs. With this code here - the values that show up whether I click "Yes" or "No" is False.

[ssanfu]

You missed one thing in the code:

This line

blnSave = MsgBox("Are you sure you want to save this record?", vbQuestion + vbYesNo, "Save Confirmation")

should be

Response = MsgBox("Are you sure you want to save this record?", vbQuestion + vbYesNo, "Save Confirmation")

then you have

If Response = vbYes Then
  blnSave = True

*random code here

Else

  blnSave = False

End If

Remember, the message box will return vbYes (6) or vbNo (7). the message box must set Response to a value, THEN use the IF statement to assign a value to blnSave.


This might be one of those times where it might be easier to use an unbound form and you write code to save any changes. I usually don't recommend using unbound forms because it is a lot of work to write all of the code to do everything necessary that Access handles with bound forms, BUT ...... in this case, an unbound form might be easier.

[McArthurGDM]

Thank you so much!!

It worked. I wish I could repay the favor in kind but my gratitude is all I have.

[ssanfu]

You're welcome.
Good luck with your project...