Access 2007 "Required" field property

[henpecked1]

I'm looking for a line (or more) of VBA that would tell me how to set the "required" property of a field to "No" instead of "Yes"

Just so you have the context, I'm helping someone write a module that imports data into an Access database. Of course, sometimes a field or two is blank. When I look at the properties for the field, the "required" property says yes. If there is a way to turn it off for all fields (except for the key of course), I'd love to know how it's done.

Thanks in advance folks.

[BitBlt]

Oops...needed to edit...see the next post...

[BitBlt]

Here's a code snippet that may help. Plop this into an event procedure or a module subroutine. Of course you'll have to do some science experiments to tailor it to your specific needs, but this should give you an idea of how to do it.

This example iterates through the TableDefs collection, allows you to select which table you want (or in this case, all non-system tables), display the field name and set the Required property as you see fit.

Dim myDb As Database
Dim myTable As TableDef
Dim myField As Field

Set myDb = CurrentDb

For Each myTable In myDb.TableDefs
    If myTable.Attributes = 0 Then           ' Here's where we do our selection.  You can also use the .Name property.
        For Each myField In myTable.Fields   ' Here's where iterate through the fields.  You can do testing to find the Primary Key.
            Debug.Print myField.Name
            myField.Required = True          ' Here's where we set the .Required property for the fields.
        Next myField
    End If
Next myTable

Set myDb = Nothing

Keep in mind that this was written in Access2003, so you may have to tweak a little for Access2007.

Good luck!

[henpecked1]

Okay, just so I understand…1-3 define variables, 5 sets the database, then 7 through 13 roam the fields setting the required property (in your code it's true rather than false), then 14 goes to the next table if there is one and back through the iteration…what does 16 do? I haven't used anything like that before, but I have seen it used in several book examples. Oh, and thank you kindly for the instruction.

[BitBlt]

Line 16 uses the "Nothing" keyword to tell the VB or the VBA engine to release any resources associated with the myDb object. You should use this technique to release object resources every time you are done with some object variable. For the sake of completeness, I probably should have done the same with myTable and myField object variables. My bad...I usually only put it in for object variables that I explicitly issue a "Set" command for.

Oh, and it's not needed for primitive datatypes (and will error anyway :) ).

[henpecked1]

.property ("nullable") = true for 2007