X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Prevent Empty Ms Access Form Fields, Access 2013    
post Jul 25 2017, 08:46 AM

Posts: 103
Joined: 8-July 17

How to prevent empty fields in Ms Access form [AccountNo] , [DocumentDate] and [DocumentName] in the below code ?

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler

Dim strLinkCriteria
    strLinkCriteria = "[AccountNo] = '" & Me!AccountNo & "' AND " & _
        "[DocumentDate] = " & Format(Me!DocumentDate, "\#dd-mmm-yyyy\#") & " AND " & _
        "[DocumentName] = '" & Me!DocumentName & "'"
    If DCount("*", "tblFacilityRegister", strLinkCriteria) > 0 Then
        MsgBox "This document has already been sent to Document Control earlier." & vbCrLf & _
vbCr & "Press OK to lead you to the previous record." & vbCrLf & _
vbCr & "Please write the previous Doc's ID at the ((back)) top to make sure it's actually matched.", vbCritical, "Duplicate Entry"
        Cancel = True
        Me.DataEntry = False
        Me.Recordset.FindFirst strLinkCriteria
End If

  Exit Sub
  ' MsgBox Err.Number & ": " & Err.Description
    MsgBox "You must enter a value in one of the following:" & vbCrLf & _
vbCr & "Account Number, DTD or Document Name.", vbOKOnly, "Empty Fields"

  Resume Cleanup
End Sub
Go to the top of the page
post Jul 25 2017, 09:00 AM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Try adding something like this after the "Dim strLinkCriteria" line:

'   Check required fields
    If Nz(Me.AccountNo, "") = "" Or IsNull(Me.DocumentDate) Or Nz(Me.DocumentName, "") = "" Then
        MsgBox "You are required to fill in the Account Number, Document Date and Document Name before you can save."
        Cancel = True
        Exit Sub
    End If

By the way, you might want to consider using "Dim strLinkCriteria As String" to more explicitly create the string variable.

Hope this helps,

Go to the top of the page
post Jul 25 2017, 10:10 AM

UtterAccess VIP
Posts: 7,758
Joined: 24-May 10
From: Downeast Maine

"[DocumentDate] = " & Format(Me!DocumentDate, "\#dd-mmm-yyyy\#")

I would use a date format Access cannot misunderstand: either mm/dd/yyyy or yyyy/mm/dd. dd-mmm-yyyy will most likely work as long as English is used, but the important point is that a format applied to the table field does not affect the underlying value. Today's date is stored as 42941 no matter the format, language, regional settings, or anything else. The value represents the number of days since Dec. 30, 1899.

That said, it is good practice when perfoming such comparisons to format the date with delimiters as you have done, but using one of the formats mentioned above.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th December 2018 - 04:35 PM