Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: Varying Validation Rule

    The section of my database I'm currently having trouble with concerns 3 tables. In it's simplest form the database keeps track of pupil's in a calss, in their marks in various tasks.

    The tables are structured as follows.

    Pupils(Pupil ID, Pupil Name)
    Marks(Pupil ID, Task ID, Mark)
    Tasks(Task ID, Marks Availible)

    There are other fields in these tables, but they are not part of the problem.

    Pupil ID and Task ID are the primary keys in each table they appear in, and table are related by the Pupil and Task ID fields.

    The problem lies in the fact that each task has a different number of marks that can be achieved, and thus the validation rule for the mark field in the marks table cannot be correct for them all.

    I have tried entering the validation rule for each field in the Marks Availible field on the tasks table and then accessing this via a DLookUp in the Validation Rule for the Mark field.

    The DLookUp was as follows
    =DLookUp("[Mark Availible]","[Tasks]","[Task ID] =Tables![Marks]![Task ID]")

    I have recently learnt that you cannot use DLookUp in a validation rule though(although I never got it to work anyway, obviously).

    So basically, is there any other way of doing what I want Access to do?

    Thanks for taking the time to read/attempt to understand/answer this query.

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    I presume you are entering your data through a form. If so, why not use the "BeforeUpdate" event of the Marks field to run a procedure to look up the marks available for the task? If the user enters a mark that is not available, you can generate a message telling the user that and clear the contents of the Marks field. I hope this helps.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Oct 2003
    Posts
    3
    That sounds like a solution that could work, but is there no way of maing it part of the vaidation rule?

    I've drawn a diagram to try and help explain what I'm talking about.

    Basically I want to know how to make it look up the ValidationRule from that table where Task ID = Task ID.

    If there is no solution that does this then I'll use the method you have suggested.
    Attached Thumbnails Attached Thumbnails problem.jpg  

  4. #4
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Alex,

    As you've found out, there is no way to incorporate a Dlookup into a Validation Rule. You are going to have to use another solution, such as the one I suggested. Just out of interest, if you wish to check the property value of a field in a table from code, the syntax would be:

    CurrentDB.TableDefs("NameOfTable").Fields("NameOfF ield").Properties("NameOfProperty").Value

    Good luck!!

    Originally posted by Alex Tennant
    That sounds like a solution that could work, but is there no way of maing it part of the vaidation rule?

    I've drawn a diagram to try and help explain what I'm talking about.

    Basically I want to know how to make it look up the ValidationRule from that table where Task ID = Task ID.

    If there is no solution that does this then I'll use the method you have suggested.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •