deleting values in combo boxes

[R]

I have a few combo boxes that users can add values to with the on not in list
function. Is there anyway to delete values from combo boxes if for example
they accidently mistype something in??

Thanks!!

 

[Ken Snell [MVP]]

If you want to delete the value that is displayed in the combo box because
that is what the person typed into the box, and it's not in the combo box's
row source list yet, just highlight the value and press the Delete key.

If you want to delete the value after it's in the row source list, then it
depends upon what you're using for the row source (value list, table/query,
etc.). You'll need to post more details.

If you mean something entirely different from the above, then post more
details, please.

 

[R]

I want to delete it after its already in the row source...The row source is
table/query.

 

[Ken Snell [MVP]]

You will need to run a delete query that will delete that record from the
table. Is this something that you want to do manually, or something that
needs to be programmed into the database in some way?

 

[R]

It needs to be put in the databse so users can delete something if they screw
up and so they don't have to go behind the scenes to delete it from the
table. Thanks in Advance!!

 

[R]

have I given enough information? If not just ask I will give.
Thanks-

 

[Ken Snell [MVP]]

There are a few ways to do this, but do you want the user to have the
ability to delete items from that list? Sometimes maintenance of the list
should be limited to just a few people.

But, if you want all to have access, probably the easiest way to implement
this would be to build a form based on the table that has the values (use a
continuous forms view for the form layout), and give the user a button to
click that will open that form in dialog mode. The user then could edit
information, add new entries, or even delete entries using the built-int
features of an ACCESS form.

Otherwise, you'd need to run code that gets the value to be deleted (could
come from the value that is displayed in the combo box after making a
selection, or could come from asking the user for the name, or could come
from displaying a form with a combo box that has the list of items and let
the user select the "offending" item), and then runs a delete query.

Which of these approaches do you prefer? What more info do you need for
setting up that option?

 

[R]

I would like to do this option "or could come from displaying a form with a
combo box that has the list of items and let the user select the "offending"
item), and then runs a delete query."

So i would build a form that has a drop down combo box and i could run that
form by using the double click feature of the original drop down box and then
have them select the value and then on the OK button have it be deleted??

Thanks-

 

[Ken Snell [MVP]]

Yes, you could use that process to do what you seek to do. The delete query
could be one that is stored, and that uses the combo box on this extra form
as the source of the criterion expression's parameter for which record to
delete.
--

Ken Snell
<MS ACCESS MVP>

 

[R]

Alright thanks...how do u make a delete query?

Yes, you could use that process to do what you seek to do. The delete query
could be one that is stored, and that uses the combo box on this extra form
as the source of the criterion expression's parameter for which record to
delete.

 

[Ken Snell [MVP]]

Create a new query in design view. Add the appropriate table to the query
grid. Add the * field to the grid. Go to toolbar and click the query type
icon button. Select Delete from the dropdown list.

You can put a Criterion expression for the desired field that holds the
value that will be in the combo box -- add that field to the grid as well,
and use an expression like this in the "Criteria:" cell under that field:

Forms!FormName!ComboBoxName

FormName is the name of the extra form that you'll open for your user.
ComboBoxName is the name of the combo box on that form where the person
selects the item that is to be deleted.

 

[R]

Im having trouble with my combo box in the new form. In my row source it
doesnt' let me pick the delete query. Am i doing something wrong?

 

[Ken Snell [MVP]]

You do not use the delete query as the row source of the combo box. Use the
same row source as on the original form. You run the delete query by running
a macro or code when you click a button on that same form. Use the Click
event of the button to run (open) the delete query.

 

[R]

alright i think i got it but i would like the form to requery the original
combo box when the new form closes...i can't remember what the coding is to
requery.
Thanks-

 

[R]

And im having trouble coding the Dbl click on the orginal combo box to open
the new form.

Thanks-

 

[Ken Snell [MVP]]

What type of trouble?

A possible code for the double click event:

Private Sub ComboBoxName_DblClick()
DoCmd.OpenForm "NewFormForDeletingItem", , , , , acDialog
Me.ComboBoxName.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>

 

[R]

