Form Wizard to add CHECK box (1 Viewer)

[liddlem]

Hi All
My DB has MANY bit fields.
Whenever I use the form wizard to create a form, it creates a text box for each field. Is there a preferences setting (or script) that I can use to change all the text boxes (for bit fields) to CHECK boxes.
Thanks

 

[Ranman256]

no. right click,
change to
check box.

 

[liddlem]

Unfortunately the 'right click' does not offer me the 'checkbox' option. (Checkbox, Toggle and Option buttons are all grayed out.)
I first have to delete the textbox first and then insert a checkbox. Then bind the checkbox to the data.
This is a pain in the proverbial!

 

[sneuberg]

I tried to replicate your problem but when I tried it the controls the Access wizard added were checkboxes but then my table has Yes/No field. What's a bit fields? Is that something from a linked table?

Someone could probably write a script to do this conversion but that's a lot of work. It's too bad arnelgp disappeared. That's just the kind of thing he would be willing to do.

 

[liddlem]

Correct Steve - I'm linked to a SQL DB.

 

[sneuberg]

No promises but I've started working on a script. If all it needs to do is create a checkbox with the same position (left and top) and control source maybe it's not as much work as I thought. If it gives it the same name it will have to rename the text box to something else. If you describe what you've been doing manually I'll try to included those steps it in.

I might be able to get you something tomorrow.

 

[liddlem]

Wow - that would be much appreciated. The same co-ords as the existing box would be fine. I am already having to relocate the fields anyhow.

 

[JHB]

Couldn't you create a query which create a local table and use the local table in connection with the wizard.
And ofcause afterwards delete the local table and query.

 

[liddlem]

Good Thinking 007. . .
I Have Many Such Tables (with The Scripts That Created Them)
So Your Idea Might Just Work Wonders.
I Shall Give It A Go.

 

[sneuberg]

JHB's suggestion will probably work but I went ahead and creating an application to do the conversion anyway. Probably some sort of avoidance behavior on my part. The database is attached.

Everything is done in the TextboxConverter form so if you want to use this all you need to do is import that form into your application. I left a table and some forms (Form1 and Form2) in the attached database so that you could test it.

Most of the code in this is just to select the textboxes. The meat in this is the ConvertTextBoxToCheckbox subroutine and is as follows:

Dim ControlName As String
Dim ctlCheckBox As Control

If ControlExists(ctrl.Name & "_Old", frmName) Then
    Exit Sub
End If
ControlName = ctrl.Name
ctrl.Name = ctrl.Name & "_Old"
Set ctlCheckBox = CreateControl(frmName, acCheckBox, acDetail, , ctrl.ControlSource, ctrl.Left, ctrl.Top)
ctlCheckBox.Name = ControlName
If Me.DeleteTextBoxes Then
    DeleteControl frmName, ctrl.Name
End If

Where

If ControlExists(ctrl.Name & "_Old", frmName) Then
    Exit Sub
End If

is just to make sure the control isn't converted twice.

ControlName = ctrl.Name
ctrl.Name = ctrl.Name & "_Old"

Saves the textbox name and renames it.

Set ctlCheckBox = CreateControl(frmName, acCheckBox, acDetail, , ctrl.ControlSource, ctrl.Left, ctrl.Top)
ctlCheckBox.Name = ControlName

creates the checkbox with the properties of the textbox and give it the name of the textbox.

If Me.DeleteTextBoxes Then
    DeleteControl frmName, ctrl.Name
End If

deletes the textbox if that option is selected in the form.

 

[liddlem]

Sir - You are an officer and a gentleman.
I thank you

 

[sneuberg]

Sir - You are an officer and a gentleman.
I thank you

You're welcome. Actually I am a retired US Air Force captain but I was never much of a gentleman.

 

[Galaxiom]

BTW Access has certain requirements when linked to bit columns in SQL Server.

You must disallow Nulls and have a default value set on the column in the table. Otherwise Access will throw cryptic errors.

 

[Royce]

This is an old thread, but if you set the default value of the YesNo field to 0 or 1 The Access Form Wizards will add the field as a check box to forms. When there is not a default, or the default is No or Yes then it will add it as a Text Box. (Not sure if this is true for SQL backend, but it is for Access backend.)