• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

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?
0
ascnd
Asked:
ascnd
  • 6
  • 3
  • 2
  • +2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try this

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

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This will force a value to be in the combo box ... in this case, the first item in the list  ... ItemData(0)

mx
0
 
CaptainGibletsCommented:
can you not put in some vba code that says this after the "after update"

if isnull(txtname) then
me.txtname.value = null
end if
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
gtdriver94Commented:
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.
0
 
peter57rCommented:
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
0
 
ascndAuthor Commented:
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
0
 
ascndAuthor Commented:
Thank you!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ascnd

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

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

mx
0
 
CaptainGibletsCommented:
sorry it was meant to say if not
0
 
peter57rCommented:
I agree with mx
0
 
ascndAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
And ... peter's answer actually does what you requested.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 6
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now