BeforeUpdate Prompt if Field is Empty

[Nadine67]

Hello and thank you for any attention this post may receive



Windows 8.1
MS Access 2013

There are mandatory fields on my form which must be filled in prior to saving. I have code which prompts the user upon saving when any of the mandatory fields is empty.

On the form is two buttons 'Save and New' and 'Save and Exit'. If I chose 'Save and New' then the prompts will appear each time I try to save the record with missing fields. it will prompt until all fields are filled in. This is exactly what I want.

However if I choose 'Save and Exit' it prompts for the first empty mandatory field and so the user selects 'OK' to acknowledge the prompt (as the code asks for this ), and then instead of going back to the form a warning pops up 'You can't save this record at this time' and asks 'do you want to close the database objet anyway?'. 'Yes' closes the form and the record is lost (the ID does not exist in the table) and the auto-numbering ID will be missing that record. Whereas 'No' results in a 'Macro Single Step' window pops up with the only option of 'Stop All Macros'. Stopping all macros takes me back to the form and allows me to fill the empty fields.

My code is this

Private Sub Form_BeforeUpdate(Cancel As Integer)
    'Go through each 'required' field and prompt if empty
    If Me.[Title] & "" = "" Then
        MsgBox "A 'Problem Title' is required, please complete this field.", vbOKOnly
        Me.[Title].SetFocus
        Cancel = True
        Exit Sub
    End If
    If Me.[txtProblemStatement] & "" = "" Then
        MsgBox "A 'Problem Statement' is required, please complete this field.", vbOKOnly
        Me.[txtProblemStatement].SetFocus
        Cancel = True
        Exit Sub
    End If
    If Me.[txtObjective] & "" = "" Then
        MsgBox "A 'Problem Objective' is required, please complete this field.", vbOKOnly
        Me.[txtObjective].SetFocus
        Cancel = True
        Exit Sub
    End If
End Sub

The macro behind 'Save and Exit' is the basic 'Close Window' macro with save as 'Yes'. The macro behind 'Save and New' is as per the attached image.




I am not sure how to solve this issue. Does anyone have any suggestions or has anyone come across this before?

Thank you!

Nadine

[NTC]

the Go To Record step is not within the If/Then container

you might consider changing to a new command button and use your vba code instead - - - not that there is anything wrong with macros - - - but if your comfort zone is more vba oriented you might as well.....

[Nadine67]

Thank you NTC

I first tried this code - it prompts me for the first empty required field, yet when I answer ok the form closes and I don't have the opportunity to enter anything in the empty required field, nothing is saved and the ID number is lost.

DoCmd.Close acForm, Me.Name

I then tried this code with the same results.

DoCmd.RunCommand acCmdSaveRecord

So then I tried this and it works.

Private Sub SaveExit_Click()
On Error GoTo Err_cmdCloseForm_Click
 
 DoCmd.RunCommand acCmdSaveRecord
 DoCmd.Close
 
Exit_cmdCloseForm_Click:
 Exit Sub
 
Err_cmdCloseForm_Click:
 MsgBox Err.Description
 Resume Exit_cmdCloseForm_Click
 
End Sub

Thank you for your suggestion NTC, it was very much appreciated. Have a great day!