Combobox: Delete the data selected and it re-appears???

[Greg (codepug]

I have a combobox with the RowSourceType set to Table/Query and the
RowSource is an SQL query that references a separate lookup table that
contains the data that can be selected in the combobox. Also,
limittolist & autoexpand are set to Yes. Control source is set to the
main table field where the data will go.

I can highlight the data that was previously selected in the combobox,
and press the delete key to remove it, but after I move the mouse and
click on another field, the data that was just deleted re-appears in
the combobox? I have three comboboxes and they are all doing this.
Also, if I keep trying to delete, eventually it accepts it? I checked
everything, and I do not have anything coded that should be causing
this. Also, I noticed a few other posts with this problem, but found
no solution.

Each lookup table has referential integrity with cascading updates set
as 1 to many, and is tied to the main table (many). I’m not sure if
this is part of the problem?

[Rich P]

To reliably delete an item from a combobox that is based on table data -
you need to capture the event of the combobox when you press the delete
key (maybe the keyup event of the combobox). In this event have your
vba code check if the delete key was pressed. Here is a sample - I use
the combobox KeyDown event (and for me the keycode is 46 when I press
the delete key while the combobox has the focus

Private Sub Combo7_KeyDown(KeyCode As Integer, Shift As Integer)
Debug.Print "keydown " & KeyCode & " " & Combo7.Text
If KeyCode = 46 Then
Dim arr() As String, str1 As String, i As Integer
arr = Split(Combo7.RowSource, ";")
For i = 0 To UBound(arr)
If str1 <"" And Right(str1, 1) <";" Then str1 = str1 & ";"
If arr(i) <Combo7.Text Then str1 = str1 & arr(i)
Next
Combo7.RowSource = str1
End If

End Sub

I use a value list for the rowsource of my combobox. I then strip off
the deleted value if it is part of the current rowsource and then reset
the new rowsource to the combobox. If you are using a table for a
rowsource - then you need to delete the value from the table as follows:

DoCmd.RunSql "Delete * From rowSourceTable Where comboValue = '" &
combo7.Text & "'"

Note: if the value is a text value (not a numeric value) then you need
to delimit the parameter with single quotes.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Tom van Stiphout]

On Wed, 23 Jul 2008 11:38:03 -0700 (PDT), "Greg (co*****@gmail.com)"
<co*****@gmail.comwrote:

What Rich said, but perhaps he misunderstood and all you want to do
is:
A form has a dropdown with initially no selection made.
I select a value
After I tab away from that control, I want to undo the selection
(going back to <blank>).

Can you confirm?

-Tom.
Microsoft Access MVP

>I have a combobox with the RowSourceType set to Table/Query and the
RowSource is an SQL query that references a separate lookup table that
contains the data that can be selected in the combobox. Also,
limittolist & autoexpand are set to Yes. Control source is set to the
main table field where the data will go.

I can highlight the data that was previously selected in the combobox,
and press the delete key to remove it, but after I move the mouse and
click on another field, the data that was just deleted re-appears in
the combobox? I have three comboboxes and they are all doing this.
Also, if I keep trying to delete, eventually it accepts it? I checked
everything, and I do not have anything coded that should be causing
this. Also, I noticed a few other posts with this problem, but found
no solution.

Each lookup table has referential integrity with cascading updates set
as 1 to many, and is tied to the main table (many). I’m not sure if
this is part of the problem?

[Greg (codepug]

Yes Tom you are correct. If I make a selection in the dropdown, but
latter decide to remove what was selected,
I would highlight it with the mouse and hit the delete key. The
problem is, as soon as I click on another field the
deleted data reappears in the field. However, this does not happen if
I tab or CR out.

Thanks
Greg
On Jul 24, 12:17*am, Tom van Stiphout <[e-mail]:

On Wed, 23 Jul 2008 11:38:03 -0700 (PDT), "Greg ([e-mail])"

<[e-mail]:

What Rich said, but perhaps he misunderstood and all you want to do
is:
A form has a dropdown with initially no selection made.
I select a value
After I tab away from that control, I want to undo the selection
(going back to <blank>).

Can you confirm?

-Tom.
Microsoft Access MVP

I have a combobox with the RowSourceType set to Table/Query and the
RowSource is an SQL query that references a separate lookup table that
contains the data that can be selected in the combobox. Also,
limittolist & autoexpand are set to Yes. Control source is set to the
main table field where the data will go.

I can highlight the data that was previously selected in the combobox,
and press the delete key to remove it, but after I move the mouse and
click on another field, the data that was just deleted re-appears in
the combobox? *I have three comboboxes and they are all doing this.
Also, if I keep trying to delete, eventually it accepts it? *I checked
everything, and I do not have anything coded that should be causing
this. Also, I noticed a few other posts with this problem, but found
no solution.

Each lookup table has referential integrity with cascading updates set
as 1 to many, and is tied to the main table (many). I’m not sure if
this is part of the problem?- Hide quoted text -

- Show quoted text -

[Rich P]

Sorry, I misunderstood your problem. I think you can still go with the
keydown event of the combobox and check if the delete key was pressed.
If yes then flag it in a form level boolean variable. Then in the
Form's Current event if the combo_delete flag is set -- clear the
combobox field.

Dim comboDelete As Boolean

Private Sub Combo1_KeyDown(...)
If keycode = 46 then combodelete = True
End Sub

Private Sub Form1_Current()
If comboDelete = True Then
combo1 = ""
combodelete = False
End If
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Greg (codepug]

Thank you Rich

I guess this problem is considered normal behavior? and not an issue
with my code/approach or
referential integrity issue? I guess I'll have to force the behavior
I'm looking for, and will follow your
suggestion.

Greg
On Jul 24, 1:07*pm, Rich P <[e-mail]:

Sorry, I misunderstood your problem. *I think you can still go with the
keydown event of the combobox and check if the delete key was pressed.
If yes then flag it in a form level boolean variable. *Then in the
Form's Current event if the combo_delete flag is set -- clear the
combobox field.

Dim comboDelete As Boolean

Private Sub Combo1_KeyDown(...)
If keycode = 46 then combodelete = True
End Sub

Private Sub Form1_Current()
If comboDelete = True Then
* combo1 = ""
* combodelete = False
End If
End Sub

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

[Greg (codepug]

This worked Rick, and I placed the combo1="" logic in the OnExit event
of the combobox.
My new problem is I have about 18 comboboxes I have to write code for
to get this functionality, just
wondering if this undesired behavior is normal to begin with ?
Thanks Greg

On Jul 24, 2:47*pm, "Greg ([e-mail])" <[e-mail]>
wrote:

Thank you Rich

I guess this problem is considered normal behavior? and not an issue
with my code/approach or
referential integrity issue? I guess I'll have to force the behavior
I'm looking for, and will follow your
suggestion.

Greg

On Jul 24, 1:07*pm, Rich P <[e-mail]:

Sorry, I misunderstood your problem. *I think you can still go with the
keydown event of the combobox and check if the delete key was pressed.
If yes then flag it in a form level boolean variable. *Then in the
Form's Current event if the combo_delete flag is set -- clear the
combobox field.

Dim comboDelete As Boolean

Private Sub Combo1_KeyDown(...)
If keycode = 46 then combodelete = True
End Sub

Private Sub Form1_Current()
If comboDelete = True Then
* combo1 = ""
* combodelete = False
End If
End Sub

Rich

*** Sent via Developersdexhttp://www.developersdex.com***- Hide quoted text -

- Show quoted text -

[Greg (codepug]

Actually, I just refined it one more time, and this seems to work:

Private Sub Combo1_KeyDown(...)
If keycode = 46 then combo1 = ""
End Sub

Thanks
Greg
On Jul 24, 3:24*pm, "Greg ([e-mail])" <[e-mail]>
wrote:

This worked Rick, and I placed the combo1="" logic in the OnExit event
of the combobox.
My new problem is I have about 18 comboboxes I have to write code for
to get this functionality, just
wondering if this undesired behavior is normal to begin with ?

Thanks Greg

On Jul 24, 2:47*pm, "Greg ([e-mail])" <[e-mail]>
wrote:

Thank you Rich

I guess this problem is considered normal behavior? and not an issue
with my code/approach or
referential integrity issue? I guess I'll have to force the behavior
I'm looking for, and will follow your
suggestion.

Greg

On Jul 24, 1:07*pm, Rich P <[e-mail]:

Sorry, I misunderstood your problem. *I think you can still go withthe
keydown event of the combobox and check if the delete key was pressed..
If yes then flag it in a form level boolean variable. *Then in the
Form's Current event if the combo_delete flag is set -- clear the
combobox field.

Dim comboDelete As Boolean

Private Sub Combo1_KeyDown(...)
If keycode = 46 then combodelete = True
End Sub

Private Sub Form1_Current()
If comboDelete = True Then
* combo1 = ""
* combodelete = False
End If
End Sub

Rich

*** Sent via Developersdexhttp://www.developersdex.com***-Hide quotedtext -

- Show quoted text -- Hide quoted text -

- Show quoted text -

[Rich P]

>>

This worked Rick, and I placed the combo1="" logic in the OnExit event
of the combobox.
My new problem is I have about 18 comboboxes I have to write code for
to get this functionality, just
wondering if this undesired behavior is normal to begin with ?
Thanks Greg
<<

This is where Object Oriented Programming (OOP -- VB.Net) would come in
handy. For a situation like this you would use a Delegate -- which is
an interface for event functions (OOP speak here). You would assign the
same delegate to each combobox in a loop of comboboxes. I am basically
thinking aloud here to see how you could implement similar functionality
in VBA without having to write 18 keydown procedures for 18 comboboxes.
The alternative would be to write 18 keydown procedures. I am coming up
blank otherwise.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Greg (codepug]

Rich

Never worked with OOP, but your explanation makes a lot of sense, and
demonstrates its value.
I created 18- 3line procedures. No big deal.
Thanks again for your help

Greg

On Jul 24, 4:04*pm, Rich P <[e-mail]:

This worked Rick, and I placed the combo1="" logic in the OnExit event
of the combobox.
My new problem is I have about 18 comboboxes I have to write code for
to get this functionality, just
wondering if this undesired behavior is normal to begin with ?

Thanks Greg
<<

This is where Object Oriented Programming (OOP -- VB.Net) would come in
handy. *For a situation like this you would use a Delegate -- which is
an interface for event functions (OOP speak here). *You would assign the
same delegate to each combobox in a loop of comboboxes. *I am basically
thinking aloud here to see how you could implement similar functionality
in VBA without having to write 18 keydown procedures for 18 comboboxes.
The alternative would be to write 18 keydown procedures. *I am coming up
blank otherwise.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***