Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1629
  • Last Modified:

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

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
stephenlecomptejr
Asked:
stephenlecomptejr
  • 3
1 Solution
 
peter57rCommented:
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
 
peter57rCommented:
..and I suppose there is a Q as to why you need to do this in code rather than through the UI?
0
 
stephenlecomptejrAuthor Commented:
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
 
peter57rCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now