 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / February 2005Validate form data
I would like to validate data in certain fields on a form using a validation rule (or any other method) so the error message appears when the field loses the focus, rather than waiting for the referential integrity of the relationship to catch the error when attempting to add the record. For example: I have a "User" table that that has a one-to-many relationship to a "Scan" table on the user id field. The user id field will accept any input that meets the size and data type criteria. However, I want an error message to appear if the value does not exist in the related table at the time of input, rather than waiting for all other fields to be populated. Any ideas are greatly appreciated! Dave "David Bowling" <David Bowling@discussions.microsoft.com> wrote: >I would like to validate data in certain fields on a form using a validation >rule (or any other method) so the error message appears when the field loses [quoted text clipped - 7 lines] >appear if the value does not exist in the related table at the time of input, >rather than waiting for all other fields to be populated. You can use the control's BeforeUpdate event to run some code. The code could use DLookup to search the other table and do a MsgBox if it's not found. The BeforeUpdate event provides a Cancel argument that you can set to True to prevent the focus from moving to another control until a valid value is entered or the user hits Esc to undo the entry. With most UI designs, this is all quite unnecessary as far as your example goes. By making the scan table's form a subform of the user table's form, the LinkMaster/Child properties would natually lead to only valid entries.  SignatureMarsh MVP [MS Access] Marshall, Thank you for your help and the quick response time! I understand everything you are telling me but I am having trouble with the syntax of the DLookup function. My form (called "Scan Form")is based off a query called Q_Scan which has the Scan table and User table joined with a one-to-many relationship on user id. [Scan]![S_User] and [User]![Id] I am trying to validate that S_User exists in the User table. I reviewed the help on Dlookup and have tried a multitude of scenarios with no success. Thanks again! Your help is greatly appreciated! > "David Bowling" <David Bowling@discussions.microsoft.com> > wrote: [quoted text clipped - 23 lines] > subform of the user table's form, the LinkMaster/Child > properties would natually lead to only valid entries. Assuming the ID field is a numeric type, I think I would do it like this: If DCount("*", "User", "ID=" & Me.S_User) = 0 Then MsgBox "Invalid user ID" & vbCrLf & "Try again" Cancel = True End If But if you prefer: If IsNull(DLookup("ID", "User", "ID=" & Me.S_User)) Then . . . SignatureMarsh MVP [MS Access] >Thank you for your help and the quick response time! >I understand everything you are telling me but I am having trouble with the [quoted text clipped - 32 lines] >> subform of the user table's form, the LinkMaster/Child >> properties would natually lead to only valid entries. Marshall, I used the following syntax as a condition in a macro. DLookUp("Id","User","Id= [Forms]![Scan Form].[S_User]") Is Null. This works great!. The macro allows me to open a form (with a timer) to display a user friendly error message, then close the form, change the focus back to user id field and clear the invalid data. This way, the fork-lift operators will not have to get down off their truck to use the mouse to clear an error. This really makes the interface user friendly! Thanks again. You rock! Dave > Assuming the ID field is a numeric type, I think I would do > it like this: [quoted text clipped - 44 lines] > >> subform of the user table's form, the LinkMaster/Child > >> properties would natually lead to only valid entries. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.