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?
This will force a value to be in the combo box ... in this case, the first item in the list ... ItemData(0)
mx
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
If nz(me.comboname,"") ="" and (not nz(me.comboname.oldvalue)=
' previous value has been deleted
' do something
else
' value has changed, do nothing
end if
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_AfterU pdate()
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
Private Sub cboPR_CAT_FAMILY_ID_AfterU
Dim db As DAO.Database
Dim SQL As String
Set db = CurrentDb
If IsNull(Me.cboPR_CAT_FAMILY
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
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
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
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
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
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
mx
Private Sub Combo1_AfterUpdate()
If Nz(Me.Combo1,"")="" Then Me.Combo1=Me.Combo1.ItemDa
End Sub
mx