I have 20 tables with hundreds of fields that have every field set to
Required: "Yes".

Does anyone have a script I could run to set all these fields to not
required? Going through one by one is getting really old, really
fast.

Thanks so much,

Laura

In design view, you can use DAO to step through the Tables Collection, and
in each table, step through the Fields Collection and set the property of
each field to not required. Googling or searching MSDN.microsoft.com or
support.microsoft.com would likely turn up an example of running the
collections.

Larry Linson
Microsoft Office Access MVP

"musicloverlch" <lhowey (AT) gmail (DOT) com> wrote

"musicloverlch" <lhowey (AT) gmail (DOT) com> wrote

The following VBA code, in a Standard Module, steps through the Tables
Collection, checks and prints (to the immediate window).

You will notice however, that I have commented out the code that would set
the Required property to False -- that is because there are limitations on
resetting some Properties of Objects in a Collection once the Object has
been added to the Collection - unfortunately the Required property appears
to be one of those (that was not abundantly clear to me from the Access 2003
Help, which you can see by copying this code into a Standard Module, putting
the cursor on the word Required and pressing F!).

I suspect that you'd have to remove the Field object from the Fields
Collection, change that Property, and re-Append it. You might have to re-set
all the other Properties of the Field... but the code could be a little
un-obvious. I'm sorry but I just don't have time / energy to pursue this any
further than I already have.

Public Function ChkSetReqd() As Long
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim lngTCount As Long

Set db = CurrentDb
For Each tbl In CurrentDb.TableDefs
lngTCount = lngTCount + 1
If Left(tbl.Name, 4) <> "MSys" Then 'Skip System Tables
Debug.Print tbl.Name & " " & tbl.Fields.Count & " Fields"
For Each fld In tbl.Fields
Debug.Print fld.Name & " Reqd = " & fld.Required
' If fld.Required = True Then
' Set fld.Required = False
' End If
Next fld
End If
Next tbl

Set db = Nothing
ChkSetReqd = lngTCount
End Function

Larry Linson
Microsoft Office Access MVP

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote


Allen Browne has DAO code examples on his website:

http://allenbrowne.com/func-DAO.html

I don't recall where I picked this up from, but between this function to
dis-allow zero length strings and the other information on Allen's site
you should find what you need.

Attribute VB_Name = "AllenBrowne"

Option Compare Database

Option Explicit

Function FixZLS()

Dim db As DAO.Database

Dim tdf As DAO.TableDef

Dim fld As DAO.Field

Dim prp As DAO.Property

Const conPropName = "AllowZeroLength"

Const conPropValue = False


Set db = CurrentDb()

For Each tdf In db.TableDefs

If (tdf.Attributes And dbSystemObject) = 0 Then

If tdf.Name <> "Switchboard Items" Then

For Each fld In tdf.Fields

If fld.Properties(conPropName) Then

Debug.Print tdf.Name & "." & fld.Name

fld.Properties(conPropName) = conPropValue

End If

Next

End If

End If

Next


Set prp = Nothing

Set fld = Nothing

Set tdf = Nothing

Set db = Nothing

End Function

?


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Thanks everyone! I'm zipping through now!


On Mar 4, 2:20*pm, "Clif McIrvin" <clare.nom... (AT) gmail (DOT) com> wrote: