469,632 Members | 1,750 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,632 developers. It's quick & easy.

Delete record commandbutton

Hi,
I would like to delete a record (with commandbutton) of a table associated
to a combobox, when I select a certain value in the combobox (the value I
select is associated with the record that I want to delete.
For example:
I have a form associated to a table, which has only one field (PatientN°),
and a combobox (within the form) that contains the list of values
(PatientN°) contained in the table. Now I would like to select a value in
the combobox and then click on a button that deletes the selected value.
How can I implement such a button?
Thanks
Nov 13 '05 #1
6 3786
"Bernd Smits" wrote
I would like to delete a record (with commandbutton) of a table associated to a combobox, when I select a certain value in the combobox (the value I
select is associated with the record that I want to delete.
For example: I have a form associated to a table,
which has only one field (PatientN°),
and a combobox (within the form) that
contains the list of values (PatientN°)
contained in the table. Now I would like
to select a value in the combobox and
then click on a button that deletes the
selected value.
How can I implement such a button?
Thanks


Here's a sample:

tblWorkers has Records identified by Field WorkerID

Created an unbound Form (the RecordSource of the Form is empty), frmDelRec,
and placed a Combo Box, cboSelectToDelete, on the Form. The RowSource of the
ComboBox is SELECT WorkerID FROM tblWorkers ORDER BY [WorkerID]; and the
default value property is =[cboSelectToDelete].[ItemData](0) so that the
first item in the list shows when the user hasn't selected anything. Added a
CommandButton, cmdDelete, to the Form. In the Click event of cmdDelete, I
put the following code:

Dim strSQL As String
Dim db As DAO.Database

strSQL = "DELETE * FROM tblWorkers WHERE [WorkerID] = " &
Me!cboSelectToDelete

Set db = CurrentDb
db.Execute strSQL

This works for me.

Good luck with your project.

Larry Linson
Microsoft Access MVP
Nov 13 '05 #2
Larry

why do you propose to do
Set db = CurrentDb
db.Execute strSQL

instead of just
currentDb.Execute strSQL
"Larry Linson" <bo*****@localhost.not> wrote in message news:<Cw16d.5230$ku4.3570@trnddc01>...
"Bernd Smits" wrote
> I would like to delete a record (with commandbutton) of a table

associated
to a combobox, when I select a certain value in the combobox (the value I
select is associated with the record that I want to delete.
For example:

> I have a form associated to a table,
> which has only one field (PatientN°),
> and a combobox (within the form) that
> contains the list of values (PatientN°)
> contained in the table. Now I would like
> to select a value in the combobox and
> then click on a button that deletes the
> selected value.
> How can I implement such a button?
> Thanks


Here's a sample:

tblWorkers has Records identified by Field WorkerID

Created an unbound Form (the RecordSource of the Form is empty), frmDelRec,
and placed a Combo Box, cboSelectToDelete, on the Form. The RowSource of the
ComboBox is SELECT WorkerID FROM tblWorkers ORDER BY [WorkerID]; and the
default value property is =[cboSelectToDelete].[ItemData](0) so that the
first item in the list shows when the user hasn't selected anything. Added a
CommandButton, cmdDelete, to the Form. In the Click event of cmdDelete, I
put the following code:

Dim strSQL As String
Dim db As DAO.Database

strSQL = "DELETE * FROM tblWorkers WHERE [WorkerID] = " &
Me!cboSelectToDelete

Set db = CurrentDb
db.Execute strSQL

This works for me.

Good luck with your project.

Larry Linson
Microsoft Access MVP

Nov 13 '05 #3
le*********@natpro.com (Roger) wrote:
why do you propose to do
Set db = CurrentDb
db.Execute strSQL

instead of just
currentDb.Execute strSQL


It's good practice/laziness (on my part at least ;-)). If you have many
operations to complete using the current db then it's easier and quicker to
declare "db" up front and then type "db." than typing "currentDb." every
time.

Regards,
Keith.
Nov 13 '05 #4
Roger wrote:
why do you propose to do
Set db = CurrentDb
db.Execute strSQL

instead of just
currentDb.Execute strSQL


In A97, or possibly an earlier version, I seem to recall reading on this
group there was some kind of issue with memory which made it adviseable
to do it Larry's way and include:

Set db = Nothing

at the procedure's exit point.

I don't know if that's still an issue in A2K or later, but I still do it
this way, myself.

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #5
I've done what you proposed to do, but now everytime I click on the "delete"
button, the selected record will be removed from the table but in the
combobox, in place of the the selected value (to delete) there is written
#Canceled or #Eliminated but only when I reboot the form #Eliminated and the
value are canceled.
Why does this happen? I would that when I click on the button automaticly
the value in the combobox is canceled, without the writing "#Eliminated".
Thanks
Bernd

"Larry Linson" <bo*****@localhost.not> ha scritto nel messaggio
news:Cw16d.5230$ku4.3570@trnddc01...
"Bernd Smits" wrote
> I would like to delete a record (with commandbutton) of a table associated
to a combobox, when I select a certain value in the combobox (the value I select is associated with the record that I want to delete.
For example:

> I have a form associated to a table,
> which has only one field (PatientN°),
> and a combobox (within the form) that
> contains the list of values (PatientN°)
> contained in the table. Now I would like
> to select a value in the combobox and
> then click on a button that deletes the
> selected value.
> How can I implement such a button?
> Thanks


Here's a sample:

tblWorkers has Records identified by Field WorkerID

Created an unbound Form (the RecordSource of the Form is empty),

frmDelRec, and placed a Combo Box, cboSelectToDelete, on the Form. The RowSource of the ComboBox is SELECT WorkerID FROM tblWorkers ORDER BY [WorkerID]; and the
default value property is =[cboSelectToDelete].[ItemData](0) so that the
first item in the list shows when the user hasn't selected anything. Added a CommandButton, cmdDelete, to the Form. In the Click event of cmdDelete, I
put the following code:

Dim strSQL As String
Dim db As DAO.Database

strSQL = "DELETE * FROM tblWorkers WHERE [WorkerID] = " &
Me!cboSelectToDelete

Set db = CurrentDb
db.Execute strSQL

This works for me.

Good luck with your project.

Larry Linson
Microsoft Access MVP

Nov 13 '05 #6
You need the combo box to get the new records once more. After deleting
the record from the table write:

me.theComboBox.requery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Steve | last post: by
4 posts views Thread by Susan Bricker | last post: by
reply views Thread by kar3n.chandra | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.