Required Fields Code - Still adding record with missing required fields

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I have the below required fields code that I am using to ensure all required fields are filled before adding a record to our table. However, it is still adding the record even if I leave some of the required fields as blank.
Code:
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control
nl = vbNewLine & vbNewLine
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        If ctl.Tag = "*" And Trim(ctl & "") = "" Then
            msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
            "You can't save this record until this data is provided!" & nl & _
            "Enter the data and try again . . . "
            Style = vbCritical + vbOKOnly
            Title = "Required Data..."
            MsgBox msg, Style, Title
            ctl.SetFocus
            Cancel = True
            Exit For
        End If
    End If
Next

I also tried it with the second code I have below, but it is still adding the record to the table.
Code:
If IsNull(Me.CycleMonth) Then
    Cancel = True
    Call MsgBox("Cycle Month is Required. Please select Cycle Month.", vbExclamation, "EntryRequired!")
    Me.CycleMonth.SetFocus
    Exit Sub
End If
If IsNull(Me.DateReviewed) Then
    Cancel = True
    Call MsgBox("Date Reviewed is Required. Please enter/select Reviewed Date.", vbExclamation, "EntryRequired!")
    Me.DateReviewed.SetFocus
    Exit Sub
End If
If IsNull(Me.ReportType) Then
    Cancel = True
    Call MsgBox("Report Type is Required. Please select Report Type.", vbExclamation, "EntryRequired!")
    Me.ReportType.SetFocus
    Exit Sub
End If
If IsNull(Me.MainSection) Then
    Cancel = True
    Call MsgBox("Main Section is Required. Please select Main Section.", vbExclamation, "EntryRequired!")
    Me.MainSection.SetFocus
    Exit Sub
End If
If IsNull(Me.TopicSection) Then
    Cancel = True
    Call MsgBox("Topic Section is Required. Please select Topic Section.", vbExclamation, "EntryRequired!")
    Me.TopicSection.SetFocus
    Exit Sub
End If
If IsNull(Me.ReviewerType) Then
    Cancel = True
    Call MsgBox("Reviewer Type is Required. Please select Reviewer Type.", vbExclamation, "EntryRequired!")
    Me.ReviewerType.SetFocus
    Exit Sub
End If
If IsNull(Me.Reviewer) Then
    Cancel = True
    Call MsgBox("Reviewer is Required. Please select Reviewer.", vbExclamation, "EntryRequired!")
    Me.Reviewer.SetFocus
    Exit Sub
End If
If IsNull(Me.ReviewerReportArea) Then
    Cancel = True
    Call MsgBox("Reviewer Report Area is Required. Please select Reviewer Report Area.", vbExclamation, "EntryRequired!")
    Me.ReviewerReportArea.SetFocus
    Exit Sub
End If
If IsNull(Me.Individual) Then
    Cancel = True
    Call MsgBox("Ownership Individual is Required. Please select Ownership Individual.", vbExclamation, "EntryRequired!")
    Me.Individual.SetFocus
    Exit Sub
End If
If IsNull(Me.Count) Then
    Cancel = True
    Call MsgBox("Count is Required. Please enter a Count.", vbExclamation, "EntryRequired!")
    'Me.Count.SetFocus
End If
If IsNull(Me.L1) Then
    Cancel = True
    Call MsgBox("QA Category L1 is Required. Please select QA Category L1.", vbExclamation, "EntryRequired!")
    Me.L1.SetFocus
    Exit Sub
End If
If IsNull(Me.L2) Then
    Cancel = True
    Call MsgBox("QA Category L2 is Required. Please select QA Category L2.", vbExclamation, "EntryRequired!")
    Me.L2.SetFocus
    Exit Sub
End If
If IsNull(Me.L3) Then
    Cancel = True
    Call MsgBox("QA Category L3 is Required. Please select QA Category L3.", vbExclamation, "EntryRequired!")
    Me.L3.SetFocus
    Exit Sub
End If
If IsNull(Me.txtHyperlink) Then
    Cancel = True
    Call MsgBox("PDF File path (Hyperlink) is Required. Please insert PDF File path (Hyperlink).", vbExclamation, "EntryRequired!")
    Me.txtHyperlink.SetFocus
    Exit Sub
End If

I originally had the Required option set to YES in the table, but I removed it thinking that it should still work with these codes. Do I still need to mark these fields as YES in the required section of the table design view?

Also, I have required fields that are either text boxes, combo boxes, and 1 hyperlink text box. Is this causing an issue in either of the codes above?

Thank you
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
where is the code? What event?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
Note:
your code also only works on text boxes (I guess):
Code:
If ctl.ControlType = acTextBox Then

so if you have other types of controls you are trying to validate they won't be validated (assuming these control types are not acTextBox).

It shouldn't matter if you set required field to yes in the table (since your validation code should catch all the required fields before saving). That said, you can leave the fields as required in the table design as a backup plan in case your validation is somehow ignored or failing.
 

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
My apologies, both codes are in the Form's BeforeUpdate event.
FYI, they are both not active at the same time. I'm testing which of these would work best.

Also, I did notice that part of the original code and I updated it to include combo boxes as well.
Code:
If ctl.ControlType = acTextBox Or acComboBox Then

However, I get an error (run-time error '438': Object doesn't support this property or method) in the following line
Code:
 If ctl.Tag = "*" And Trim(ctl & "") = "" Then
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do I still need to mark these fields as YES in the required section of the table design view?
No, do one or the other. There isn't much benefit to what you're doing IMHO (this looks like my code) because it was meant to gather all the "empty" controls and identify them in one message. You're not doing that. Be that as it may: remember, ctl is an object. You can't trim (ctl & ""). You could assign ctl value to a variable and Trim that. When you fix that, you will probably error out on
Code:
If ctl.ControlType = acTextBox Or acComboBox Then
. You are interpreting that as most inexperienced programmers would. You need to learn to look at AND or OR statements as chunks and sometimes groups. In this case, chunks. See if you can figure out why Access won't like your statement:
chunk 1
If ctl.ControlType = acTextBox [makes sense, yes?]

chunk 2
Or acComboBox Then [doesn't make sense]

Think in terms of If ctl.ControlType = acTextBox Or ctl.Type = acComboBox Then

That's all for now - gotta go.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
MHamid: After reviewing the link I take this back
(this looks like my code)
I thought you were one of many for whom I've posted code for iterating through required controls and compiling a composite text message. Rather than this
Code:
 If ctl.Tag = "*" And Trim(ctl & "") = "" Then
I use a custom function in a standard module because it can be called from anywhere in the db (and usually is, many times) as:
Code:
Public Function IsNullEmpty(ctl As Control) As Boolean
IsNullEmpty = False
If IsNull(ctl) Or ctl="" Then IsNullEmpty = True
End Function
which is called like
Code:
If IsNullEmpty(Me.txtMyTextbox) Then
 do stuff for empty string or null
End If
Perhaps I should modify it going forward to trim any leading or trailing spaces :confused:
 

Watch MrExcel Video

Forum statistics

Threads
1,126,897
Messages
5,621,498
Members
415,845
Latest member
marej123

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
Top