Delete record commandbutton

[Bernd Smits]

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

[Larry Linson]

"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

[Roger]

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

[Keith Wilby]

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.

[Tim Marshall]

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

[Bernd Smits]

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

[Ron Devonish]

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!