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,373 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

752 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