I have an issue whereby all of the properties of a field are not exposed at the time the field is created. This isn't really a problem, but more of a nuisance.
For reasons I won't go into now, we have to set the DisplayControl property of a Yes/No field in a table to be Check Box. This is easy enough to do: open the table in design mode, go to the Yes/No field, click the lookup tab and change the Display Control from the drop-down.
The thing is that I'm doing this programmatically. You can recreate this problem like this:
1) Make a new table called Table1.
2) Add some field to it (TestField) and make it anything but a yes/no.
3) Save the table and close design view.
4) Open the immediate window (press Ctrl-G) and type this out:
This will create the yes/no field for you in the table.
5) Now type this in the immediate window:
This will create an error as it can't find a property named "DisplayControl".
6) Now type this in the immediate window:
It will return 20 properties (0-19). This is the problem. The DisplayControl property is #23. To prove this, continue on. 
7) Go to table design on Table1, then to the TestYesNo field we just made, and then click on the Lookup tab and change the Display Control to ComboBox.
8) Save and close the table and go back to the immediate window and type this in again:
It now returns 24 properties available (0-23). Like magic, the "DisplayControl" property now exists and can be altered from code. To prove this, try this line again and it will work:
9) Go back to the Table1 in design mode, look at that field, and its Display Control is now set to a checkbox.
I realize I went into a lot of detail here, but I am just curious if someone knows of a way around this. The reason it's important for me is that we do a lot of table manipulation through code like this, and having 100% automation instead of 90% automation is the goal. As it stands now, we have to manually go back to the tables and set the Display Control to be a CheckBox where appropriate. It's not a big deal, really, but it's just another step for human error to occur.
Again, please don't ask why we need it displayed as a checkbox. I know that DisplayControl only changes how the thing looks in table view and has no bearing on how the data is stored, but for the time being, we need it to be a physical checkbox and not a -1 or a 0. It's minor enough that I'm not raising a stink about it at work, but annoying enough that I'd like to figure it out.
Thanks.
For reasons I won't go into now, we have to set the DisplayControl property of a Yes/No field in a table to be Check Box. This is easy enough to do: open the table in design mode, go to the Yes/No field, click the lookup tab and change the Display Control from the drop-down.
The thing is that I'm doing this programmatically. You can recreate this problem like this:
1) Make a new table called Table1.
2) Add some field to it (TestField) and make it anything but a yes/no.
3) Save the table and close design view.
4) Open the immediate window (press Ctrl-G) and type this out:
Code:
CurrentDb.Execute "ALTER TABLE Table1 ADD COLUMN TestYesNo YESNO;"5) Now type this in the immediate window:
Code:
CurrentDb.TableDefs("Table1").Fields("TestYesNo").Properties("DisplayControl") = acCheckbox6) Now type this in the immediate window:
Code:
?CurrentDb.TableDefs("Table1").Fields("TestYesNo").Properties.Count7) Go to table design on Table1, then to the TestYesNo field we just made, and then click on the Lookup tab and change the Display Control to ComboBox.
8) Save and close the table and go back to the immediate window and type this in again:
Code:
?CurrentDb.TableDefs("Table1").Fields("TestYesNo").Properties.Count
Code:
CurrentDb.TableDefs("Table1").Fields("TestYesNo").Properties("DisplayControl") = acCheckboxI realize I went into a lot of detail here, but I am just curious if someone knows of a way around this. The reason it's important for me is that we do a lot of table manipulation through code like this, and having 100% automation instead of 90% automation is the goal. As it stands now, we have to manually go back to the tables and set the Display Control to be a CheckBox where appropriate. It's not a big deal, really, but it's just another step for human error to occur.
Again, please don't ask why we need it displayed as a checkbox. I know that DisplayControl only changes how the thing looks in table view and has no bearing on how the data is stored, but for the time being, we need it to be a physical checkbox and not a -1 or a 0. It's minor enough that I'm not raising a stink about it at work, but annoying enough that I'd like to figure it out.
Thanks.
