isnull(string( ? )) code check

[thegameoflife]

I'm trying to check for any empty values on my form. PLEASE let me know if there is a better/more efficient way of writing the code below.

THANK YOU

Dim str(0 To 18) As Variant
Dim rst As DAO.Recordset
Dim i As Integer
Dim a As Integer
    Set str(1) = Me.txtclaim
    Set str(2) = Me.txtProv_Num
    Set str(3) = Me.txtProvider_Name
    Set str(4) = Me.txtTax
    Set str(5) = Me.txtMember_Name
    Set str(6) = Me.txtMEMBER_NUM
    Set str(7) = Me.txtPaid
    Set str(8) = Me.cmbReason_Overpay
    Set str(9) = Me.cmbRefun_Type
    Set str(10) = Me.txtDate_Received
    Set str(11) = Me.txtDATE_PAID
    Set str(12) = Me.txtRequested
    Set str(13) = Me.cmbDept_Error
    Set str(14) = Me.cmbReason
    Set str(15) = Me.cmbSource
    Set str(16) = Me.cmbProduct
    'Set str(17) = Me.cmbREGION_CODE
    Set str(18) = Me.txtmemo
    
    For i = 1 To 18
        a = a + 1
    If IsNull(str(a)) Then
        If MsgBox("One of the required fields are empty! Are you sure you want to continue?", vbYesNo, "Error") = vbNo Then
        End
        End If
    End If
    Next i

 

[KenReay]

Hi

I hate to get into the 'more efficient' debate, since I think it is more important that code is easily understandable and easily maintainable. If it can be all three then great.

Any way something like is neater that your original code:

Dim frm as Form
Dim ctl as Control

Set frm = Me
For each ctl in frm
Select Case ctl.ControlType acTextBox, acComboBox
If IsNull(ctl.Value) Then
If MsgBox("One of the required fields are empty! Are you sure you want to continue?", vbYesNo, "Error&quot = vbNo Then
End

End If
Case Else
End Select
Next Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[LittleSmudge]

Well for a start

For i = 1 To 18
a = a + 1
If IsNull(str(a)) Then


Could simply be

For i = 1 To 18
If IsNull(str(i)) Then


But then again, If you are going to the effort of listing all of the controls that you want testing then you are saving nothing over the "simple" solution of

Dim Missing As Boolean
Missing = False

If IsNull(txtclaim) Then Missing = True
If IsNull(txtProv_Num) Then Missing = True
If IsNull(txtProvider_Name) Then Missing = True
If IsNull(txtTax) Then Missing = True
If IsNull(txtMember_Name) Then Missing = True
If IsNull(txtMEMBER_NUM) Then Missing = True
If IsNull(txtPaid) Then Missing = True
If IsNull(cmbReason_Overpay) Then Missing = True
If IsNull(cmbRefun_Type) Then Missing = True
If IsNull(txtDate_Received) Then Missing = True
If IsNull(txtDATE_PAID) Then Missing = True
If IsNull(txtRequested) Then Missing = True
If IsNull(cmbDept_Error) Then Missing = True
If IsNull(cmbReason) Then Missing = True
If IsNull(cmbSource) Then Missing = True
If IsNull(cmbProduct) Then Missing = True
If IsNull(cmbREGION_CODE) Then Missing = True
If IsNull(txtmemo) Then Missing = True

If Missing Then
If MsgBox("One of the required fields are empty! Are you sure you want to continue?", vbYesNo, "Error&quot = vbNo Then
' etc .. ..
End If
End If


That is at least "Simple"


'ope-it-'elps.







G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.