If Statements to Prevent Form From Inputting w/ Missing Fields

davidb88

Board Regular
Joined
Sep 23, 2013
Messages
76
I have a form in my database with 12 data input boxes. The user inputs the forms and then they press a "Save" button and it saves it as a new record in a table. I would like to implement a check though of sorts where when the user presses "Save" it checks to make sure all fields are filled in before actually saving. If there are missing fields I would like to have a message box pop up confirming that they want to save with missing fields. If they select "Ok" it would save the record with missing otherwise if they selected "Cancel" it would exit the sub and cancel the macro. I have tried different series of If Statements but I am not having any luck. Is there a good way to implement this sort of check?

Thanks.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
yeah you are better to cycle through the controls on the form and test each with a loop. Are all inputs text boxes or are there also comboboxes, listboxes, etc?
 
Upvote 0
Brian, thanks for your response. I have 8 text boxes and 4 combo boxes. I understand the concept of a loop but I've never been able to create one before. Can you suggest how to do that?
 
Upvote 0
Add this function to your forms code. You can then call it to test if there are any blank textboxes or comboboxes on the form. If there are it will return true and you can use an if statement in your save code to test and act accordingly.


Code:
Function CheckBlankControls()
Dim c As Control
CheckBlankControls = False
For Each c In Me.Controls
    If TypeName(c) = "TextBox" Or typenmae(c) = "ComboBox" Then
        If IsNull(c) Then
            CheckBlankControls = True
            Exit For
        End If
    End If
Next c
End Function
 
Upvote 0
Awesome. Thank for you this. And just to be clear, I don't have to change where you have "Me.Controls" to Me.[My Form Name] right? Also, if I wanted a message box to pop up if one of those checks fail where do I add that?

Thanks again.
 
Upvote 0
Also, where is this code entered? I have tried entering it where I have my other code that is transfering the inputs to the table but I get an error saying "expected end sub". What am I missing?

Rich (BB code):
Sub savebutton_Click()
Function CheckBlankControls()
Dim c As Control
CheckBlankControls = False
For Each c In Me.Controls
    If TypeName(c) = "TextBox" Or TypeName(c) = "Combobox" Then
        If IsNull(c) Then
            CheckBlankControls = True
            Exit For
        End If
    End If
Next c
End Function
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblclstrack")
rst.AddNew
 
rst![Today's Date] = Me.today_date
rst![Email Date] = Me.email_date
rst![Email Sender] = Me.email_sender
rst![Email Subject] = Me.email_subject
rst![CD#] = Me.cd_number
rst![Inquiry Type] = Me.inquiry_type
rst![CU#] = Me.cu_number
rst![comments] = Me.comments
rst![state] = Me.state
rst![Request Number] = Me.request_number
rst![Assigned To] = Me.request_assign
rst![Request Status] = Me.request_status
 
rst.Update
rst.Close
 
Set rst = Nothing
 
Me.today_date = ""
Me.email_date = ""
Me.email_sender = ""
Me.email_subject = ""
Me.cd_number = ""
Me.inquiry_type = ""
Me.cu_number = ""
Me.comments = ""
Me.state = ""
Me.request_number = ""
Me.request_assign = ""
Me.request_status = ""
 
today_date.SetFocus
 
End Function
 
Upvote 0
Ok. So the solution that Brian recommended didn't work for me. I assume I am doing something wrong in the implementation of it? I tried one more thing which works for me somewhat. I have an if statement that runs when the button is clicked. The problem is this code will only work when I first add it to the code. When I re-open the input form after closing the form, the code no longer runs. It is the strangest thing. When I add the code to the VB screen and go back to the form it works well. But when I close the form and re-open it, the code no longer works and it doesn't execute anything. Has anyone else experience this? Or can someone suggest an alternative or how to properly implement Brian's function as I am sure that's more foil proof than my solution? Thanks!

Rich (BB code):
Sub savebutton_Click()
 
 
If Len(Me.today_date) = 0 Or Len(Me.email_date) = 0 Or Len(Me.email_sender) = 0 Or Len(Me.email_subject) = 0 Or Len(Me.cd_number) = 0 _
        Or Len(Me.inquiry_type) = 0 Or Len(Me.cu_number) = 0 Or Len(Me.comments) = 0 Or Len(Me.state) = 0 Or Len(Me.request_number) = 0 _
        Or Len(Me.request_assign) = 0 Or Len(Me.request_status) = 0 Then
    If MsgBox("There are one or more fields missing from the input form. To import the record with the missing fields press 'OK'. " _
        & "Otherwise press 'Cancel' and enter in the missing inputs.", vbExclamation + vbOKCancel, "Missing Fields") = vbCancel Then
       
        today_date.SetFocus
       
        Exit Sub
 
Else
 
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblclstrack")
rst.AddNew
 
rst![Today's Date] = Me.today_date
rst![Email Date] = Me.email_date
rst![Email Sender] = Me.email_sender
rst![Email Subject] = Me.email_subject
rst![CD#] = Me.cd_number
rst![Inquiry Type] = Me.inquiry_type
rst![CU#] = Me.cu_number
rst![comments] = Me.comments
rst![state] = Me.state
rst![Request Number] = Me.request_number
rst![Assigned To] = Me.request_assign
rst![Request Status] = Me.request_status
 
rst.Update
rst.Close
 
Set rst = Nothing
 
Me.today_date = ""
Me.email_date = ""
Me.email_sender = ""
Me.email_subject = ""
Me.cd_number = ""
Me.inquiry_type = ""
Me.cu_number = ""
Me.comments = ""
Me.state = ""
Me.request_number = ""
Me.request_assign = ""
Me.request_status = "Pending"
 
today_date.SetFocus
 
End If
End If
 
 
End Sub
 
Upvote 0
You need to add the function outside your already existing code. Then you use it to return true or false and test for that.

Code:
Option Explicit


Sub savebutton_Click()


    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("tblclstrack")
    If CheckBlankControls = True Then
        Select Case MsgBox("1 or more fields have been left blank.  Are you sure you want to submit with missing information?", vbYesNo Or vbExclamation Or vbDefaultButton2, "Blank Fields Alert")


            Case vbYes
                'do nothing and continue sub
            Case vbNo
                Exit Sub
        End Select
    End If
    rst.AddNew


    rst![Today    's Date] = Me.today_date
    rst![Email Date] = Me.email_date
    rst![Email Sender] = Me.email_sender
    rst![Email Subject] = Me.email_subject
    rst![CD#] = Me.cd_number
    rst![Inquiry Type] = Me.inquiry_type
    rst![CU#] = Me.cu_number
    rst![Comments] = Me.Comments
    rst![State] = Me.State
    rst![Request Number] = Me.request_number
    rst![Assigned To] = Me.request_assign
    rst![Request Status] = Me.request_status


    rst.Update
    rst.Close


    Set rst = Nothing


    Me.today_date = ""
    Me.email_date = ""
    Me.email_sender = ""
    Me.email_subject = ""
    Me.cd_number = ""
    Me.inquiry_type = ""
    Me.cu_number = ""
    Me.Comments = ""
    Me.State = ""
    Me.request_number = ""
    Me.request_assign = ""
    Me.request_status = ""


    today_date.SetFocus


End Sub


Function CheckBlankControls()
    Dim c As Control
    CheckBlankControls = False
    For Each c In Me.Controls
        If TypeName(c) = "TextBox" Or TypeName(c) = "Combobox" Then
            If IsNull(c) Then
                CheckBlankControls = True
                Exit For
            End If
        End If
    Next c
End Function
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top