?
Solved

Access Form

Posted on 2011-10-09
14
Medium Priority
?
333 Views
Last Modified: 2012-05-12
I have a value that I want to set in the record set of a form.  The default value is null, however I want the user to be able to select "Y" and have that value store in the table.

The table is tblclaim1


What type of control should I use on the form?
0
Comment
Question by:seamus99
  • 8
  • 5
14 Comments
 
LVL 75
ID: 36939255
Well, to 'select'  ... that would imply either a Combo box or check box.  But a check box connected to a  Yes/No field will store a Boolean True or False.  Is this what you want?

mx
0
 
LVL 75
ID: 36939260
Are there any other options besides "Y" ?  If not, a combo box probably does not make sense.

I would use a Check Box ... and store True

mx
0
 

Author Comment

by:seamus99
ID: 36939301
The value should be either True or Null.


So how do I use the checkbox?  Do I have to have an event created after they click it?

can you provide an example please.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 36939311
Well first ... the value is going to be True or False with a Check box.  If that's not cool, then you will need to use a Text box.

In either case, you would set the Control Source to the Field Name in your table you want to store this value.  No code is required to do this.

mx
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36939319
<< or Null. >>

Why null?

A check box bound to a yes/no field in your form's recordsource will store true (-1) or false (0) or null if the field permits it and you have a triple-state checkbox.

The update is automatic when the user checks or unchecks the box, with true appearing "checked".
False appears "unchecked"
Null where it is used appears "gray"
0
 
LVL 75
ID: 36939328
I think there are some issues with Tri State.  I've never quite seen that work right.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36939331
Btw, if the "Y" or Null format is critically important for reporting purposes (or other reasons), this data can always be formatted to appear the way you want it to appear.

However, for storing and entering the data, a Yes/No field with a checkbox is the simplest choice for two-state data like this.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36939339
< I've never quite seen that work right. >

Any specific examples?  I have never had any issues with it.
0
 
LVL 75
ID: 36939351
So how do I use the checkbox?  
See Image

Do I have to have an event created after they click it?
No.

can you provide an example please.
See image.
Capture1.gif
Capture2.gif
0
 
LVL 75
ID: 36939373
"Any specific examples?  I have never had any issues with it."

Well ... I once wrote a response to this article:
http://www.databasedev.co.uk/triplestate_checkbox.html

which was: (and maybe I'm missing something)

"
I just wanted to note, respectfully … that unless I am missing something,  the results when you click the ‘Run Query’ button are identical whether or not the Triple State property of the check box is set to Yes or No.  

IE, it you set that property to No, save the form, close the form then reopen … the check box is still grayed out and is in a ‘Null’ state (until the first time it is clicked).  If you click the Run Query button, you get all members in the Membership table.

It turns out that this:

"A normal check box can have only values of 0 (unchecked) or -1 (checked) corresponding to Yes/No or True/False or On/Off”

is not the case.  At the Form level, a Yes/No Field check box always initially has a Null default value and will be grayed out, unless a default value is explicitly set when the form loads.

The real problem stems from the fact that at the table level, there is no Null state when a Yes/No data type field is created, as there is when other data types are initially created, such as a Text, Date, Numeric, etc.  The initial default state of a Yes/No data type is False.
 
If you add another Y/N field to the Membership table, call it TestYN … then run this query:


SELECT tblMembership.lngMemberID

FROM tblMembership

WHERE (((tblMembership.TestYN) Is Null))

.. you get zero results, because the field is False for all records.

I’ve never understood Microsoft’s implementation of the Triple State check box."

mx

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36939407
Okay - Its been a while since I've used one, but a triple state checkbox, in order to work has to be bound to an integer field (not Y/N, which is a special case of a numeric, integer field) - it does not work bound to a Y/N field.  If it is implemented correctly (bound to the correct field type), it will store -1, 0, or null and nothing else., and queries like that one will work as expected.

<can you provide an example please. >

Here's another example, Form1 shows a two-state checkbox bound to a Yes/No field and a Tri-state checkbox, which has true, false and null as values.

YNField.mdb
0
 
LVL 75
ID: 36939441
"in order to work has to be bound to an integer field (not Y/N, which is a special case of a numeric, integer field) -"

That's cheating :-)

mx
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36939448
Lol!

Cheating is highly underrated.
0
 
LVL 75
ID: 36939462
Allen Browne doesn't like Y/N Data types anyway.

mx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question