Required Fields Code - Still adding record with missing required fields

[MHamid]

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.

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.

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

 

[xenou]

where is the code? What event?

 

[xenou]

Note:
your code also only works on text boxes (I guess):

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]

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.

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

 If ctl.Tag = "*" And Trim(ctl & "") = "" Then

 

[Micron]

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

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.

 

[xenou]

Some further explanation of this code (if you haven't been to this page yet):
http://www.databasedev.co.uk/validate_textbox.html

But it is written for textbox controls only so you will need to experiment if you have other types of controls you want to validate.

 

[Micron]

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

 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:

Public Function IsNullEmpty(ctl As Control) As Boolean
IsNullEmpty = False
If IsNull(ctl) Or ctl="" Then IsNullEmpty = True
End Function

which is called like

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