Delete a record from a listbox

G

Guest

Hello
I am working on an Access 2k app. One of my forms uses a listbox to show an
employee list. I want to be able to add and delete names from this list.
Adding isn't so hard but deleting seems to be. So far I have...
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
Dim strSQL As String

' delete via listbox
strSQL = "DELETE employeesID, employees FROM [tblEmployees] WHERE
[employeesID] = & me!lstEmployees"

DoCmd.RunSQL strSQL
Me.lstEmployees.Requery
Forms!frmEmployees.Refresh

txtAddemployee.Text = vbNull
Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub
but I get no response when I press the delete key with the name highlighted
in the listbox.

thanks for any help!
Steve
 
D

Douglas J. Steele

Do you actually get into the cmdDelete_Click routine when you click on the
button? Sometimes a problem can occur, and the linkage between the control
and the code gets broken.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox" <Delete a record from a
(e-mail address removed)> wrote in message
news:[email protected]...
 
G

Guest

I'm not real sure how to tell if I am entering the command button routine.
Unlike VB where you can at least double click an object on a form to go to
that click event VBA doesn't have that capability.

So I'm not sure, to be honest.

Steve

Douglas J. Steele said:
Do you actually get into the cmdDelete_Click routine when you click on the
button? Sometimes a problem can occur, and the linkage between the control
and the code gets broken.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox" <Delete a record from a
(e-mail address removed)> wrote in message
Hello
I am working on an Access 2k app. One of my forms uses a listbox to show
an
employee list. I want to be able to add and delete names from this list.
Adding isn't so hard but deleting seems to be. So far I have...
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
Dim strSQL As String

' delete via listbox
strSQL = "DELETE employeesID, employees FROM [tblEmployees] WHERE
[employeesID] = & me!lstEmployees"

DoCmd.RunSQL strSQL
Me.lstEmployees.Requery
Forms!frmEmployees.Refresh

txtAddemployee.Text = vbNull
Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub
but I get no response when I press the delete key with the name
highlighted
in the listbox.

thanks for any help!
Steve
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Look at the Properties window for the form. Select the cmdDelete control,
and look at what's entered for the Click event. If it doesn't say [Event
Procedure], select that from the combobox.

If it does say [Event Procedure], put a break point inside the module so
that you can single-step through the routine.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox"
I'm not real sure how to tell if I am entering the command button routine.
Unlike VB where you can at least double click an object on a form to go to
that click event VBA doesn't have that capability.

So I'm not sure, to be honest.

Steve

Douglas J. Steele said:
Do you actually get into the cmdDelete_Click routine when you click on
the
button? Sometimes a problem can occur, and the linkage between the
control
and the code gets broken.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox" <Delete a record from a
(e-mail address removed)> wrote in message
Hello
I am working on an Access 2k app. One of my forms uses a listbox to
show
an
employee list. I want to be able to add and delete names from this
list.
Adding isn't so hard but deleting seems to be. So far I have...
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
Dim strSQL As String

' delete via listbox
strSQL = "DELETE employeesID, employees FROM [tblEmployees] WHERE
[employeesID] = & me!lstEmployees"

DoCmd.RunSQL strSQL
Me.lstEmployees.Requery
Forms!frmEmployees.Refresh

txtAddemployee.Text = vbNull
Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub
but I get no response when I press the delete key with the name
highlighted
in the listbox.

thanks for any help!
Steve
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

thanks Douglas for the reply!
There wasn't an event procedure. So I made it so.
Now I'm getting the error"Run-time error '3075'
Syntax error (missing operator) in query expression '[employeesID] = &
me!lstEmployees'.

strSQL = "DELETE employeesID, employees FROM [tblEmployees] WHERE
[employeesID] = & me!lstEmployees"
DoCmd.RunSQL strSQL ' on this line is the error

Douglas J. Steele said:
Look at the Properties window for the form. Select the cmdDelete control,
and look at what's entered for the Click event. If it doesn't say [Event
Procedure], select that from the combobox.

If it does say [Event Procedure], put a break point inside the module so
that you can single-step through the routine.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox"
I'm not real sure how to tell if I am entering the command button routine.
Unlike VB where you can at least double click an object on a form to go to
that click event VBA doesn't have that capability.

So I'm not sure, to be honest.

Steve
Click to expand...
Click to expand...
 
G

Guest

It works now
I used ...
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

in the delete routine
thanks very much for your help!

Steve

Douglas J. Steele said:
Look at the Properties window for the form. Select the cmdDelete control,
and look at what's entered for the Click event. If it doesn't say [Event
Procedure], select that from the combobox.

If it does say [Event Procedure], put a break point inside the module so
that you can single-step through the routine.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox"
I'm not real sure how to tell if I am entering the command button routine.
Unlike VB where you can at least double click an object on a form to go to
that click event VBA doesn't have that capability.

So I'm not sure, to be honest.

Steve

Douglas J. Steele said:
Do you actually get into the cmdDelete_Click routine when you click on
the
button? Sometimes a problem can occur, and the linkage between the
control
and the code gets broken.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox" <Delete a record from a
(e-mail address removed)> wrote in message
Hello
I am working on an Access 2k app. One of my forms uses a listbox to
show
an
employee list. I want to be able to add and delete names from this
list.
Adding isn't so hard but deleting seems to be. So far I have...
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
Dim strSQL As String

