CODEWITHSUNDEEP
ms-accesstextboxvbaboolean
toofaced
toofaced

Reputation: 151

How to display tri-state boolean in a textbox input in MS Access vba?

I rarely use VBA Access for maintaining our legacy application. I am using MS Access 2007. I am trying to use tri-state in a text-box input. In other words, I have a textbox input that is bind to Boolean field (HOME which is bit with allow NULL in SQL Server) in database; however, I need to show 1 not -1 for true when user enter 1. Similarly, there should be 0 for "false" when user enters 0 in database. The last case is: it should save Null in database if user enter nothing or space. What I did so far: Under Property Sheet for this textbox:

  1. Under Format tab, I set Format field to nothing instead of true/false, on/off or yes/no as I need three state.
  2. Under Data tab, bind Control Source to Home field.
  3. Under other tab of property, I set Status Bar Text to 1=HOME, 0=Not HOME

Problem :

  1. When I enter 1, textbox is displaying -1 and it saves 1 in table which is good.
  2. When I enter 0, textbox is displaying 0 and saving 0. So, this case is good.
  3. When I try to enter nothing or space, it takes 0 by default.

Can anyone please tell me how do I achieve the three state for my Boolean text-box? or redirect me to the link here in Stack Overflow because I couldn't find one when I was researching in SO.

Upvotes: 0

Views: 601

Answers (2)

toofaced
toofaced

Reputation: 151

Per reading different online articles and Posts, I believe it is not possible to have tri-state in MS ACCESS. In other words, MS Access will take "NULL" as FALSE and save False/0/No in database. I thought of keeping it in here so that it might be helpful to some other people if they are struggling on it.

Upvotes: 0

Gustav
Gustav

Reputation: 56026

Set the Format property of the textbox to: 0,0 or to: 0;0

Also, set the DefaultValue of the field to Null.

Try adjusting your Sub "up/down":

Private Sub txtHome_AfterUpdate() 
    If Me!txtHome.Value = "" Then 
        Me!txtHome.Value = Null
    End If 
End Sub

Upvotes: 0

Related Questions