how to reinstate the REQUIRED property programically

[humvie]

I have this code that prevents the user to duplicate entries at runtime. The problem is that I had to disable the REQUIRED property in the table. Now users can bypass this field without keying any data. Any suggestions on how to programically trigger the REQUIRED property would be appreciated.

Thanks in advance.

Private Sub Invoice_Number_BeforeUpdate(Cancel As Integer)
Dim intMsg As Integer
Dim intTitle As String

If Not IsNull(DLookup("[Invoice Number]", "Freight", "[Invoice Number]= '" & Me![Invoice Number] & "' And [Carrier Name]= '" & Me![Carrier Name] & "'")) Then
intMsg = MsgBox("Duplicate invoice detected.", vbInformation, "Duplicate Invoice!")
Me.[Invoice Number].Undo

Cancel = True
End If

End Sub

 

[jbehrne]

Hi,

An easy way would be to use some code like this:
- In the TextBox's "After Update Event"

If IsNull(Me!TextBox) Then
MsgBox "Value cannot be Null", vbCritical
Me!TextBox.SetFocus
End If

If you wanted to get more complicated & want to make sure that a value is entered then use this method (which forces the user back to the current recordset so that a value will be entered, if the user skips to another record):

Dim RecNum

If IsNull(Me!TextBox) Then
MsgBox "Value cannot be Null", vbCritical
RecNum = Me!KeyField
Requery
Forms!FormName.RecordsetClone.FindFirst "FormName.KeyField = " & RecNum
Forms!FormName.Bookmark = Forms!FormName.RecordsetClone.Bookmark
End If

HTH,

jbehrne


If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[humvie]

It helps! Thank you very much.

 

[danvlas]

Why not use the BeforeUpdate event of the form to ultimately check the validity of each 'required' box:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(YourTextBox) Then
Beep
MsgBox "Ooops, " & YourTextBox & " is null...Can't do that!"
Cancel = True
End If

End Sub

You can insert as many branches as you need and users won't be able to leave the record until they put in something or leave the record as it was before.

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html