CAN THIS VBA BE SIMPLIFIED

[nigelh]

I am fairly new to VBA and would like to know if the following code could be simplified using arrays.
The code fires on the click action of a SAVE command button. It is checking that all fields have been completed.
Any help would be appreciated.

Private Sub cmdSaveExit_Click()

'Declare variables

Dim first 'First Name
Dim last 'Last Name
Dim dept 'Department
Dim cont 'Contract
Dim start 'Start Date
Dim leave 'Leave Date

'Set Variables
With Me
first = .First_Name
last = .Last_Name
dept = .Department
cont = .Shift_Worker
start = .Date_Joined_Company
leave = .Date_Left_Company
End With

'Check required variables are filled in

If IsNull(first) Then
MsgBox ("First Name must be supplied"), _ vbExclamation, "Error!"
Me.First_Name.SetFocus
GoTo exitSub
End If

If IsNull(last) Then
MsgBox ("Last Name must be supplied"), _ vbExclamation, "Error!"
Me.Last_Name.SetFocus
GoTo exitSub
End If

If IsNull(dept) Then
etc...........

exitSub:
Exit Sub

End Sub

 

[Robert Dunstan]

You could make use of the controls collection and then make use of a For...Next loop to test each control to see if it has an entry.

The code would look something like this:

Dim ctl as Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If IsNull(ctl) Then
MsgBox("Your message in here",vbExclamation,"Error")
ctl.SetFocus
Exit Sub
end if
end if
Next ctl

This is a very simple loop through each text box control and is starting point. Have a look at Access help under Controls Collection for more info.

HTH
Rob

 

[nigelh]

Thanks Robert, I've substituted my code with your example and it works fine.