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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

685 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