|
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
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?
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”
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
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”
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
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Notifications
