Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-23-2014, 04:46 AM   #1
frankt68
Newly Registered User
 
Join Date: Mar 2012
Posts: 50
Thanks: 14
Thanked 0 Times in 0 Posts
frankt68 is on a distinguished road
Change table field property

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


frankt68 is offline   Reply With Quote
Old 04-23-2014, 11:36 AM   #2
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,162
Thanks: 3
Thanked 471 Times in 464 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Change table field property

Why would you want to do this? I can't think of a situation requiring this.
Cronk is offline   Reply With Quote
Old 04-23-2014, 11:51 PM   #3
frankt68
Newly Registered User
 
Join Date: Mar 2012
Posts: 50
Thanks: 14
Thanked 0 Times in 0 Posts
frankt68 is on a distinguished road
Re: Change table field property

Quote:
Originally Posted by Cronk View Post
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.

frankt68 is offline   Reply With Quote
Old 04-24-2014, 12:16 PM   #4
Fran Lombard
Newly Registered User
 
Join Date: Mar 2014
Location: Shelton, Ct. USA
Posts: 65
Thanks: 12
Thanked 16 Times in 16 Posts
Fran Lombard is on a distinguished road
Re: Change table field property

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
Fran Lombard is offline   Reply With Quote
Old 04-24-2014, 01:04 PM   #5
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,162
Thanks: 3
Thanked 471 Times in 464 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Change table field property

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.
Cronk is offline   Reply With Quote
Old 04-24-2014, 01:31 PM   #6
Fran Lombard
Newly Registered User
 
Join Date: Mar 2014
Location: Shelton, Ct. USA
Posts: 65
Thanks: 12
Thanked 16 Times in 16 Posts
Fran Lombard is on a distinguished road
Re: Change table field property

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.
Fran Lombard is offline   Reply With Quote
Old 04-24-2014, 10:49 PM   #7
frankt68
Newly Registered User
 
Join Date: Mar 2012
Posts: 50
Thanks: 14
Thanked 0 Times in 0 Posts
frankt68 is on a distinguished road
Re: Change table field property

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 .


frankt68 is offline   Reply With Quote
Reply

Tags
change , field , property , table

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Can't change required field property... jlnewnam General 4 07-16-2013 08:26 AM
Line Visibility property change based on field value virencm Reports 4 05-22-2012 08:01 PM
Question Field Property Change Bremen217 General 2 02-23-2011 10:01 AM
ADOX - change 'Required' property of new field dleake Modules & VBA 0 07-13-2006 02:47 AM
Field property 'Required' is YES in table... Carl_R Tables 4 09-25-2002 12:16 AM




All times are GMT -8. The time now is 03:26 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World