CODEWITHSUNDEEP
ms-accessvba
cdomination
cdomination

Reputation: 603

Make all fields mandatory to be filled in

This question is continued from here: Add user input to Excel table upon upload to Access database

Now that I have my fields connected to a table in my database, I want to make sure that everyone fills them in. Upon clicking the Import button, I want to check the fields (SANumber, SerialNumber, CustomerName, and LyoSize) to make sure it will be a 'valid upload'.

I have this code so far:

Function CheckInputs() As Boolean

If Me.SANumber.value Or Me.SerialNumber.value Or Me.CustomerName.value Or Me.LyoSize.value = Null Then
 CheckInputs = True
Else
 CheckInputs = False
End If

 End Function

'Import MCL Files Code
Private Sub ImportMCL_Click()

On Error GoTo ErrorHandler
'disable ms access warnings
DoCmd.SetWarnings False

Call CheckInputs
If CheckInputs = True Then
MsgBox "All inputs must be entered!"
Exit Sub
Else

'load spreadsheet in .xls format
DoCmd.TransferSpreadsheet acImport, 8, "_MCL_UPLOAD", selectFile(), True
DoCmd.OpenQuery "UpdateMCL"
Call InsertInto_MASTER_UPLOAD
Call Delete_MCL_UPLOAD
MsgBox "MCL Imported Successfully!"
're-enable ms access warnings
DoCmd.SetWarnings True    
End If

Exit Sub

ErrorHandler:
MsgBox "There was an Error: " & Err & ": " & Error(Err)

End Sub

It should work, but keeps on giving me the

ERROR: 13. Type Mismatch

Upvotes: 0

Views: 338

Answers (3)

user5438532
user5438532

Reputation:

Also, if you're clearing out afterwards by going something like SANumber = "" then testing for Nulls might not work. I'd check for both nulls and blanks. This is a general template you could use. 

Dim LResponse As Integer
If (Nz(Me.SANumber.Value) = "") Then
    MsgBox "Please enter a SA Number.", vbCritical + vbOKOnly, "Error"
ElseIf (Nz(Me.SerialNumber.Value) = "") Then
   MsgBox "Please enter a Serial Number.", vbCritical + vbOKOnly, "Error"

'All criteria met
Else
    LResponse = MsgBox("Would you like to submit? ", vbQuestion + vbYesNo, "Question")
    If LResponse = vbYes Then
        'enter code here
    ElseIf LResponse = vbNo Then
        MsgBox ("Not submitted.")
    End If
End If

Upvotes: 1

user6432984
user6432984

Reputation:

You CheckInputs() functions logic is incorrect. Or will return true if any one condition is meet.
To get your desired result you can either ask does:
If Condition1 = true AND Condition2 = true AND ....
Otherwise you can ask If Condition1 = false OR Condition2 = false OR ....
Try this....

Function isFormValid() As Boolean

    If isTextFieldInvalid(Me.SANumber) Or isTextFieldInvalid(Me.SerialNumber) Or isTextFieldInvalid(Me.CustomerName.Value) Or Me.LyoSize.Value = Null Then
        isFormValid = False
    Else
        isFormValid = True
    End If

End Function

Function isTextFieldInvalid(FieldControl) As Boolean
    If Not IsNull(FieldControl) Then
        If Len(Trim(FieldControl.Value)) Then
            isFieldValid = True
        End If
    End If
End Function

'Import MCL Files Code
Private Sub ImportMCL_Click()

    On Error GoTo ErrorHandler
    'disable ms access warnings
    DoCmd.SetWarnings False

    If isFormValid Then
        MsgBox "All inputs must be entered!"
        Exit Sub
    Else

        'load spreadsheet in .xls format
        DoCmd.TransferSpreadsheet acImport, 8, "_MCL_UPLOAD", selectFile(), True
        DoCmd.OpenQuery "UpdateMCL"
        Call InsertInto_MASTER_UPLOAD
        Call Delete_MCL_UPLOAD
        MsgBox "MCL Imported Successfully!"
        're-enable ms access warnings
        DoCmd.SetWarnings True
    End If

    Exit Sub

ErrorHandler:
    MsgBox "There was an Error: " & Err & ": " & Error(Err)

End Sub

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166281

You need to specifically check each field for null - you cannot do it this way:

If Me.SANumber.value Or Me.SerialNumber.value Or _
   Me.CustomerName.value Or Me.LyoSize.value = Null Then

Something like

If IsNull(Me.SANumber) Or IsNull(SerialNumber) Or _
   IsNull(Me.CustomerName) Or IsNull(Me.LyoSize) = Null Then

You should rename your function to something like "EmptyInputs" to make your code a little more self-documenting. "CheckInputs" is a little non-descriptive.

Upvotes: 2

Related Questions