Default Values

[Tony Williams]

Is there anyway that I can globally update the Default value of all my
numerical fields in my table to "0" apart from going to table design and
changing each one individually?
Thanks
Tony

 

[Brendan Reynolds]

Something like this should do it. This assumes local tables - you'd have to
modify it for linked tables.

Public Sub SetDefaultFieldValues( _
ByVal FieldDataType As DAO.DataTypeEnum, _
ByVal NewDefaultValue As Variant)

Dim db As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim strPrefix As String
Dim flds As DAO.Fields
Dim fld As DAO.Field

Set db = CurrentDb
Set tdfs = db.TableDefs
For Each tdf In tdfs
strPrefix = UCase$(Left$(tdf.Name, 4))
If strPrefix <> "MSYS" And strPrefix <> "USYS" Then
Set flds = tdf.Fields
For Each fld In flds
If fld.Type = FieldDataType Then
fld.DefaultValue = NewDefaultValue
End If
Next fld
End If
Next tdf

End Sub

Public Sub TestSetDefaultFieldValues()

On Error GoTo ErrorHandler
DoCmd.Hourglass True
SetDefaultFieldValues dbByte, 0
SetDefaultFieldValues dbCurrency, 0
SetDefaultFieldValues dbDecimal, 0
SetDefaultFieldValues dbDouble, 0
SetDefaultFieldValues dbInteger, 0
SetDefaultFieldValues dbLong, 0
SetDefaultFieldValues dbSingle, 0
DoCmd.Hourglass False
MsgBox "Done!"

ExitProcedure:
Exit Sub

ErrorHandler:
DoCmd.Hourglass False
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProcedure

End Sub

 

[Tony Williams]

Brendan that looks amazing. BUT? How do I run it? I'm a beginner with VBA,
do I create a module with this code but how do I then run it to change all
the values.?
This wont change any data that is already in the fields will it?
Thanks
Tony

 

[Brendan Reynolds]

If this is a one-off operation, then after pasting the code into a standard
module you could execute it simply by typing the name of the procedure in
the Immediate Window. Hold down the Ctrl key and press 'g' to go to the
Immediate Window, type the name of the procedure (TestSetDefaultFieldValues
in my example) and press the Enter key.

If you want to provide a more user-friendly way of executing the code, you
could call the procedure from the Click event of a command button. Place a
command button on a form. If a dialog box with the caption 'Command Button
Wizard' pops up, press Cancel. Give the button a meaningful name.
Right-click the new command button and choose 'Build Event'. If a 'Choose
Builder' dialog pops up, choose 'Code Builder'. A procedure stub that looks
like this will be created for you ...

Private Sub Command1_Click()

End Sub

(Where 'Command1' is the name of your command button.)

Enter the name of the procedure between these two lines ...

Private Sub Command1_Click()

TestSetDefaultValue

End Sub

The code makes no changes to existing data, and should not, as far as I
know, cause you any problems. It is, however, always a good policy to make a
backup copy of your application and to use a test copy of your data when
executing any new code for the first time..