UtterAccess.com
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    
 
   
Alhakeem1977
post Jul 25 2017, 08:46 AM
Post#1



Posts: 103
Joined: 8-July 17



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

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.Undo
        Me.DataEntry = False
        Me.Recordset.FindFirst strLinkCriteria
End If

Cleanup:
  Exit Sub
  
  
ErrorHandler:
  ' MsgBox Err.Number & ": " & Err.Description
  Beep
    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
 
doctor9
post Jul 25 2017, 09:00 AM
Post#2


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


Alhakeem1977,

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

CODE
'   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,

Dennis
Go to the top of the page
 
BruceM
post Jul 25 2017, 10:10 AM
Post#3


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


QUOTE
"[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