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, cboSelectToDele te, on the Form. The RowSource of the
ComboBox is SELECT WorkerID FROM tblWorkers ORDER BY [WorkerID]; and the
default value property is =[cboSelectToDele te].[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!cboSelectToD elete

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.Execu te strSQL
"Larry Linson" <bo*****@localh ost.not> wrote in message news:<Cw16d.523 0$ku4.3570@trnd dc01>...

"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, cboSelectToDele te, on the Form. The RowSource of the
ComboBox is SELECT WorkerID FROM tblWorkers ORDER BY [WorkerID]; and the
default value property is =[cboSelectToDele te].[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!cboSelectToD elete

Set db = CurrentDb
db.Execute strSQL

This works for me.

Good luck with your project.

Larry Linson
Microsoft Access MVP

[Keith Wilby]

le*********@nat pro.com (Roger) wrote:

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

instead of just
currentDb.Execu te 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.Execu te 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 "#Eliminate d".
Thanks
Bernd

"Larry Linson" <bo*****@localh ost.not> ha scritto nel messaggio
news:Cw16d.5230 $ku4.3570@trndd c01...

"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, cboSelectToDele te, on the Form. The RowSource of the ComboBox is SELECT WorkerID FROM tblWorkers ORDER BY [WorkerID]; and the
default value property is =[cboSelectToDele te].[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!cboSelectToD elete

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!