when i type in Private Sub ComboBoxName_DblClick()
DoCmd.OpenForm frmDeleteComposer, , , , acDialog
Me.cbocomposerwmf.Requery
End Sub

I get an error stating The Action Or Method Requires a Form Name Argument.
What am i doing wrong?

Thanks-

 

[Ken Snell [MVP]]

What I posted is VBA code ... it goes in the form's module, not in the
DblClick event property box.

In the box next to Dbl Click, put
[Event Procedure]

Then click on the button at far right (with three dots on it). The Visual
Basic Editor will open and display this:

Private Sub cbocomposerwmf_DblClick()

End Sub


with the cursor sitting on the blank line between these two lines. In that
blank line, insert the two code lines that I posted:

DoCmd.OpenForm frmDeleteComposer, , , , acDialog
Me.cbocomposerwmf.Requery

Save and close the Visual Basic Editor. Save and close your form. It now
should respond to the double-clicking of the combo box.
--

Ken Snell
<MS ACCESS MVP>

 

[R]

I got everything to work..my problem was that i didnt' put quotes around my
form name. I used the double click feature and it works nicely...I put the
requery statement in the original combo box after it opens the new
form...when i run the new form and delete the value inside my combo box it
says #deleted# is that what its supposed to do or is there a way have it just
not show any thing left of that value? Thanks again for all your help!! I
really appreciate it.

What I posted is VBA code ... it goes in the form's module, not in the
DblClick event property box.

In the box next to Dbl Click, put
[Event Procedure]

Then click on the button at far right (with three dots on it). The Visual
Basic Editor will open and display this:

Private Sub cbocomposerwmf_DblClick()

End Sub


with the cursor sitting on the blank line between these two lines. In that
blank line, insert the two code lines that I posted:

DoCmd.OpenForm frmDeleteComposer, , , , acDialog
Me.cbocomposerwmf.Requery

Save and close the Visual Basic Editor. Save and close your form. It now
should respond to the double-clicking of the combo box.
--

Ken Snell
<MS ACCESS MVP>

when i type in Private Sub ComboBoxName_DblClick()
DoCmd.OpenForm frmDeleteComposer, , , , acDialog
Me.cbocomposerwmf.Requery
End Sub

I get an error stating The Action Or Method Requires a Form Name Argument.
What am i doing wrong?

Thanks-

 

[Ken Snell [MVP]]

You're missing a comma in the DoCmd.OpenForm step:

DoCmd.OpenForm frmDeleteComposer, , , , , acDialog

The above is what you need to use in place of the step you have. Your code
is not opening the deletion form in dialog mode, so the requery action in
the code occurs before you run the deletion.

--

Ken Snell
<MS ACCESS MVP>

I got everything to work..my problem was that i didnt' put quotes around my
form name. I used the double click feature and it works nicely...I put the
requery statement in the original combo box after it opens the new
form...when i run the new form and delete the value inside my combo box it
says #deleted# is that what its supposed to do or is there a way have it just
not show any thing left of that value? Thanks again for all your help!! I
really appreciate it.

What I posted is VBA code ... it goes in the form's module, not in the
DblClick event property box.

In the box next to Dbl Click, put
[Event Procedure]

Then click on the button at far right (with three dots on it). The Visual
Basic Editor will open and display this:

Private Sub cbocomposerwmf_DblClick()

End Sub


with the cursor sitting on the blank line between these two lines. In that
blank line, insert the two code lines that I posted:

DoCmd.OpenForm frmDeleteComposer, , , , acDialog
Me.cbocomposerwmf.Requery

Save and close the Visual Basic Editor. Save and close your form. It now
should respond to the double-clicking of the combo box.
--

Ken Snell
<MS ACCESS MVP>

when i type in Private Sub ComboBoxName_DblClick()
DoCmd.OpenForm frmDeleteComposer, , , , acDialog
Me.cbocomposerwmf.Requery
End Sub

I get an error stating The Action Or Method Requires a Form Name Argument.
What am i doing wrong?

Thanks-


:

What type of trouble?

A possible code for the double click event:

