Help wi' Deleting Records from a List box

[satty]

hey guys!

i was wondering if you could help me with the following problem.

i have a form in which i have added a LIST BOX. this list box lists all the users i have in the tblEmployees table.

i then have a COMMAND BUTTON to which i have given the command DELETE RECORD using the wizard that pops up. However, when i select a record in the LIST BOX and click the button, it doesnt work!!!

nothing happens? i think i need a code to link the button with the list box or something like this maybe.

i have the following code assigned to the button:

Private Sub btnDeleteUser_Click()
On Error GoTo Err_btnDeleteUser_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_btnDeleteUser_Click:
Exit Sub

Err_btnDeleteUser_Click:
MsgBox Err.Description
Resume Exit_btnDeleteUser_Click

End Sub


thanx people,



Satty

 

[Dugantrain]

You're absolutely right; the code that the Wizard wrote for you has the list box and the Command Button as mutually exclusive objects. What the Command Button is trying to do is delete the Form's Current Record from the table that that Form is bound to. This isn't what you want, especially if your Form is unbound. You'll need to write your own code. For Deleting records, there's a ton of ways to do it (ADO, DAO, RunSQL, pre-defined Delete Queries). I'm going to use RunSQL as it's my current flavor of the week. You'll also need a field in your list box which will uniquely identify each record (I always use Autonums as my Primary Key, but whatever you use as a PK is fine).

Private Sub btnDeleteUser_Click() 
DoCmd.RunSQL ("DELETE * FROM tblEmployees WHERE Your_Primary_Key = " & Me.Your_List.Column(0))
Me.Your_List.Requery
End Sub

Columns start their count at 0, so if your Primary Key is the first field in the list, then my code above will work; if it's the second column, use Me.Your_List.Column(1), etc.

 

[satty]

Thanx Dugantrain!!

however, im still having a problem. the database system i am creating will be made so that it is "user-friendly"

The Problem:

when i select the username and select delete, a get a msg box that says you will be deleting 1 Row(s) from the specified table.. it then asks do you want to continue? if i select NO it come with the following message:

Run-tine error '3059':
Operation Cancelled by user.

Is there anyway of making it so that this msg does not come up, but just doesnt continue with the deleting of the row?

Or additionally, i wouldnt mind making my own msg box popup with this message. if this is possible. i just dont want messages saying RUNTIME ERROR 2564 what ever numbers becuase these wont really mean anything to my users as they have like Near-ZERO Experience with Access.

the current code i am using is:

Private Sub btnDeleteUser_Click()
DoCmd.RunSQL ("DELETE * FROM tblEmployees WHERE strEmpName = " & Me.UserList.Column(1))
Me.UserList.Requery
End Sub

Thanx Guys!


Satty

 

[Dugantrain]

Certainly, :

Private Sub btnDeleteUser_Click() 
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM tblEmployees WHERE strEmpName = " & 
Me.UserList.Column(1)) 
DoCmd.SetWarnings True
Me.UserList.Requery 
End Sub

If you still want to give them the Option to Delete or back out, then write a conditional Msgbox. Something like:

Private Sub btnDeleteUser_Click() 
If Msgbox ("Delete The Current Record?", vbyesno)=vbyes then
  DoCmd.SetWarnings False
  DoCmd.RunSQL ("DELETE * FROM tblEmployees WHERE strEmpName = " & 
  Me.UserList.Column(1)) 
  DoCmd.SetWarnings True
  Me.UserList.Requery 
End If
End Sub

 

[satty]

Hey again,

i bet ur thinking wat a *****!!! sorry...erm merry christmas? hehe :wink:

anyways, the code provided by DuganTrain is even better, infact its perfect.

Code was:

Private Sub btnDeleteUser_Click()
If Msgbox ("Delete The Current Record?", vbyesno)=vbyes then
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM tblEmployees WHERE strEmpName = " &
Me.UserList.Column(1))
DoCmd.SetWarnings True
Me.UserList.Requery
End If
End Sub

however...

i know, theres always a however...

when i select YES to the "Delete The Current Record?" MsgBox i get a another box which has Enter Parameter Value in the Title Bar. Here i have to type the select value again. in a way this can be good as it confirms, however if i select CANCEL i still get the RUNTIME ERROR-2001.

i think i might be getting the ENTER PARAMETER VALUE due to the Me.UserList.Requery. may be im totally wrong. is it possible to make the record get deleted after Clicking YES to the First Msgbox?

if not, i can live with this, its excellent, Thanx DUGANTRAIN!! LIFE SAVER!


Satty

 

[Dugantrain]

ok, I think I see what the problem is. Your RunSQL statement is including strEmpName as a Column Heading. I'm assuming that strEmpName is not a column heading, but a variable that you're trying to pass into the SQL statement. If this is the case, then you're wanting:

DoCmd.RunSQL ("DELETE * FROM tblEmployees WHERE " &  strEmpName & " = " & 
Me.UserList.Column(1))

 

[satty]

ok, this is totally not working?

this what i have got ONCLICK on my button:

Private Sub btnDeleteUser_Click()
If MsgBox("Delete The Current User?", vbYesNo) = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM tblEmployees WHERE " & strEmpName & " = " & Me.UserList.Column(1))
DoCmd.SetWarnings True
Me.UserList.Requery
End If
End Sub

howevern now i get a error message saying:

Syntax error (missing operator) in query expression = 'Test'

I used the username as Test to delete it.

when i click DEBUG the following is highlight YELLOW:

DoCmd.RunSQL ("DELETE * FROM tblEmployees WHERE " & strEmpName & " = " & Me.UserList.Column(1))

Thanx Again



Satty

 

[Dugantrain]

ok, what is the field name that you're specifying in your SQL statement? In your example above, it sounds like you've passed in the string 'Test' as the field name; i.e. 'DELETE * From tblEmployees WHERE Test = ' & Me.UserList.Column(1)

But 'Test' is not the actual name of your field; it's the data within that field, right? I'm thinking that you don't even need the variable strEmpName as this part of your SQL won't be changing. Try this:

'DELETE * FROM tblEmployees WHERE Your_Field_Name_Goes Here = ' & Me.UserList.Column(1)

 

[satty]

T hanx DUGANTRAIN!

u've been a major HELP!!!! thanx a alot for all of it. i have finally got it to work.




:wink: Thanx


Satty

:wink: