Before Update Code

[lmcc007]

This code will undo my changes after displaying the message and then close the form, but all I want it to do is display the message and set the focus to the empty field. Below is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Trim(cboTypeofAddress.Value & "") = "" Then
        MsgBox "You must provide data for Type of Address.", _
            vbOKOnly, "ETA - Required Field"
        Cancel = True
    End If
End Sub

Should I use If IsNull instead and put this code on the OK button?

 

[John Big Booty]

Try removing the line Cancel = True or alternatively simply put an apostrophe (') at the start of that line.

 

[dbDamo]

I would use:-

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Len(Nz(Me.cboTypeofAddress,"")) = 0 Then
        MsgBox "You must provide data for Type of Address.", _
            vbOKOnly, "ETA - Required Field"
        Me.cboTypeofAddress.SetFocus       
        Cancel = True
        Exit Sub
    End If
End Sub

 

[lmcc007]

Try removing the line Cancel = True or alternatively simply put an apostrophe (') at the start of that line.

It still closes. Thanks.

 

[lmcc007]

I would use:-

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Len(Nz(Me.cboTypeofAddress,"")) = 0 Then
        MsgBox "You must provide data for Type of Address.", _
            vbOKOnly, "ETA - Required Field"
        Me.cboTypeofAddress.SetFocus       
        Cancel = True
        Exit Sub
    End If
End Sub

Nope, it still closes. I had to move the code to the cmdOK button and then it worked. For some reason putting it on the BeforeUpate still closes--meaning, it displays the message and goes to the TypeofAddress fields and automatically closes before you can add a TypeofAddress.

Thanks for your help.

 

[John Big Booty]

It still closes. Thanks.

Sorry I misunderstood what you where doing my apologies
Is this code being fired as the form is being closed by a close command? Either from a custom button or the form control button? If so you will be unable to cancel the form close event from another event (Before Update in this case).

What you will need to do is one disable the form's native control buttons, two create a customs form close button and put your code on that and ensure that all validation is completed prior to the Close command being called.

 

[lmcc007]

Sorry I misunderstood what you where doing my apologies
Is this code being fired as the form is being closed by a close command? Either from a custom button or the form control button? If so you will be unable to cancel the form close event from another event (Before Update in this case).

What you will need to do is one disable the form's native control buttons, two create a customs form close button and put your code on that and ensure that all validation is completed prior to the Close command being called.

I did:

Private Sub cmdOK_Click()
    If Trim(cboTypeofAddress.Value & "") = "" Then
        MsgBox "You must provide data for Type of Address.", _
            vbOKOnly, "ETA - Required Field"
        Me.cboTypeofAddress.SetFocus
    Else
        ' User wants to save and close the form
        DoCmd.Close acForm, "fdlgAddressDetail", acSavePrompt
    End If
End Sub

I would think the BeforeUpdate would work, you know, before you update check to see if this field is empty and so on.

Anyway I put it on the cmdOK button and that seems to do it.

 

[boblarson]

I would not use acSavePrompt but instead use acSaveNo.

That part is not about saving records but is about saving DESIGN CHANGES to the form. So, you do not want users saving design changes to the form (this would include persistent filters, etc.). So, use acSaveNo there so that you don't end up with some wacky thing.

 

[lmcc007]

I would not use acSavePrompt but instead use acSaveNo.

That part is not about saving records but is about saving DESIGN CHANGES to the form. So, you do not want users saving design changes to the form (this would include persistent filters, etc.). So, use acSaveNo there so that you don't end up with some wacky thing.

Hey boblarson,

I see "acSavePrompt" a lot in the examples and books. So, let me get this correct in my head. acSavePrompt will save changes to the design of the form, but will not save changes made to the record; therefore, I should leave it off or use acSaveno. Is that correct?

 

[boblarson]

acSavePrompt will ask the user if they want to save changes to the form. Not the data. acSaveNo just bypasses the dialog to them and says to discard any changes that may have been made to the DESIGN of the form; not the data.

If they are in the Before Update event and you don't stop it, changes will be saved.

Also, your code does not stop them in the first test. You need to use Cancel = True to stop the update.

Private Sub cmdOK_Click()
    If Trim(cboTypeofAddress.Value & "") = "" Then
        MsgBox "You must provide data for Type of Address.", _
            vbOKOnly, "ETA - Required Field"
[B][COLOR=red]        Cancel = True[/COLOR][/B]
        Me.cboTypeofAddress.SetFocus
    Else
        ' User wants to save and close the form
        DoCmd.Close acForm, "fdlgAddressDetail", acSaveNo
    End If
End Sub

 

[lmcc007]

acSavePrompt will ask the user if they want to save changes to the form. Not the data. acSaveNo just bypasses the dialog to them and says to discard any changes that may have been made to the DESIGN of the form; not the data.

If they are in the Before Update event and you don't stop it, changes will be saved.

Also, your code does not stop them in the first test. You need to use Cancel = True to stop the update.

Private Sub cmdOK_Click()
    If Trim(cboTypeofAddress.Value & "") = "" Then
        MsgBox "You must provide data for Type of Address.", _
            vbOKOnly, "ETA - Required Field"
[B][COLOR=red]        Cancel = True[/COLOR][/B]
        Me.cboTypeofAddress.SetFocus
    Else
        ' User wants to save and close the form
        DoCmd.Close acForm, "fdlgAddressDetail", acSaveNo
    End If
End Sub

No, the code is doing what I want it to do--that is, keep the data that's there, just go back to TypeofAddress field so I can enter the type.

Additionally, I added the Cancel = True and it will not compile. Get: Compile error. Variable not defined. And when I hit the OK button to close the error message, it highlights Cancel = True.

 

[ShaneMan]

No, the code is doing what I want it to do--that is, keep the data that's there, just go back to TypeofAddress field so I can enter the type.

Additionally, I added the Cancel = True and it will not compile. Get: Compile error. Variable not defined. And when I hit the OK button to close the error message, it highlights Cancel = True.

The message is telling you correctly. You do not have a variable "cancel" defined. Cancel was declared when you where using the BeforeUpdate event (go back to you first post and look inside of the parenthesis) I believe this will work for you since you are putting your code behind the cmdOK button

Private Sub cmdOK_Click()
If Trim(cboTypeofAddress.Value & "") = "" Then
MsgBox "You must provide data for Type of Address.", _
vbOKOnly, "ETA - Required Field"
Me.cboTypeofAddress.SetFocus
Exit Sub
Else
' User wants to save and close the form
DoCmd.Close acForm, "fdlgAddressDetail", acSaveNo
End If
End Sub

HTH,
Shane