How do I change imported field properties with code?

[chdavisjr]

I have a table created by importing a large delimited text file.
There are multiple Yes/no fields (defined on the import screen).
Currently, I open the table in design view, click on the yes/no field in the field list, F6 to Field Properties, and change the Format (General tab) to Yes/No (it is blank) and then under the Lookup tab to CheckBox (it shows textbox). I defined the fields as Yes/No on import.
Is there an easier way to do this with code than to manually opening each of the hundreds of text files and doing the above for each of the 100 or so yes/no boxes?
I would like the code to loop thru all the fields (approx 200) and if the data type=yes/no, then set the field properties as outlined above. I am new to programming access.
Thank you in advance.
Chalmers Davis

 

[chdavisjr]

here is an example of the db:
TxtFld1, TxtFld2, YNfld1, YNfld2, TxtFld3, YNfld3
Can the code be made to loop from the first field to the last field of the table, and if
1) the field is of data type=Yes/No, (YNfld1, YNfld2, Ynfld3)then
2) set the DisplayControl =checkbox or 106 and
3) set the format=Yes/No?

There are around 100 yes/no fields, and each of the many imports will have different field names.
Thanks again,
Chalmers

 

[cmmrfrds]

You can do it with the DAO object tabledef. Here is a thread that deals with it there are many others. That should get you started.

thread181-27567

 

[chdavisjr]

Thank you, cmmrfrds ... some good stuff there, but unfortunately not what I need ... When I import these text delimited files, the fields have T or F and I manually set them to Yes/No fields on the Import screens in ACC2K.
The problem comes in AFTER import has finished.
Currently, I open the table in "Design mode"; select each YesNofld and set its properties to: Format="Yes/No" and Lookup Display Control="Check Box". I would like to know how to write the code to (OR a macro to) to simplify:
1) loop through the table structure from the first field
to the last; IF the field is DataType=Yes/No THEN
2) Change the Lookup "DisplayControl" from:
"Text Box" to "Check Box" (or 106) and
3) Set the "Format"=Yes/No?
ELSE skip the next field data type and follow steps 1 to 2

There will be many of these types of text files to import ... all different but with at least 100 of these Yes/No fields that will have to be changed to Check Boxes.
Chalmers

 

[cmmrfrds]

Okay, you don't want to change the field type but instead the data in a certain type of field?? When you return a resultset there is a fields collection available where you can determine a fields type, value, etc.... First while in some access vba code,look through the field and fields objects to see the data type you will be checking for.

1. create a public function in the standard module.
2. create a connection, recordset, etc to read the table. make the recordset properties updatable.
3. set up a loop to loop through all the records.
4. set up an inner loop using the fields collection to check each fields type. there is a count on the fields collection so you can determine dynamically how many fields in the collection - upper index value on loop, minus 1 since it is zero based.
5. in the inner loop apply the changes to the records.
6. call the function from a form or macro.