Solved

MS Access VBA code to change an already existing field from text to boolean and display checkbox within table?

Posted on 2010-08-31
5
1,232 Views
Last Modified: 2013-11-28
Could someone please provide a sample of VBA coding that converts an already existing field from text to yes/no and it has to be able to change the display control lookup to a check box within the table?  The field name in particular is called Data.

Thank you in advance.
0
Comment
Question by:stephenlecomptejr
  • 3
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 33565792
Is there any data in the text field?  Anything which is not Yes or No (Or True or False) will be lost.

Changing the display control in the field definition will not change the control on any existing forms or reports.  They will stay as textboxes, and will just show -1 or 0 instead of Yes or No.

Are you OK with that outcome?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 33565801
..and I suppose there is a Q as to why you need to do this in code rather than through the UI?
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33566277
I'm not concerned about losing the data nor the change affecting existing forms since none use the field yet.

I have 100+ databases that all have the same table/field and already have scripts to loop through the listing - thus I'd rather use the code.

The table name by the way is PROJ_ME....I don't know why they named it that but it is what it is.
I'm not even sure if Data is one of the fieldnames that's a no-no.

Anyway this is a lot of typing for an explanation - I appreciate your concern about the results but I've asked a lot of questions before for VBA code and merely received the listing.

Unless you are stalling for time to produce it?  =)  just kidding around.

When you do a search a lot comes up but rarely the code for the conversion from text to check box display.   I think the only means to do it is in ADOX.  Am I correct?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 33567312
This is a sample function to do the change.  It is all DAO.

The reason for the delay was that although I thought  knew how to do this, I've not been able to get this to work in Access 2010 which is my main environment now. I can't see why that is at the moment.

I had to startup an A2003 session and it works OK in Access 2003.

Function ChangeYesNoField()
'This function use a table named table1.
'It changes a field called Field1 to a YesNo field, with the display control as a check box.

'***Modify the names where shown.

Dim db As DAO.Database
Dim t As TableDef
Dim f1 As DAO.Field

Dim p As DAO.Property

Set db = CurrentDb

'CHANGE THE FIELD TYPE
db.Execute "Alter table Table1 alter column Field1 YesNo;"  'modify

'NOW CHANGE THE DISPLAY CONTROL
Set t = db.TableDefs("table1")    ' modify
'Create the ID field.
Set f1 = t.Fields("field1")    'modify

' delete current display control property
f1.Properties.Delete ("Displaycontrol")

'Set the DisplayControl property to Check Box.
Set p = f1.CreateProperty("DisplayControl", dbInteger, 106)
f1.Properties.Append p
Set p = Nothing
Set db = Nothing
End Function
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 33568227
here is how to alter an existing field..
pass the name of the table and field as string in the sub


Sub changeFieldType(sTable As String, sField As String)

On Error Resume Next

Dim p As DAO.Property, db As DAO.Database

Set db = CurrentDb

db.Execute "alter table [" & sTable & "] alter column [" & sField & "] YesNo"



'delete the existing "DisplayControl" property

db.TableDefs(sTable).Fields(sField).Properties.Delete "DisplayControl"

'create the new Displaycontrol property

Set p = db.TableDefs(sTable).Fields(sField). _

        CreateProperty("displaycontrol", dbInteger, acCheckBox)



db.TableDefs(sTable).Fields(sField).Properties.Append p



End Sub

Open in new window

0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now