Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    validation rule (Access 2000)

    I want to set the validation rule in the table products, field stock, by code.The code i have prepared seems not to be right.
    The part of code, which seems false to me is the following:

    ' set additional field properties
    Set fld = tdf.Fields("stock")
    Set prp = fld.CreateProperty("Validation rule > 0"])
    fld.Properties.Append prp

    To my regret i cannot do it properly.What is the exact wording for the validation rule?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: validation rule (Access 2000)

    If you look up CreateProperty in the online help, or in the Object Browser (press F2 to open it), you will see that the syntax is

    Set property = object.CreateProperty(name, type, value, DDL)

    You have tried to put it all into one argument. Instead, use

    Set prp = fld.CreateProperty("ValidationRule", dbText, ">0")

    I have omitted the DDL argument, it is not needed here. And note that the name to be used here is ValidationRule without a space between 'Validation' and 'Rule'.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation rule (Access 2000)

    thanks a lot for the detailed and clever answer

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation rule (Access 2000)

    Thank you so much for your answer. Why is my function not working? I get no message for error when i click
    on the control,but the property is not appended to the field Validation rule in the table.
    What is the reason for that?


    Public Function Validate()
    On Error Resume Next
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Set tdf = dbs.TableDefs("products")
    Set fld = tdf.Fields("branch0")
    Set prp = fld.CreateProperty("ValidationRule", dbText, ">0")
    fld.Properties.Append prp
    dbs.Close
    Set prp = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing

    End Function

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: validation rule (Access 2000)

    Exactly where did you put this function and how are you triggering it?
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation rule (Access 2000)

    super ! Thank you very much !!!!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: validation rule (Access 2000)

    Of course you don't get an error message - you put "On Error Resume Next" at the beginning; this tells Access to ignore all errors. Unless you have a specific reason to do so, it is dangerous to ignore errors.

    So, actually an error did occur: probably that the property already existed. Here is an example procedure SetProperty based on the example in the online help that takes care of all this. You can call it like this:

    SetProperty "Products", "Branch0", "ValidationRule", dbText, ">0"

    <img src=/w3timages/blueline.gif width=33% height=2>

    Public Sub SetProperty(strTable As String, _
    strField As String, strProperty As String, _
    intType As Integer, varValue As Variant)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTable)
    Set fld = tdf.Fields(strField)
    fld.Properties(strProperty) = varValue

    ExitHandler:
    Set prp = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    If Err = 3270 Then ' property doesn't exist
    Set prp = fld.CreateProperty(strProperty, intType, varValue)
    fld.Properties.Append prp
    Resume Next
    Else
    MsgBox Err.Description
    End If
    Resume ExitHandler
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation rule (Access 2000)

    Thank you for your reply.In the OnClick event of a control on the form i have put the following line

    SetProperty "Products", "Branch0", dbText, ">0"

    I then got the message" Compile error. Argument not optional.
    What may be the reason?"

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: validation rule (Access 2000)

    I made a mistake in the example in my previous reply. I have corrected it, but I think it would be a good excercise to try to find the necessary modification yourself. It shouldn't be difficult.

Posting Permissions

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