By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,505 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Validate Data

P: 51
Hello dear... I need any one's help

I have a table "Process" with only 2 fields. Drawing & Rev
Now I want to sent these drawings for approval in New Revision.
Now I am making a new form. The data of this form must be saved in a new table AsBuilt.
I want to enter Drawing and its Rev. and system must tell me that "this Rev is already exist".
How to do it with DLookup Function. and where to put this function BeforeUpdate or AfterUpdate.
My database is attached. Thanks in advance.
Irshad Ahmed
Attached Files
File Type: zip TEST.zip (29.5 KB, 18 views)
Dec 8 '11 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 446
Hi
The bottom line is you must have a unique index on your table to prevent duplicates.

I prefer to put checks in the After_Update event but it really depends on what action you are going to take next.

The syntax for the lookup in the After_Update event of field 'Rev' would be;-
Expand|Select|Wrap|Line Numbers
  1. Private Sub Rev_AfterUpdate()
  2. Dim varX As Variant
  3.  
  4. varX = DLookup("[DRAWING]", "ASBUILT", "[DRAWING]='" & Me!Drawing & "' AND [REV] ='" & Me!Rev & "'")
  5. If Not IsNull(varX) Then
  6.     MsgBox "This Drawing and Revision already exists", vbCritical, "Duplicate!"
  7.     Me.Drawing.SetFocus
  8.     Me.Rev.SetFocus
  9. End If
  10.  
  11. End Sub
  12.  
DLOOKUP is quite powerfull and you can retrieve two fields at the same time! Hence a more informative message might be as follows;-
Expand|Select|Wrap|Line Numbers
  1. Private Sub Rev_AfterUpdate()
  2. Dim varX As Variant
  3.  
  4. varX = DLookup("[TRNSMTL] & ' on ' & [RETURN]", "ASBUILT", "[DRAWING]='" & Me!Drawing & "' AND [REV] ='" & Me!Rev & "'")
  5. If Not IsNull(varX) Then
  6.     MsgBox "This Drawing was Revised by Transmital No. " & varX, vbCritical, "Duplicate!"
  7.     Me.Drawing.SetFocus
  8.     Me.Rev.SetFocus
  9. End If
  10.  
  11. End Sub
  12.  
Both define a Variant type variable and then do the lookup. If nothing is found then Null is returned (which is why you must use a Variant)

These examples only give you the message and don't stop the duplication. They attempt to put the cursor back into [Rev] but the user can still tab or click elsewhere.

To prevent duplicates you must re-do the check before Adding the record and then cancel the operation if duplicate.

But the only sure check is to put a unique index on your table.
S7
Dec 8 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.