CheckBoxes on Form Bound To SQL Server Table

mlcktmguy
mlcktmguy used Ask the Experts™
on
Access 2013, SQL Server Developer 2014

Background
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'?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
https://support.microsoft.com/en-gb/kb/280730
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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

And
The default could be 1

Author

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))
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
well that's me out then - sorry

Author

Commented:
No reason to be sorry, all suggestions are welcome and encouraged

Author

Commented:
I was thinking this might be a common thing that Access developers encountered.

Anyone else have ideas?
Distinguished Expert 2017
Commented:
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.

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial