Link to home
Start Free TrialLog in
Avatar of ascnd
ascnd

asked on

Capture Delete combo box value to Null

I have a column (PR) in a table (tCat) that uses a combo box on a form to set the value.  They can only select values in the combo box.  Initally every record in column PR is set to NULL.  The problem I am having is that I need to run code that depends on what they select in the combo box.  The problem is when they delete the value in the combo box, which is acceptable, I need to capture that NULL value and I can't figure out how to do it.  When the delete the value the "ON CHANGE" event still says the old value is there not the NULL value.  How do I get around this?
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Try this

Private Sub Combo1_AfterUpdate()
If Nz(Me.Combo1,"")="" Then  Me.Combo1=Me.Combo1.ItemData(0)
End Sub

mx
This will force a value to be in the combo box ... in this case, the first item in the list  ... ItemData(0)

mx
ASKER CERTIFIED SOLUTION
Avatar of CaptainGiblets
CaptainGiblets
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The term "null" is not a measureable object. It basically means "not initialized". Once you initialize the drop-down, it is no longer "null". I would not depend on "null" when running queries against a database. Try setting default to 0 for number fields or "" (empty string) for text fields. Then add a value in your dropdown of 0 or "".

Also try using the AfterUpdated event to check the value of the dropdown.
In the form_beforeupdate event procedure you can test the old value and the current value of the combo.

If nz(me.comboname,"") ="" and (not nz(me.comboname.oldvalue)="") then
' previous value has been deleted
' do something
else
' value has changed, do nothing
end if
Avatar of ascnd
ascnd

ASKER

Maybe my question was a bit confusing but CatainGiblets got me on the correct path.  This is what worked:

Private Sub cboPR_CAT_FAMILY_ID_AfterUpdate()
    Dim db As DAO.Database
    Dim SQL As String
   
    Set db = CurrentDb
   
    If IsNull(Me.cboPR_CAT_FAMILY_ID) Then
        SQL = "UPDATE " & Me.TBL & " SET CAT_FAMILY_ID = -999 WHERE ITMCATNBR='" & Me.ITMCATNBR & "'"
        db.Execute SQL
    Else
        SQL = "UPDATE " & Me.TBL & " SET CAT_FAMILY_ID = " & Me.cboPR_CAT_FAMILY_ID & " WHERE ITMCATNBR='" & Me.ITMCATNBR & "'"
        db.Execute SQL
    End If
End Sub
Avatar of ascnd

ASKER

Thank you!
"The term "null" is not a measureable object."

Actually it is.

And Null means the absence of data.  

"Try setting default to 0 for number fields or "" (empty string) for text fields."

Not a good idea. '0' is a valid data item ... but the wrong data.  Text fields should not be set to empty strings ....  See this link for an explanation:

http://allenbrowne.com/bug-09.html

scroll down to Zero Length String

mx
ascnd

The answer you accepted doesn't do anything ... It says:

"If the value is Null ... then set it to Null"

mx
sorry it was meant to say if not
I agree with mx
Avatar of ascnd

ASKER

Yes, I know that CaptainGiblets code is incorrect but as I said CaptainGiblets got me on the correct path.  I have submitted the code that worked.  Basically I just had to move it to another event.  Thanks for all your help and such quick responses.
"Yes, I know that CaptainGiblets code is incorrect"

Please reopen the Q.  That answer will be misleading for future viewers.  Also, what you posted is quite different than the original Q.

mx
And ... peter's answer actually does what you requested.

mx