If Form Field is Left Blank Throw Message (1 Viewer)

BHWATT

New member
Local time
Yesterday, 22:48
Joined
Oct 6, 2017
Messages
6
I have been working on a form and would like it to throw a message if certain fields are not filled in correctly. I have it coded to where it works the way I want it but the issue is I have it in the before update sub and then the button I click to save is coded DoCmd.GoToRecord , , acNewRec.

When I click the save button it runs the before update that highlights the blank fields needed before it can save but then it attempts to save it after and throws an error saying Run time error 2105 You cant go to the specific field. I guess I need to figure out how to stop it from attempting to save after it is determined that certain fields are blank.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iResponse As Integer

On Error Resume Next
    Dim ctl As Control
    For Each ctl In Me.Controls
        With ctl
            If .Tag = "required" Then
                If Nz(.Value, "") = "" Then
                    .BackColor = RGB(255, 255, 191)
                    If Not Cancel Then .SetFocus
                    Cancel = True
                End If
            End If
        End With
    Next ctl
    If Cancel Then MsgBox "Your changes cannot be saved until the fields highlighted have been filled in.", vbCritical, "Missing Data!"
End Sub


Private Sub SaveBtn_Click()
DoCmd.GoToRecord , , acNewRec
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:48
Joined
Sep 21, 2011
Messages
14,048
So set a global variable/tempvar that says whether you can execute the newrec code?
 

BHWATT

New member
Local time
Yesterday, 22:48
Joined
Oct 6, 2017
Messages
6
Actually I think I may have just figured it out. I put all of the code in the SaveBtn sub and then added in If Cancel = False Then DoCmd.GoToRecord , , acNewRec at the end and that seems tohave solved the issue
 

Isaac

Lifelong Learner
Local time
Yesterday, 22:48
Joined
Mar 14, 2017
Messages
8,738
I have been working on a form and would like it to throw a message if certain fields are not filled in correctly. I have it coded to where it works the way I want it but the issue is I have it in the before update sub and then the button I click to save is coded DoCmd.GoToRecord , , acNewRec.

When I click the save button it runs the before update that highlights the blank fields needed before it can save but then it attempts to save it after and throws an error saying Run time error 2105 You cant go to the specific field. I guess I need to figure out how to stop it from attempting to save after it is determined that certain fields are blank.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iResponse As Integer

On Error Resume Next
    Dim ctl As Control
    For Each ctl In Me.Controls
        With ctl
            If .Tag = "required" Then
                If Nz(.Value, "") = "" Then
                    .BackColor = RGB(255, 255, 191)
                    If Not Cancel Then .SetFocus
                    Cancel = True
                End If
            End If
        End With
    Next ctl
    If Cancel Then MsgBox "Your changes cannot be saved until the fields highlighted have been filled in.", vbCritical, "Missing Data!"
End Sub


Private Sub SaveBtn_Click()
DoCmd.GoToRecord , , acNewRec
End Sub
You may have forgotten to actually DO the cancel.

Cancel=true
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:48
Joined
Feb 19, 2002
Messages
42,981
Actually I think I may have just figured it out. I put all of the code in the SaveBtn sub and then added in If Cancel = False Then DoCmd.GoToRecord , , acNewRec at the end and that seems tohave solved the issue
Put the code back into the form's BeforeUpdate event. That is where it belongs. Your code is incorrect. I'll just fix the part that is wrong:

Code:
               If Nz(.Value, "") = "" Then
                    .BackColor = RGB(255, 255, 191)
                    ctl.SetFocus
                    Cancel = True
                    Exit Sub
                Else
                    .BackColor = (?,?,?)
                End If

Don't forget, you need to reset the .BackColor to whatever the default is when the control is not empty.
Also, you probably need move validation than this. Even if dates don't have specific range settings, you should try to avoid typos. 5/27/222 is a valid date and an easy typo to make.

Also, you are not writing a macro so I'll move the thread.
 
Last edited:

Users who are viewing this thread

Top Bottom