' delete via listbox
strSQL = "DELETE employeesID, employees FROM [tblEmployees] WHERE
[employeesID] = & me!lstEmployees"

DoCmd.RunSQL strSQL
Me.lstEmployees.Requery
Forms!frmEmployees.Refresh

txtAddemployee.Text = vbNull
Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub
but I get no response when I press the delete key with the name
highlighted
in the listbox.

thanks for any help!
Steve
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry, I didn't look closely enough at your routine.

Far better than what you're using is to correct your original routine.

The problem is you need to have the value of the listbox in your SQL, not
just the name of the control. In other words, take the reference to the
control outside of the quotes:

strSQL = "DELETE employeesID, employees FROM [tblEmployees] WHERE
[employeesID] = " & me!lstEmployees

If employeesID is text, as opposed to numeric, use:

strSQL = "DELETE employeesID, employees FROM [tblEmployees] WHERE
[employeesID] = " & Chr$(34) & me!lstEmployees & Chr$(34)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox"
It works now
I used ...
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

in the delete routine
thanks very much for your help!

Steve

Douglas J. Steele said:
Look at the Properties window for the form. Select the cmdDelete control,
and look at what's entered for the Click event. If it doesn't say [Event
Procedure], select that from the combobox.

If it does say [Event Procedure], put a break point inside the module so
that you can single-step through the routine.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox"
I'm not real sure how to tell if I am entering the command button
routine.
Unlike VB where you can at least double click an object on a form to go
to
that click event VBA doesn't have that capability.

So I'm not sure, to be honest.

Steve

:

Do you actually get into the cmdDelete_Click routine when you click on
the
button? Sometimes a problem can occur, and the linkage between the
control
and the code gets broken.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox" <Delete a record from a
(e-mail address removed)> wrote in message
Hello
I am working on an Access 2k app. One of my forms uses a listbox to
show
an
employee list. I want to be able to add and delete names from this
list.
Adding isn't so hard but deleting seems to be. So far I have...
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
Dim strSQL As String

' delete via listbox
strSQL = "DELETE employeesID, employees FROM [tblEmployees] WHERE
[employeesID] = & me!lstEmployees"

DoCmd.RunSQL strSQL
Me.lstEmployees.Requery
Forms!frmEmployees.Refresh

txtAddemployee.Text = vbNull
Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub
but I get no response when I press the delete key with the name
highlighted
in the listbox.

thanks for any help!
Steve
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hello
I tried your suggestion but it always deletes the first name in the table.
I'm using a textbox for entry and in the textbox when I pull the form up the
first name in the table is always in the textbox and that is the one that
gets deleted regardless of the value chosen in the listbox.

When I click on an entry in the listbox the value is reflected in the
textbox just fine.

so far I have...
Dim strSQL As String
strSQL = "DELETE employeeID, employees FROM [tblEmployees] WHERE
[employeeID] = " & Me!lstEmployees

Me!lstEmployees.Requery
Forms!employees.Refresh

Douglas J. Steele said:
Sorry, I didn't look closely enough at your routine.

Far better than what you're using is to correct your original routine.

The problem is you need to have the value of the listbox in your SQL, not
just the name of the control. In other words, take the reference to the
control outside of the quotes:

strSQL = "DELETE employeesID, employees FROM [tblEmployees] WHERE
[employeesID] = " & me!lstEmployees

If employeesID is text, as opposed to numeric, use:

strSQL = "DELETE employeesID, employees FROM [tblEmployees] WHERE
[employeesID] = " & Chr$(34) & me!lstEmployees & Chr$(34)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox"
It works now
I used ...
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

in the delete routine
thanks very much for your help!

Steve

Douglas J. Steele said:
Look at the Properties window for the form. Select the cmdDelete control,
and look at what's entered for the Click event. If it doesn't say [Event
Procedure], select that from the combobox.

If it does say [Event Procedure], put a break point inside the module so
that you can single-step through the routine.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox"
I'm not real sure how to tell if I am entering the command button
routine.
Unlike VB where you can at least double click an object on a form to go
to
that click event VBA doesn't have that capability.

So I'm not sure, to be honest.

Steve

:

Do you actually get into the cmdDelete_Click routine when you click on
the
button? Sometimes a problem can occur, and the linkage between the
control
and the code gets broken.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Delete a record from a listbox" <Delete a record from a
(e-mail address removed)> wrote in message
Hello
I am working on an Access 2k app. One of my forms uses a listbox to
show
an
employee list. I want to be able to add and delete names from this
list.
Adding isn't so hard but deleting seems to be. So far I have...
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
Dim strSQL As String

' delete via listbox
strSQL = "DELETE employeesID, employees FROM [tblEmployees] WHERE
[employeesID] = & me!lstEmployees"

DoCmd.RunSQL strSQL
Me.lstEmployees.Requery
Forms!frmEmployees.Refresh

txtAddemployee.Text = vbNull
Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub
but I get no response when I press the delete key with the name
highlighted
in the listbox.

thanks for any help!
Steve
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Delete a record using macros. 1
Command acCmdDeleteRecord on Subform 7
Replace standard delete message 3
Odd Error Message 1
Delete record error 3
ERROR: "...Can't find the field 'i' referred to in your expression 1
Delete Wizard Not Working 1
Why does this code stop working? 2

Top