If Statements to Prevent Form From Inputting w/ Missing Fields

[davidb88]

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.

 

[BrianMH]

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?

 

[davidb88]

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?

 

[BrianMH]

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.

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

 

[davidb88]

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.

 

[davidb88]

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?

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

 

[davidb88]

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!

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

 

[BrianMH]

You need to add the function outside your already existing code. Then you use it to return true or false and test for that.

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

 

[davidb88]

Thank you so much! You solved my problem!