CheckBoxes on Form Bound To SQL Server Table

Access 2013, SQL Server Developer 2014

I am new to SQL but have been working with Access for a very long time.  I have the SQL DB defined and some of the tables populated.  I currently have the SQL table linked to the Access front end using the Linked Table manager.

I am creating a new form, bound to a SQL linked table using the form Wizard.

In the wizard I select the linked table fields that I want on the form.  The form is created containing the select fields.  A couple of the fields are defined as 'Bit' in the SQL table.  They show as Yes/No if I look at the table design in Access.

In the past, when using an Access back end DB and the form wizard, type 'yes/no' fields are created on the form as check boxes, which is what I want.

When the form wizard creates the 'bit' fields on the new form, they are created as textboxes.  If I right click on one of these fields and select 'change to', Checkbox is not permitted.  Only 'label', 'listbox' and combobox are allowed.

Either with the form wizard or by some other means, how can I make the SQL 'bit' field appear as, and function properly as a 'checkbox'?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Have you set the default value of those bit fields (in mssql) to zero?
I believe that is necessary.
To resolve the problem with bit data type, do one of the following:
Using SQL Server, open the table in Design view, and assign a default value of 0 (zero) on all bit fields.

NOTE: With this option, you must update records that were entered before this change was made. See the next item for more information.

Using SQL Server, run an Update Transact-SQL statement against the table, and set all bit fields that have a value of Null to a new value of 0 or 1.
PortletPaulEE Topic AdvisorCommented:
By the way.

In mssql a bit field can be NULL or 0 or 1
(3 possible states)
In Access a yes/no is only yes or no

The default could be 1
mlcktmguyAuthor Commented:
I double checked the definition.  All of the bit fields in the table bound to the form I'm creating appear to have a default of 0.  I don't see any [Null].

Each field has this definition in its column properties

Default Value or Binding     ((0))
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

PortletPaulEE Topic AdvisorCommented:
well that's me out then - sorry
mlcktmguyAuthor Commented:
No reason to be sorry, all suggestions are welcome and encouraged
mlcktmguyAuthor Commented:
I was thinking this might be a common thing that Access developers encountered.

Anyone else have ideas?
Access doesn't seem to recognize SQL Server fields as the yes/no data type and so does not automatically create checkboxes.  To do it manually:

Delete the textbox.  
From the ribbon copy the checkbox control and draw it on the form.
Select the checkbox and change its ControlSource to the name of the field you want to bind it to.
Also change the Control name to something meaningful such as chkfieldname.
Change the attached label to a meaningful name.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.