find and replace

[slimjen1]

using access 2010. Is there any code that could search the properties of the table in a database mainly the default value field and change it from for example 2300 to 2400. Database has two many tables to open each and change. Thanks

 

[PeterF]

For default values the code below should get you started.

Function ChangeDefaultValueInTables()

Dim tblDefs As TableDefs
Dim tbl As TableDef
Dim fld As Field
Dim ChangeCounter As Integer
Dim OldValue
Dim NewValue

OldValue = "2300" 'if its a nummeric field remove the quotes
NewValue = "2400"

ChangeCounter = 0

    Set tblDefs = CurrentDb.TableDefs
    For Each tbl In tblDefs
        For Each fld In tbl.Fields
            If fld.DefaultValue = OldValue Then
                fld.DefaultValue = NewValue
                ChangeCounter = ChangeCounter + 1
            End If
        Next
    Next
    MsgBox "Fields changed " & ChangeCounter

    Set tblDefs = Nothing
End Function

 

[Cronk]

On the other hand, you could change the default value of a bound control on a form.

 

[slimjen1]

Thank you for responding. I will try this code. As for the changing in the form; I would have to tie all the tables to forms which would still take time???

 

[Galaxiom]

Such a requirement suggests inappropriate data structure.

It would be better to make a table of the defaults and change the DefaultValue of the controls in the forms so that it is looked up when loading. Otherwise you will be doing this all again some time.

 

[slimjen1]

Database will be revamped so its a one time thing. Thanks again for your help.