Default Value for number fields 1

[shyamsundar]

Hi techies,

I have a database which is having around 20 tables populated with around 1400 fields. Around 600-700 fiels of number type fields. The default value for these fields are 0. But in our application 0 also a value. Hence we decided to put -9 as default value for all numeric fields. Now anybody can suggest me do this. Doing it manually may be difficult. Is it possible programmatically to identify the field type and replace the default value by -9.

Thanx



Shyam
cwizshyam@yahoo.com

 

[RamziSaab]

this will go through all tables in ur dabatase and change and dbdouble field (asuuming that is ur number field) to have a defaulvalue of -9....however if u are using a naming convention e.g. tbl .... i would add another if statement to make sure it does not change an sys and hidden tables...

Make a form with a button and copy the code.

Tell me if u need more help and if this works

Private Sub Command0_Click()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

For Each tbl In CurrentDb.TableDefs
For Each fld In tbl.Fields
If fld.Type = dbDouble Then

SetFieldProperty fld, "DefaultValue", dbInteger, -9
End If
Next fld
Next tbl
End Sub



Public Sub SetFieldProperty(ByVal fld As DAO.Field, ByVal strPropertyName As String, ByVal iDataType As DAO.DataTypeEnum, ByVal vValue As Variant)
Dim prp As DAO.Property

Set prp = Nothing

On Error Resume Next
Set prp = fld.Properties(strPropertyName)
On Error GoTo 0

If prp Is Nothing Then
Set prp = fld.CreateProperty(strPropertyName, iDataType, vValue)
fld.Properties.Append prp
Else
prp.Value = vValue
End If
End Sub

 

[shyamsundar]

raamzisaab..

Wow!!!! it works..with little modification...not in fieldproperty sub....thanx a log

Shyam

Shyam
cwizshyam@yahoo.com