Delete from Combo Box

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I use the function below to automatically add a new value
to a combobox (property "Limit To List" = Yes).

&&&&&&&&&&&&&&&&&&&&&&&&
Private Sub DocumentType_NotInList(NewData As String,
Response As Integer)
Dim MySql As String
Beep
If MsgBox("'" & NewData & "' is currently not an
existing Document Type. " & vbCrLf _
& "Would you like to add this new Document
Type to the menu?", _
vbQuestion + vbOKCancel) = vbOK Then

MySql = "Insert into tbl_DocumentTypes
(DocumentType) VALUES ('" & NewData & "')"
CurrentDb.Execute MySql
Response = acDataErrAdded
End If
End Sub
&&&&&&&&&&&&&&&&&&&&&&&&


Is there a similar way to delete a value from the combo
box (and its source table)?

My choice would NOT to open another form, executing a
command button, etc. I'm not sure if this is possible
though.

Does anyone have a suggestion?

Thanks,
Tom
 
I use the function below to automatically add a new value
to a combobox (property "Limit To List" = Yes).

&&&&&&&&&&&&&&&&&&&&&&&&
Private Sub DocumentType_NotInList(NewData As String,
Response As Integer)
Dim MySql As String
Beep
If MsgBox("'" & NewData & "' is currently not an
existing Document Type. " & vbCrLf _
& "Would you like to add this new Document
Type to the menu?", _
vbQuestion + vbOKCancel) = vbOK Then

MySql = "Insert into tbl_DocumentTypes
(DocumentType) VALUES ('" & NewData & "')"
CurrentDb.Execute MySql
Response = acDataErrAdded
End If
End Sub
&&&&&&&&&&&&&&&&&&&&&&&&


Is there a similar way to delete a value from the combo
box (and its source table)?

My choice would NOT to open another form, executing a
command button, etc. I'm not sure if this is possible
though.

Does anyone have a suggestion?

Thanks,
Tom
Click to expand...

You can re-use some of your code there to simply execute a DELETE action (as opposed to an INSERT action):

MySql = "DELETE * FROM tbl_DocumentTypes WHERE DocumentType = '" & Me.DocumentType & "'"

and refresh the combo list:

Me.DocumentType.Requery

Jeremiah Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Jeremiah:

Thanks for the feedback.

Adding a new value to the combo box seems simple... I
just type in a value and press ENTER. I then get
prompted to confirm the addition to the combo box.

