Form (Combo Box) (1 Viewer)

lhooker

Registered User.
Local time
Today, 10:39
Joined
Dec 30, 2005
Messages
399
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

MVP / VIP
Local time
Today, 14:39
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 10:39
Joined
Dec 30, 2005
Messages
399
ridders,

The combo box and table both have no value.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,044
So it is set in code?
 

isladogs

MVP / VIP
Local time
Today, 14:39
Joined
Jan 14, 2017
Messages
18,186
It has to be set somewhere for this behaviour to occur.
Suggest you strip down and upload your db.
 

lhooker

Registered User.
Local time
Today, 10:39
Joined
Dec 30, 2005
Messages
399
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.
 

Attachments

  • Test.accdb
    756 KB · Views: 54

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:39
Joined
Jul 9, 2003
Messages
16,244
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.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:39
Joined
Jul 9, 2003
Messages
16,244
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
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:39
Joined
Jul 9, 2003
Messages
16,244
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...
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:39
Joined
Feb 19, 2002
Messages
42,970
REMOVE the table level lookup. The field is defined as a Yes/no data type. That data type is NUMERIC and accepts ONLY numeric values -1 (for Yes/True) and 0 (for No/False)

For some reason, the combo on the form still works but I wouldn't trust it. I would fix that also to use two columns for each entry so you can show the words Yes and NO but save the values -1 and 0 or preferably an option group which is clearer.

Also, and VERY important - your validation is in the Wrong event. Form level validation belongs in the FORM's BeforeUpdate event where you can cancel the update if you find an error. Your code does not prevent invalid records from being saved as you can see from the contents of your test table.

The Yes/No data type as the name implies accepts Yes (-1) or No(0), there is no third option. If you want to allow null as an option, then switch the data type to Integer.
 

lhooker

Registered User.
Local time
Today, 10:39
Joined
Dec 30, 2005
Messages
399
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 ! ! !
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:39
Joined
Feb 19, 2002
Messages
42,970
That was't what I said to do. I said to remove the table level lookup

Changing the data type on the table did not convert the data. It should now show as its true value which would be -1 or 0.

Table level lookups cause nothing but trouble. The first thing they do is to mask the underlying data and convince people that the data is text when in fact it is numeric. That leads to issues with VBA and criteria in SQL.
 

lhooker

Registered User.
Local time
Today, 10:39
Joined
Dec 30, 2005
Messages
399
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:39
Joined
Feb 19, 2002
Messages
42,970
This is what I am talking about.
 

Attachments

  • TableLevelLookup.jpg
    TableLevelLookup.jpg
    77 KB · Views: 52

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:39
Joined
Jul 9, 2003
Messages
16,244
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

Registered User.
Local time
Today, 10:39
Joined
Dec 30, 2005
Messages
399
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 ! ! !
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:39
Joined
Feb 19, 2002
Messages
42,970
It will always work in the FORM's BeforeUpdate event. It will only work under one condition in the control's BeforeUpdate event.

There are several reasons that most validation code should be placed into the Form's BeforeUpdate .
1. The Form's BeforeUpdate event ALWAYS runs as the LAST event PRIOR to saving a record. It doesn't matter what caused the record to be saved, whether you forced a save, scrolled to a new main form record, switched focus from a main form to a subform or vice versa, closed the form, closed the database, etc. This event catches ALL closes. When you put validation code in your save button, the only time the validation runs is if the user pressed the save button. As you can see by my list, there are many situations that cause a record to be saved and your code will not catch those errors.
2. Sometimes it makes sense to put certain validation code into a control's BeforeUpdate event. Perhaps, you might want to show/hide another control based on the value in this control.
3. The one validation rule that can NEVER be used in a control's BeforeUpdate event is an "empty" check. Control level events only fire if a control gets the focus. If the user never tabs (or clicks) into a control, those control events will NEVER fire so you will never know that the controls is empty unless you catch that error in the FORM's BeforeUpdate event.
4. Validation that uses multiple fields is awkward at best if you put it in control level events. If you have to enter two dates and one must be earlier than the other, which control's events do you do the test in? No mater which event you choose, one of them may always be blank and unless you are saving "now", that is valid because you can physically only enter one at a time. By the time the Form's BeforeUpdate event runs, all data should have been entered so if one field is empty, you can raise an error message.

I would change your validation:
Code:
If Me.First_Name & "" = "" Then
    MsgBox "Please enter your first name.", , " Missing First Name", vbOKOnly
    Me.First_Name.SetFocus = True
    Cancel = True
    Exit Sub
End If

Using this method, the code cancels the save and exits when the first error is encountered. It puts focus in the field your message is about. Users are confused when you raise multiple errors and they forget what else they have to change.

I changed the IsNull() check because even if your table does not allow ZLS, form controls do and if the user clears the field by spacing or backspacing, you end up with a ZLS rather than null so you need to trap both. Concatenating a ZLS will make a null value = ZLS but will not alter a populated value so this concatenation allows you to use a single If statement to check for both situations. There is no need to distinguish because from the database/s point of view, both are invalid.
 

Users who are viewing this thread

Top Bottom