none
Assigning NULL to ComboBox RRS feed

  • Question

  • Hi,

    Using Access 2010. I've tried to assign Null to bounded combo box as below (from what i've found over the internet), but all of them are ending up with Errors:

    Me.ComboBox = vbNullString
    Me.ComboBox = vbNull
    Me.ComboBox = Null

    i need to reset value so that form could not be saved if wrong value is selected. Column itself is marked as NOT NULL, so record will not save if Combo Box value will be null.

    Thank you in advance.

    K

    Saturday, May 28, 2016 7:00 AM

Answers

All replies

  • Access will not recognize Me.ComboBox, unless you have actually named your combobox as ComboBox, but you should never name any control on a form using its control type. Give the ComboBox a descriptive name such as FilterName (just an example). Then Me.FilterName=Null will set the field bound to the combobox to null.
    Saturday, May 28, 2016 2:33 PM
  • Yes, the ComboBox name in this case is 'EmployeeID'. I'm getting attached error when i try to do Me.EmployeeID = NULL.

    Thanks,

    K

    Sunday, May 29, 2016 9:28 AM
  • There you have it. Check your BeforeUpdate or ValidationRule procedure for this control.

    Also check this:

    http://stackoverflow.com/questions/25367080/error-2115-macro-function-set-to-beforeupdate-validationrule-is-preventing-pgm


    Best regards, George


    Sunday, May 29, 2016 11:05 AM

  • You say that you are trying to prevent wrong values being selected in the controls.  You have a number of options:

    1.  If the legitimate values are determined by one or more other values entered into controls in the form, limit the available values in the combo box to the legitimate values by correlating it with the other control(s).  This is done by referencing the other control(s) as one or more parameters in the combo box's RowSource, and Requerying the combo box in the AfterUpdate event procedure of the other control(s).

    2.  Prevent an illegitimate value being entered by means of code in the combo box's BeforeUpdate event procedure.  This has a Cancel argument whose return value can be set to True if the validation criteria are not satisfied.

    3.  Prevent the record being saved by means of code in the form's BeforeUpdate event procedure.  As with the control's event procedure this has a Cancel argument whose return value can be set to True if the validation criteria are not satisfied.

    4.  In situations where the validation criteria make reference to other rows in the same table, or in other tables, apply a CHECK CONSTRAINT to the table and handle the error in the form's Error event procedure.

    However you do it, you should always protect the integrity of the data as far as possible independently of whatever might be done in code at runtime, particularly by the enforcement of referential integrity, disallowing Null where appropriate, and the inclusion of one or more columns in unique indexes where the column or columns constitute a candidate key of a table and are not designated as the primary key.

    Ken Sheridan, Stafford, England

    Sunday, May 29, 2016 11:37 AM
  • Also, check the table where EmployeeID is located to make sure that EmployeeID is not an AutoNumber data type and is not a Primary Key. Access will not allow you set the value manually or with code if that is the case.
    Sunday, May 29, 2016 1:44 PM
  • Thank you, now I was able to recall and understand why it works like that.
    Monday, May 30, 2016 6:10 AM
  • Thanks for reminding me my forgotten lessons. Really appreciated )
    Monday, May 30, 2016 6:10 AM