Change table field property (1 Viewer)

frankt68 · Apr 23, 2014

Hello!

First let me say that I am using Access 2010 and I do not have much experience using it.
I need advice on how to change the property of a field in a table programmatically.
I have a table in which one field has Required property set to "Yes". I would like to set this property by using VBA code to "No", then add data into a table using a query and re-set the Required property to "Yes".

Is this possible and if so, how can I do it?

Regards,

Frank

:banghead:

Cronk · Apr 23, 2014

Why would you want to do this? I can't think of a situation requiring this.

frankt68 · Apr 24, 2014

Cronk said:
Why would you want to do this? I can't think of a situation requiring this.

Maybe it is a bit unusual . Let me explain .

I have a data table (eg Table 1) and the form for adding and editing records in this table. Certain fields in this table must be filled in, so for this fields the Required property is set to "Yes". This ensures that all required fields are filled.
Certain records in this table should be added from another table (eg, Table 2) , but this table does not contain all the information required in Table 1. For Table 2 I get data from another software application.
As the data in certain fields in Table 2 are missing , I have to manually set the Required property to "No" to add records in Table 1 by using an append query (if I do not do this , the data will not be added) , and after adding records again manually re-set it back to "Yes ".
So I would like to make users may download data from Table 2 in Table 1 and then just enter the missing data in Table 1, because this would greatly facilitate the work . So I'm looking for a way to change field propertiy Required with some VAB code.

Fran Lombard · Apr 24, 2014

Here's some vba code that should do what you are looking for.
You can place this code in a module and call from anywhere.
Just pass to it the TableName, FieldName and New Setting (True or False)
You can call this at the beginning of your Append Process with setting False
then again at the end with the setting True to re-set

Code:

Public Sub ChangeFieldPropoerty(ByVal sTableName As String, ByVal sFieldName As String, ByVal bNewSetting As Boolean)
    Dim myTblDefs As TableDefs
    Dim myTblDef As TableDef

    Dim myFields As Fields
    Dim myField As Field
    
           Set myTableDefs = CurrentDB.TableDefs
    
    'Loop through TableDefs and Get Each Table
        For Each myTableDef In myTableDefs

        'Get Your Table of Interest
            If myTableDef.Name = sTableName Then
               
              'Set local Variable to Fields Collection of Current Table
                Set myFields = myTableDef.Fields
                
                'Loop through Fields Collection and Get your Field of Interest
                For Each myField In myFields
                        If myField.Name = sFieldName Then
                          myField.Required = bNewSetting
                        End If
                Next myField
            End If
            
        Next myTableDef
    
    
End Sub

Hope this helps
Fran

Cronk · Apr 24, 2014

frankt68,

Fran gives you a vba solution which seems to exactly answer your query.

However, I wonder about your system process. It seems to me, you have required fields in Table1, you want to import data from Table2 that does not have data in some fields, required in Table1.

So you set Required off, import the data, fill in the blank fields (which you must do) before resetting Required back on.

I would import from Table2 via an append query that puts in dummy data (such as "***Data to be added") in blank fields that you change after the import.

But whatever rocks your boat.

Fran Lombard · Apr 24, 2014

I agree with Cronk. Constantly changing properties of table definitions to support an on-going requirement of a business process screams for a need to change to the process.
Usually data should pass all validation requirements before being added to permanent storage.

Consider adding these fields to table 2, provide the users a screen to maintain these fields then when the data is ready to pass validation import to table 1.

frankt68 · Apr 25, 2014

Thank you both, Cronk and Fran for your advice and code.

Is a bit silly that I did not thought of putting in dummy data.

I will try it as sun as I'm back from my vacation

.

Change table field property (1 Viewer)

frankt68

Registered User.

Cronk

Registered User.

frankt68

Registered User.

Fran Lombard

Registered User.

Cronk

Registered User.

Fran Lombard

Registered User.

frankt68

Registered User.

Similar threads

Users who are viewing this thread