April 2, 2012 at 12:22 pm
I have an option group on my access 2007 form with a data type of bit on a backend sql server. The default value is 0 and labeled as "Pending" on the form. The alternate option value is 1 and labeled as "Closed" on the form.
When I change the selection to "Closed" the buttons both show as empty. I have attached three images to demonstrate what is happening.
The database is on a sql server 2008 server and the access database is linked to the database.
Can anyone offer any suggestions as to why this might happen? Thanks.
April 2, 2012 at 1:36 pm
Is the Option Group bound to the correct field (you have the button selected in your screen shot)? That's one possibility - another issue with Access and SQL bit fiels is that you must have the field set to nulls not allowed, and have a default value set. Otherwise you get unpredictable behavior.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 2, 2012 at 2:08 pm
Wendel,
you were correct on the column allowing nulls. I have changed this to
alter table CaseData Alter column CaseStatus BIT NOT NULL
In regards to setting a default value this is done in access when the new record is created.
I attached a result set from the sql server as an image.
The button's behavior hasn't change though. When I click on the "Closed" button it still disappears. I do notice however that the value in the sql server database changes to a 1 from a 0.
Could the issue be a setting on access such as visibility?
April 2, 2012 at 2:35 pm
OK - that's a result of the difference in the way that SQL and Access represent True. In SQL it's a +1 - in Access it is a -1. Change the value for your button to a -1 and see if that solves the problem.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 2, 2012 at 2:43 pm
Bingo!!! You were right on the money. Thanks Wendel!!!!!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply