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,294 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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

806 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