Form (Combo Box)

[lhooker]

I have a 'Yes/No' field in a table with no value for the 'Default Value' of the table definition. This field is bounded to a combo box of a form. When tabbing through the form the combo box insert 'No' automatically. If I blank out the value 'No', it comes back. I can override it with 'Yes', but I would like the user to select one or the other (with no value initially). If no selection, then I will generate a pop-up error message to alert the user of no value selected.

 

[isladogs]

You have set the combo box to have a default value = No
In design view, go to the combo box property sheet and clear the default value

 

[lhooker]

ridders,

The combo box and table both have no value.

 

[Gasman]

So it is set in code?

 

[Uncle Gizmo]

Use an Option Group ...

 

[isladogs]

It has to be set somewhere for this behaviour to occur.
Suggest you strip down and upload your db.

 

[lhooker]

OK, I copied and stripped down my database. I'm getting the same results as soon as I enter data and tab down. The code for 'After Update' should not cause the problem. Uncle Gizmo, 'Option Group' ?


'Test' database uploaded.

 

[Uncle Gizmo]

THIS ANSWER IS A BIT WRONG ... (just a small tinsy winsy bit) SEE HERE

The problem is you have a field in your table which is recording boolean values yes or no... I think it records - 1 and 0 without looking it up. Now the trouble is the table field DISPLAYS "yes" and "no", however that's MS Access trying to be helpful as usual and messing you right up! You are providing the values "yes" and "no" with your combo box and you are feeding those values (text) to the table but the table field is "Boolean" and won't accept the text.

To correct the problem you have several options you could change the table field to accept text.

You could link your combobox to a table which offers you two choices, yes and no and Returns -1 and 0 if that's correct (I haven't checked yet!)

You could leave your table field as a boolean field and fill in the values with an option group instead of a combobox.

 

[Uncle Gizmo]

In this video I demonstrate how to create an Option Group. Go to time index 2 minutes 40 seconds

VBA Beginner - Opening Another Query Nifty Access
https://youtu.be/eX8-aTmUdOM?t=2m40s


However you will have to take an extra step from that shown in the video because the option group in the video is not bound to a table a field in your table it's just stand alone on the surface of the form for gathering information. If you need instructions on how to connect the Option Group to a field, express your requirement in this red and I'm sure someone will help you.

That video is one of a set of twelve videos in a playlist that takes you from a simple button on a form up to creating queries out of text strings in VBA...

VBA Beginner - Nifty Access
https://www.youtube.com/playlist?list=PLhf4YcS5Ajdpjhlv5w4uNi5QSAQSKU7YC

 

[Uncle Gizmo]

Opp's --- I just had another look and I can see the table field is set to a combo box! It is actually collecting the yes/no values for you. I assumed you'd made the combo box yourself --- my mistake!!!! So the combobox is working correctly...

Lets just say, I was giving the right answer to the wrong problem!!!

I will go away and sulk now...

 

[lhooker]

Uncle Gizmo, Pat Hartman,

I changed the 'Data Type' from 'Yes/No' to 'Short Text'. The default value of 'No' is no longer appearing when tabbing through the form. I'll work on moving the validation code to the 'Before Update' 'Event Procedure'. Thank you for the solutions to this error ! ! !

 

[lhooker]

Pat Hartman,

If you're talking about 'DLookup'. I'm not using it. What are you talking about ? I'm just referring to fields in the 'Test' table.

 

[Uncle Gizmo]

Just to elaborate on Pat's reply... Microsoft are trying to make MS Access "More Helpful" they are trying to make it do more for you.. Microsoft keep adding "improvements"... In this particular case you have a boolean field in your table a field for storing yes/no values. It does not store "yes" or "no" as Text, it stores "BOOLEAN" in the format of -1 and 0 ... Microsoft have obviously added some extra code so that when you select a combobox as a format for your data ( in the table) MS Access provides you with a working combobox. This combobox provided by Microsoft allows you to select text values and have these text values automatically converted to boolean data suitable for the underlying table.... I didn't know Microsoft had added this functionality, hence my confusion in my first answer, I assumed you had added the functionality yourself. However this "automatic helpfulness" can mislead you into thinking that a table field is storing text when it is actually storing a boolean value. You might write code to detect the text "yes" or "no" instead of a boolean value. This would cause problems for you! You are much better off, and it is much safer if you can look into the table and see the actual true values which are being housed by the fields in the tables... The confusion caused in this thread should be enough to demonstrate the truth of this!

 

[lhooker]

Uncle Gizmo,

Thank you for the thorough explanation ! ! ! I though Pat Hartman was referring 'DLookup'. During my investigation, I did create code to test for '0' or '-1'. I have to check to see if I still have the code in place. Also, as suggested, I did try to do my testing for blank text boxes in 'Before Update' 'Event Procedure' , but it did not work. As I tabbed through without entering data, it did not produce the pop-up error message. This same code worked in the 'Test' database 'Finish' button. Can you explain why the below code works for the 'Event Procedure' for the 'Finish' button of my form ('Test' database - allready sent) and not in the 'Before Update' 'Event Procedure' ?

If IsNull([First_Name]) Then
MsgBox "Please enter your first name.", , " Missing First Name"
SurveyError = "On"
[First_Name].SetFocus
GoTo Bottom
End If

Lastly, Pat Hartman thank you for your suggestion ! ! !