?
Solved

Capture Delete combo box value to Null

Posted on 2009-12-22
14
Medium Priority
?
494 Views
Last Modified: 2012-05-08
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
Comment
Question by:ascnd
  • 6
  • 3
  • 2
  • +2
14 Comments
 
LVL 75
ID: 26105895
Try this

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

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

mx
0
 
LVL 6

Accepted Solution

by:
CaptainGiblets earned 2000 total points
ID: 26105908
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Expert Comment

by:gtdriver94
ID: 26105919
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
 
LVL 77

Expert Comment

by:peter57r
ID: 26105978
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
 

Author Comment

by:ascnd
ID: 26105994
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
 

Author Closing Comment

by:ascnd
ID: 31669046
Thank you!
0
 
LVL 75
ID: 26106010
"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
 
LVL 75
ID: 26106052
ascnd

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

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

mx
0
 
LVL 6

Expert Comment

by:CaptainGiblets
ID: 26106070
sorry it was meant to say if not
0
 
LVL 77

Expert Comment

by:peter57r
ID: 26106072
I agree with mx
0
 

Author Comment

by:ascnd
ID: 26106113
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
 
LVL 75
ID: 26106131
"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
 
LVL 75
ID: 26106151
And ... peter's answer actually does what you requested.

mx
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question