Is there a tidier way to write this?

[TryingMyBest]

What have I done wrong?

Ok...I tried SJ's code and it still adds the data to the table and doesn't display the error message....here's my code can you see where it has gone wrong please?

Private Sub cmdAddToAudit_Click()

Dim ctl As Control
For Each ctl In Me
    If ctl.Tag = "*" Then
        If IsNull(ctl) Then
            MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
            ctl.SetFocus
        Else
            DoCmd.RunSQL "INSERT INTO TBLAuditRecords (ProgrammeNumber, ProjectName, TeamName, ProcessName, ResponsiblePerson, RPEMailAddress, Auditee, AEmailAddress, WBSCode, ReasonForAudit, AssignedAuditor, PlannedAuditDate, PlannedAuditDuration, AuditLocation, AuditNumber)" & _
            "VALUES([Forms]![frmAuditProgrammePlanning]![txtProgrammeID], [Forms]![frmAuditProgrammePlanning]![cboProjectName], [Forms]![frmAuditProgrammePlanning]![cboTeamName], [Forms]![frmAuditProgrammePlanning]![cboProcessName], [Forms]![frmAuditProgrammePlanning]![txtResponsiblePerson], [Forms]![frmAuditProgrammePlanning]![txtEmailAddress], [Forms]![frmAuditProgrammePlanning]![txtAuditee], [Forms]![frmAuditProgrammePlanning]![txtAuditeeEmailAddress], [Forms]![frmAuditProgrammePlanning]![txtWBSCode], [Forms]![frmAuditProgrammePlanning]![cboReasonForAudit], [Forms]![frmAuditProgrammePlanning]![cboAuditorName], [Forms]![frmAuditProgrammePlanning]![txtPlannedAuditDate], [Forms]![frmAuditProgrammePlanning]![txtPlannedAuditDuration], [Forms]![frmAuditProgrammePlanning]![cboLocation], [Forms]![frmAuditProgrammePlanning]![txtAuditNumber])"
            DoCmd.Requery "SUBAuditDetails"
            DoCmd.Requery "cboAuditItem"
            Exit Sub
        End If
    End If
Next
Set ctl = Nothing

End Sub

If I comment out exit sub then the error displays but it adds many new records to the table.

I've messed a bit more and it would seem that this code displays the error if none of the required fields are populated. I'm looking to have an error if one or more of the required fields aren't populated.

Thanks for all your help
A very confused Jo

 

[Mile-O]

You have put the SQL within the loop so values are inserted every time there is a value in the control.

Change your code to this:

Private Sub cmdAddToAudit_Click()

    If IsComplete(Me) Then
        DoCmd.RunSQL "INSERT INTO TBLAuditRecords (ProgrammeNumber, ProjectName, TeamName, ProcessName, ResponsiblePerson, RPEMailAddress, Auditee, AEmailAddress, WBSCode, ReasonForAudit, AssignedAuditor, PlannedAuditDate, PlannedAuditDuration, AuditLocation, AuditNumber)" & _
            "VALUES([Forms]![frmAuditProgrammePlanning]![txtProgrammeID], [Forms]![frmAuditProgrammePlanning]![cboProjectName], [Forms]![frmAuditProgrammePlanning]![cboTeamName], [Forms]![frmAuditProgrammePlanning]![cboProcessName], [Forms]![frmAuditProgrammePlanning]![txtResponsiblePerson], [Forms]![frmAuditProgrammePlanning]![txtEmailAddress], [Forms]![frmAuditProgrammePlanning]![txtAuditee], [Forms]![frmAuditProgrammePlanning]![txtAuditeeEmailAddress], [Forms]![frmAuditProgrammePlanning]![txtWBSCode], [Forms]![frmAuditProgrammePlanning]![cboReasonForAudit], [Forms]![frmAuditProgrammePlanning]![cboAuditorName], [Forms]![frmAuditProgrammePlanning]![txtPlannedAuditDate], [Forms]![frmAuditProgrammePlanning]![txtPlannedAuditDuration], [Forms]![frmAuditProgrammePlanning]![cboLocation], [Forms]![frmAuditProgrammePlanning]![txtAuditNumber])"
        DoCmd.Requery "SUBAuditDetails"
        DoCmd.Requery "cboAuditItem"
    Else
        MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
    End If

End Sub


Private Function IsComplete(ByVal f As Form) As Boolean
    On Error GoTo Err_IsComplete
    Dim ctl As Control
    For Each ctl In f
        If ctl.Tag = "*" Then
            If IsNull(ctl) Then
                IsComplete = False
                Exit Function
            End If
        End If
    Next
    IsComplete = True
Exit_IsComplete:
    Set ctl = Nothing
    Exit Function
Err_IsComplete:
    IsComplete = False
    Resume Exit_IsComplete
End Function

And make sure you put an asterisk in the Tag property of each control you want to check for a value in.

 

[TryingMyBest]

Perfect

Thank you so much....that is miles better than what I had....I wish I had a better grasp of VB I guess it'll come to me in time.

Jo

 

[Mile-O]

I think the quickest way to grasp it is to realise there are a tiny number of selection structures and a tiny number of loops. Couple this with variables and you are laughing.

Selection Structures:

• IF...THEN...ELSE...END IF
• SELECT CASE....CASE IS...CASE ELSE....END SELECT

Loop Structures:

• FOR...TO...NEXT
• FOR....EACH...NEXT
• WHILE...WEND
• DO...LOOP

 

[TryingMyBest]

I think the quickest way to grasp it is to realise there are a tiny number of selection structures and a tiny number of loops. Couple this with variables and you are laughing.

Selection Structures:

• IF...THEN...ELSE...END IF
• SELECT CASE....CASE IS...CASE ELSE....END SELECT

Loop Structures:

• FOR...TO...NEXT
• FOR....EACH...NEXT
• WHILE...WEND
• DO...LOOP

Thanks I'll bear that in mind

Jo