Is there a tidier way to write this?

[TryingMyBest]

Hello

Does anyone know of a tidier way to write this or is this ok?

ElseIf IsNull(Me.txtResponsiblePerson) Then
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
ElseIf IsNull(Me.txtEmailAddress) Then
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
ElseIf IsNull(Me.txtAuditee) Then
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
ElseIf IsNull(Me.txtAuditeeEmailAddress) Then
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
ElseIf IsNull(Me.txtWBSCode) Then
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
ElseIf IsNull(Me.cboReasonForAudit) Then
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
ElseIf IsNull(Me.cboAuditorName) Then
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
ElseIf IsNull(Me.txtPlannedAuditDate) Then
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
ElseIf IsNull(Me.cboLocation) Then
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
ElseIf IsNull(Me.txtWBSCode) Then
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
ElseIf IsNull(Me.txtPlannedAuditDuration) Then
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
Else

Many thanks
Jo

 

[KenHigg]

Can you do this at the table level with the 'Required' attribute for each fld?

kh

 

[Pat Hartman]

Aside from being verbose your code isn't accomplishing anything since it doesn't prevent the bad data from being saved. This type of edit should be done in the FORM's BeforeUpdate event and you should cancel the update if an error is found -
Cancel = True

As Ken suggested, you should set the fields to required in the table.

If you decide you need to perform this edit anyway, do it by looping through the controls collection of the form. You'll find examples in help or here of the necessary code.

 

[TryingMyBest]

Oops perhaps more explanation required

The form is unbound so the data won't be saved until the button is pressed, hence bad data is not a problem.

I'll try making them required in the table and see if that helps.

How do I still display the error message if I adopt this solution? Is it just a case of OnError GoTo...?

Jo

 

[Mile-O]

First of all, why is the form unbound? What benefit do you think you'll achieve by having an unbound form?

Anyway, the simplest code would be:

Dim ctl As Control
For Each ctl In Me
    Select Case ctl.ControlType
        Case Is = acTextBox, acComboBox, acListBox
            If IsNull(ctl) Then
                MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
            Exit Sub
            End If
    End Select
Next
Set ctl = Nothing

 

[TryingMyBest]

Getting there

OK so I've managed to follow the suggestion above and it's working quite well.

However...I get an error that the fields could not be added to the query before I get my custom error message. How can I circumvent the system error message about the query not being able to run?

Private Sub cmdAddToAudit_Click()
On Error GoTo Err_cmdAddToAudit_Click

'If no audit has been chosen display error message
If IsNull(Me.txtAuditNumber) Then
    MsgBox "You have not chosen an audit to add this item to", vbOKOnly, "Error"
Else
'Add all form data to the audit records table and repopulate audit details subform
    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"
End If

Exit_cmdAddToAudit_Click:
    Exit Sub

Err_cmdAddToAudit_Click:
    MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
    Resume Exit_cmdAddToAudit_Click

End Sub

 

[TryingMyBest]

First of all, why is the form unbound? What benefit do you think you'll achieve by having an unbound form?

The form is unbound because it references many tables and queries...also because I don't want any data saved to tables unless the user clicks the button. It was the easiest way I could find to do it and it seems to work very well, even if my code is verbose which I'm trying to deal with here. It's perhaps not the most efficient way to write a database but I'm no expert.

Jo

 

[Mile-O]

The form is unbound because it references many tables and queries...

So your problem is with table design and normalisation?

 

[TryingMyBest]

So your problem is with table design and normalisation?

I guess so

If I fix this will my coding be easier or will I have to completely recode the entire application again? I'm not keen on completely recoding just now as I have a Christmas deadline to meet and can iron out the design issues at a later date.

 

[Mile-O]

With a properly designed table structure there's very little need for code at all - the database's system Jet properly deals with all the indexing and queries, etc..

The code I gave above, though, will check all of the controls on a form for data.

 

[TryingMyBest]

Thanks

Thanks for all your help.

I think I would rather code the actions....I'm more comfortable with code as it's all in the one place so I can easily find where I have gone wrong. Perhaps I'm mad....I don't know. Anyway I've sorted the original query so thankyou.

 

[KenHigg]

Are you going to use something similar to your original post? If so, I can see some minor improvements you may want to consider.

kh

 

[Pat Hartman]

The form is unbound because it references many tables and queries...also because I don't want any data saved to tables unless the user clicks the button.

- We've had this discussion here many times. The primary reason to use Access is because of its support for bound forms and reports.

You can gain complete control over bound forms if you are willing to learn how to use form and control events properly. You will need only a verrrry small percentage of your existing code and the best thing is you won't have to debug it!

 

[TryingMyBest]

Are you going to use something similar to your original post? If so, I can see some minor improvements you may want to consider.

kh

Yes I have because otherwise I don't know how to circumvent the system error messages.

Any suggestions will be gratefully received

- We've had this discussion here many times. The primary reason to use Access is because of its support for bound forms and reports.

My primary reason for using Access is because I don't have Visual Basic....hence I can use VBA as a substitute. I don't want bound forms and I don't want Access reports. I want a very flexible reporting mechanism so will be using automatic mail merge to Word and Excel.

 

[KenHigg]

' Sub Start here

If IsNull(Me.txtAuditee) Then goto nullError
If IsNull(Me.txtAuditeeEmailAddress) Then goto nullError
If IsNull(Me.txtWBSCode) Then goto nullError
If IsNull(Me.cboReasonForAudit) Then goto nullError

exit_sub:
exit

nullError:
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
'I assume you want to do something else here...
goto exit_sub

' Sub end here

 

[KenHigg]

Or:

' Sub Start here

If IsNull(Me.txtAuditee) or _
IsNull(Me.txtAuditeeEmailAddress) or _
IsNull(Me.txtWBSCode) or _
IsNull(Me.cboReasonForAudit) _
Then goto nullError
end If

exit_sub:
exit

nullError:
MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
'I assume you want to do something else here...
goto exit_sub

' Sub end here

 

[Mile-O]

Anyway, the simplest code would be:

Dim ctl As Control
For Each ctl In Me
    Select Case ctl.ControlType
        Case Is = acTextBox, acComboBox, acListBox
            If IsNull(ctl) Then
                MsgBox "You must complete all relevant fields to continue", vbOKOnly, "Error"
            Exit Sub
            End If
    End Select
Next
Set ctl = Nothing

You haven't tried this, have you?

 

[KenHigg]

SJ - The only issue would be if there happened to be controls that were not required...

kh

 

[Mile-O]

That can easily be factored in. All you'd need to do was put a * in the Tag property.

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
            Exit Sub
        End If
    End If
Next
Set ctl =Nothing

 

[KenHigg]

Cool. I knew you'd have a clever response

kh