How would this work with the DELETE function. Do I need
need to create a command button (e.g. "Press to
delete")? If yes, how would the function determine which
one to delete?

Maybe I misunderstood, my first choice though would still
be not to use yet another button to execute the deletion
of a record.

I appreciate if you have any additional pointers!

Thanks,
Tom


-----Original Message-----
You can re-use some of your code there to simply execute
Click to expand...
a DELETE action (as opposed to an INSERT action):
MySql = "DELETE * FROM tbl_DocumentTypes WHERE
Click to expand...
DocumentType = '" & Me.DocumentType & "'"
Click to expand...
 
Hey Tom, no problem on the feedback.

If you don't want to use a button for deleting the an entry in the combo box, the only other thing I can think of is using a
special keypress combination to delete the currently selected entry, such as control+D. It may look like this then (using the
On KeyDown event of the control):

Private Sub DocumentType_KeyDown(KeyCode As Integer, Shift As Integer)
' KeyCode contains the upper-case Ascii value of the key pressed
' replace acCtrlMask with acAltMask or acShiftMask for the alt or shift keys as desired for your key combination
If (Shift And acCtrlMask) And KeyCode = Asc("D") Then
' if you put this function on a button just use the 3 lines below
If Me.DocumentType.ListIndex = -1 Then Exit Sub
If MsgBox ("Delete Document Type '" & Me.DocumentType & "'?", vbQuestion + vbOKCancel) = vbCancel Then Exit Sub
CurrentDb.Execute "DELETE * FROM tbl_DocumentTypes WHERE DocumentType = '" & Me.DocumentType & "'"
End If
End Sub

Jeremiah:

Thanks for the feedback.

Adding a new value to the combo box seems simple... I
just type in a value and press ENTER. I then get
prompted to confirm the addition to the combo box.

How would this work with the DELETE function. Do I need
need to create a command button (e.g. "Press to
delete")? If yes, how would the function determine which
one to delete?

Maybe I misunderstood, my first choice though would still
be not to use yet another button to execute the deletion
of a record.

I appreciate if you have any additional pointers!

Thanks,
Tom


-----Original Message-----

a DELETE action (as opposed to an INSERT action):
DocumentType = '" & Me.DocumentType & "'"
Click to expand...

Jeremiah Ellison
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
 
Jeremiah:

This works brilliantly!!!!

Thank you so much!


Tom

-----Original Message-----
Hey Tom, no problem on the feedback.

If you don't want to use a button for deleting the an
Click to expand...
entry in the combo box, the only other thing I can think
of is using a
special keypress combination to delete the currently
Click to expand...
selected entry, such as control+D. It may look like this
then (using the
On KeyDown event of the control):

Private Sub DocumentType_KeyDown(KeyCode As Integer, Shift As Integer)
' KeyCode contains the upper-case Ascii value of the key pressed
' replace acCtrlMask with acAltMask or acShiftMask
Click to expand...
for the alt or shift keys as desired for your key
combination
If (Shift And acCtrlMask) And KeyCode = Asc("D") Then
' if you put this function on a button just use the 3 lines below
If Me.DocumentType.ListIndex = -1 Then Exit Sub
If MsgBox ("Delete Document Type '" &
Click to expand...
Me.DocumentType & "'?", vbQuestion + vbOKCancel) =
vbCancel Then Exit Sub
CurrentDb.Execute "DELETE * FROM
Click to expand...
tbl_DocumentTypes WHERE DocumentType = '" &
Me.DocumentType & "'"
End If
End Sub

Click to expand...
 
Well... just one more thing...

I have now added the same function for a second drop-down
box.

I took the function and used the REPLACE statement to
ensure I update all properties correctly.

Now, on the 2nd combo-box,
- I get prompted after CTRL+D
- I acknowledge with OK
- then I am left with the value "d" in the combo box.

I doubt that I need to use different Key-stroke
combination for different combo boxes, right?!

BTW, I did put the Me.XYX.Requery into the function, such
as listed below. What am I doing wrong?

Tom




....
....
....
CurrentDb.Execute "DELETE * FROM
tbl_Classifications WHERE Classification = '" &
Me.Classification & "'"
Me.Classification.Requery
End If
End Sub
-----Original Message-----
Jeremiah:

This works brilliantly!!!!

Thank you so much!


Tom

-----Original Message-----
Hey Tom, no problem on the feedback.

If you don't want to use a button for deleting the an
Click to expand...
entry in the combo box, the only other thing I can think
of is using a
special keypress combination to delete the currently
Click to expand...
selected entry, such as control+D. It may look like this
then (using the
On KeyDown event of the control):

Private Sub DocumentType_KeyDown(KeyCode As Integer, Shift As Integer)
' KeyCode contains the upper-case Ascii value of
Click to expand...
the
key pressed
' replace acCtrlMask with acAltMask or acShiftMask
Click to expand...
for the alt or shift keys as desired for your key
combination
If (Shift And acCtrlMask) And KeyCode = Asc("D") Then
' if you put this function on a button just use the 3 lines below
If Me.DocumentType.ListIndex = -1 Then Exit Sub
If MsgBox ("Delete Document Type '" &
Click to expand...
Me.DocumentType & "'?", vbQuestion + vbOKCancel) =
vbCancel Then Exit Sub
CurrentDb.Execute "DELETE * FROM
Click to expand...
tbl_DocumentTypes WHERE DocumentType = '" &
Me.DocumentType & "'"
Click to expand...
 
Jeremiah:

Here's the problem...

Coincidentally, the first "real value" that currently
resides in the Document Type combo is called "Diretive".

Obviously, "Directive" begins with "D" so after I pressed
the CTRL+D keys, the next value found was Directive.
That's why it didn't store the "d" in this combo box.

However, the first current value in the "Classification"
was "NATO Secret"... value beginning with "N". So the
previously pressed "d" was captured in the drop-down box.

At the same time, the "AddValue" function kicked in and
now prompts if I want to add "d" to the menu (which I
don't).

Besides the Me.Classification.Requery, I also
added "Me.Classification = Null". But this does not
work.

Do you have any suggestions as to how I need to fine-tune
the DELETE function so that the "d" will not be
interpreted as a new value to be added?

Thanks,
Tom
-----Original Message-----
Well... just one more thing...

I have now added the same function for a second drop- down
box.

I took the function and used the REPLACE statement to
ensure I update all properties correctly.

Now, on the 2nd combo-box,
- I get prompted after CTRL+D
- I acknowledge with OK
- then I am left with the value "d" in the combo box.

I doubt that I need to use different Key-stroke
combination for different combo boxes, right?!

BTW, I did put the Me.XYX.Requery into the function, such
as listed below. What am I doing wrong?

Tom




....
....
....
CurrentDb.Execute "DELETE * FROM
tbl_Classifications WHERE Classification = '" &
Me.Classification & "'"
Me.Classification.Requery
End If
End Sub
Click to expand...
 
I don't seem to get this with the version of Access I'm running. But I believe you can fix this by including a line which
sets the KeyCode to 0 in the If block:

If (Shift And acCtrlMask) And KeyCode = Asc("D") Then
KeyCode = 0
'rest of code etc.
End If

I hope that fixes it for you.

Jeremiah:

Here's the problem...

Coincidentally, the first "real value" that currently
resides in the Document Type combo is called "Diretive".

Obviously, "Directive" begins with "D" so after I pressed
the CTRL+D keys, the next value found was Directive.
That's why it didn't store the "d" in this combo box.

However, the first current value in the "Classification"
was "NATO Secret"... value beginning with "N". So the
previously pressed "d" was captured in the drop-down box.

At the same time, the "AddValue" function kicked in and
now prompts if I want to add "d" to the menu (which I
don't).

Besides the Me.Classification.Requery, I also
added "Me.Classification = Null". But this does not
work.

Do you have any suggestions as to how I need to fine-tune
the DELETE function so that the "d" will not be
interpreted as a new value to be added?

Thanks,
Tom
Click to expand...

Jeremiah Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Jeremiah:

Thanks again for the prompt feedback...

yes, adding the KeyCode = 0 working great!


Tom

-----Original Message-----
I don't seem to get this with the version of Access I'm
Click to expand...
running. But I believe you can fix this by including a
line which
sets the KeyCode to 0 in the If block:

If (Shift And acCtrlMask) And KeyCode = Asc("D") Then
KeyCode = 0
'rest of code etc.
End If

I hope that fixes it for you.
Click to expand...
 
Back
Top