Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Default Values

Discussion in 'Information Technology' started by Tony Williams, Jul 28, 2006.

Tags:
  1. Tony Williams

    Tony Williams
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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

    --
    Brendan Reynolds
    Access MVP

    "Tony Williams" <tw@invalid.com> wrote in message
    news:eWn%23X0UkGHA.3848@TK2MSFTNGP04.phx.gbl...
    > 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
    >
     
  4. Tony Williams

    Tony Williams
    Expand Collapse
    Guest

    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" <brenreyn@discussions.microsoft.com> wrote in message
    news:%23L2BpKVkGHA.4284@TK2MSFTNGP05.phx.gbl...
    > 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
    >
    > --
    > Brendan Reynolds
    > Access MVP
    >
    > "Tony Williams" <tw@invalid.com> wrote in message
    > news:eWn%23X0UkGHA.3848@TK2MSFTNGP04.phx.gbl...
    >> 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
    >>

    >
    >
     
  5. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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..

    --
    Brendan Reynolds
    Access MVP

    "Tony Williams" <tw@invalid.com> wrote in message
    news:uTp5bXVkGHA.5036@TK2MSFTNGP04.phx.gbl...
    > 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" <brenreyn@discussions.microsoft.com> wrote in message
    > news:%23L2BpKVkGHA.4284@TK2MSFTNGP05.phx.gbl...
    >> 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
    >>
    >> --
    >> Brendan Reynolds
    >> Access MVP
    >>
    >> "Tony Williams" <tw@invalid.com> wrote in message
    >> news:eWn%23X0UkGHA.3848@TK2MSFTNGP04.phx.gbl...
    >>> 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
    >>>

    >>
    >>

    >
    >
     

Share This Page