Private Sub ComboBoxName_DblClick()
DoCmd.OpenForm "NewFormForDeletingItem", , , , , acDialog
Me.ComboBoxName.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>


And im having trouble coding the Dbl click on the orginal combo box to
open
the new form.

Thanks-

:

alright i think i got it but i would like the form to requery the
original
combo box when the new form closes...i can't remember what the coding is
to
requery.
Thanks-


:

You do not use the delete query as the row source of the combo box.
Use the
same row source as on the original form. You run the delete

query
by

running
a macro or code when you click a button on that same form. Use the
Click
event of the button to run (open) the delete query.

--

Ken Snell
<MS ACCESS MVP>

Im having trouble with my combo box in the new form. In my row
source it
doesnt' let me pick the delete query. Am i doing something wrong?

:

Create a new query in design view. Add the appropriate

table
to

the
query
grid. Add the * field to the grid. Go to toolbar and click the
query
type
icon button. Select Delete from the dropdown list.

You can put a Criterion expression for the desired field that
holds the
value that will be in the combo box -- add that field to

the
grid

as
well,
and use an expression like this in the "Criteria:" cell

under
that

field:

Forms!FormName!ComboBoxName

FormName is the name of the extra form that you'll open

for
your

user.
ComboBoxName is the name of the combo box on that form

where
the

person
selects the item that is to be deleted.

--

Ken Snell
<MS ACCESS MVP>

Alright thanks...how do u make a delete query?

:

Yes, you could use that process to do what you seek to

do.
The

delete
query
could be one that is stored, and that uses the combo

box
on

this
extra
form
as the source of the criterion expression's parameter for
which
record
to
delete.
--

Ken Snell
<MS ACCESS MVP>



I would like to do this option "or could come from
displaying a
form
with
a
combo box that has the list of items and let the

user
select

the
"offending"
item), and then runs a delete query."

So i would build a form that has a drop down combo

box
and i

could
run
that
form by using the double click feature of the

original
drop

down
box
and
then
have them select the value and then on the OK button have it
be
deleted??

Thanks-


:

There are a few ways to do this, but do you want

the
user

to
have
the
ability to delete items from that list? Sometimes
maintenance of
the
list
should be limited to just a few people.

But, if you want all to have access, probably the easiest
way to
implement
this would be to build a form based on the table

that
has

the
values
(use a
continuous forms view for the form layout), and

give
the

user a
button
to
click that will open that form in dialog mode. The user
then
could
edit
information, add new entries, or even delete

entries
using

the
built-int
features of an ACCESS form.

Otherwise, you'd need to run code that gets the

value
to

be
deleted
(could
come from the value that is displayed in the combo box
after
making
a
selection, or could come from asking the user for the
name, or
could
come
from displaying a form with a combo box that has

the
list

of
items
and
let
the user select the "offending" item), and then runs a
delete
query.

Which of these approaches do you prefer? What more info do
you
need
for
setting up that option?

--

Ken Snell
<MS ACCESS MVP>

It needs to be put in the databse so users can delete
something if
they
screw
up and so they don't have to go behind the scenes to
delete it
from
the
table. Thanks in Advance!!


:

You will need to run a delete query that will delete
that
record
from
the
table. Is this something that you want to do manually,
or
something
that
needs to be programmed into the database in

some
way?

--

Ken Snell
<MS ACCESS MVP>


I want to delete it after its already in the row
source...The
row
source
is
table/query.

:

If you want to delete the value that is displayed
in the
combo
box
because
that is what the person typed into the

box,
and

it's not
in
the
combo
box's
row source list yet, just highlight the

value
and

press
the
Delete
key.

If you want to delete the value after it's

in
the

row
source
list,
then
it
depends upon what you're using for the row source
(value
list,
table/query,
etc.). You'll need to post more details.

If you mean something entirely different

from
the

above,
then
post
more
details, please.
--

Ken Snell
<MS ACCESS MVP>


I have a few combo boxes that users can add
values to
with
the
on
not
in
list
function. Is there anyway to delete

values
from

combo
boxes
if
for
example
they accidently mistype something in??

Thanks!!