w00tw00t111
asked on
Append DisplayControl Property to Yes/No Field in VBA
Hello Experts!
I have the following function:
This function is passed a recordset that has the list of column names and column types that need to be created in a temp table.
Creation of each column works without a problem. However, when I tried to add code to change and dbBoolean (Yes/No) field to give it the DisplayProperty of a CheckBox (Versus the default of a text box that shows 0 for false and -1 for true) I receive an error message.
The error is on this line:
Set prp = fld.CreateProperty("Displa yControl", dbInteger, acCheckBox)
fld.Properties.Append prp
(specifically the error is thrown when i try to append the property)
Error: 3219, invalid operation
What is odd is if I remove the property code and let the table be created I can manually go into design mode and change the displaycontrol to a checkbox without a problem.
What is the cause of the append error? Am I missing something?
Thank you!
I have the following function:
Public Function CreateTable(strTableName As String, rsTableInfo As DAO.Recordset) As Boolean
'Purpose: Create Table based on input criteria
'Requires: Desired Name and recordset
' Recordset = ColumnName, FieldType
'Returns: True if table is created
'On Error GoTo HandleErr
Dim dbs As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
CreateTable = False
Set dbs = CurrentDb
'First delete table if exists
DropTable strTableName
Set tbl = dbs.CreateTableDef(strTableName)
'Loop through recordset of table particular information
With rsTableInfo
If .RecordCount <> 0 Then 'stuff is here!
.MoveFirst
Do While Not .EOF
Set fld = tbl.CreateField(!ColumnName, !ColumnType)
If !ColumnType = dbBoolean Then 'need to set display property to checkbox
Set prp = fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld.Properties.Append prp
End If
tbl.Fields.Append fld
.MoveNext
Loop
Else
MsgBox "No Recordset data to create table.", vbInformation, "Error: No Data"
GoTo Done
End If
End With
dbs.TableDefs.Append tbl
dbs.TableDefs.Refresh
CreateTable = True
Done:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Function
HandleErr:
MsgBox "Error " & Err.Number & " while creating table" & vbCrLf & Err.Description, vbCritical, "Error Creating Table"
Resume Done
End Function
This function is passed a recordset that has the list of column names and column types that need to be created in a temp table.
Creation of each column works without a problem. However, when I tried to add code to change and dbBoolean (Yes/No) field to give it the DisplayProperty of a CheckBox (Versus the default of a text box that shows 0 for false and -1 for true) I receive an error message.
The error is on this line:
Set prp = fld.CreateProperty("Displa
fld.Properties.Append prp
(specifically the error is thrown when i try to append the property)
Error: 3219, invalid operation
What is odd is if I remove the property code and let the table be created I can manually go into design mode and change the displaycontrol to a checkbox without a problem.
What is the cause of the append error? Am I missing something?
Thank you!
have you tried appending the field to the tabledef before setting the DisplayControl property.
ASKER
Yes and it threw the same error
ASKER
My code effectively mimics both of those posts code however instead of hardcoding the column names I'm referencing the recordsets data.
Still isn't working :(
Still isn't working :(
ASKER
Public Sub test()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim pt As DAO.Property
Set db = CurrentDb
Set tbl = db.TableDefs("tmpExportList")
Set fld = tbl.Fields("TTechnology")
Set pt = fld.CreateProperty("displaycontrol", dbInteger, acCheckBox)
tbl.Fields("TTechnology").Properties.Append pt
db.TableDefs.Refresh
End Sub
Written as a separate sub this code works.If I use the function posted above to create the table and then run this sub, it will change one column to checkbox fields.
However, there are several columns that need to be changed (and each time the procedure is run the number that need to be changed can vary)...
Still trying to pinpoint how to "loop" through the boolean columns and set their properties
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Created solution
so you have tried:
Do While Not .EOF
Set fld = tbl.CreateField(!ColumnNam e, !ColumnType)
tbl.Fields.Append fld
tbl.fields.Refresh
If !ColumnType = dbBoolean Then 'need to set display property to checkbox
Set prp = fld.CreateProperty("Displa yControl", dbInteger, acCheckBox)
tbl.Fields(!ColumnName).Pr operties.A ppend prp
end if
.MoveNext
Loop
Do While Not .EOF
Set fld = tbl.CreateField(!ColumnNam
tbl.Fields.Append fld
tbl.fields.Refresh
If !ColumnType = dbBoolean Then 'need to set display property to checkbox
Set prp = fld.CreateProperty("Displa
tbl.Fields(!ColumnName).Pr
end if
.MoveNext
Loop