Deleting List item using Form

[hooijdonk]

Hi,
using a form I have been trying to get a list box to display the values of database table and have two options on there.
a) the user can select a list item and click a button to delete that item
b) the user can type text into a text box and click another button to add that value to the list box (and therefore the table sitting behind it)

i found a very useful starting point here http://www.thescripts.com/forum/thread634077.html

however, when i click to delete the item from the list, a Message box appears asking me to enter a parameter value. If the value entered is exactly the same as item chosen on the list then the item is removed, otherwise 0 items get removed. have i not done something right?

Private Sub cmdDelConsultant_Click()
On Error GoTo Err_cmdDelConsultant_Click
Dim strSQL As String
 
   strSQL = "DELETE * " & _
      "FROM [Consultant] " & _
      "WHERE [Consultant]=" & [lstConsultant].Column(0)
   DoCmd.RunSQL strSQL
   Me![lstConsultant].Requery ' requery the list
 
Exit_cmdDelConsultant_Click:
 
Err_cmdDelConsultant_Click:
       Exit Sub
   MsgBox Err.Description
   Resume Exit_cmdDelConsultant_Click
 
 
 
End Sub

any help much appreciated,
Thanks hooijdonk

[convexcube]

Hi,
using a form I have been trying to get a list box to display the values of database table and have two options on there.
a) the user can select a list item and click a button to delete that item
b) the user can type text into a text box and click another button to add that value to the list box (and therefore the table sitting behind it)

i found a very useful starting point here http://www.thescripts.com/forum/thread634077.html

however, when i click to delete the item from the list, a Message box appears asking me to enter a parameter value. If the value entered is exactly the same as item chosen on the list then the item is removed, otherwise 0 items get removed. have i not done something right?

Private Sub cmdDelConsultant_Click()
On Error GoTo Err_cmdDelConsultant_Click
Dim strSQL As String
 
   strSQL = "DELETE * " & _
      "FROM [Consultant] " & _
      "WHERE [Consultant]=" & [lstConsultant].Column(0)
   DoCmd.RunSQL strSQL
   Me![lstConsultant].Requery ' requery the list
 
Exit_cmdDelConsultant_Click:
 
Err_cmdDelConsultant_Click:
       Exit Sub
   MsgBox Err.Description
   Resume Exit_cmdDelConsultant_Click
 
 
 
End Sub

any help much appreciated,
Thanks hooijdonk

Hi Hooijdonk,

Would you be able to provide the source for your list box? and if different the structure of the underlying table?

Kind Regards,
Ken Farrawell.

[ADezii]

Hi,
using a form I have been trying to get a list box to display the values of database table and have two options on there.
a) the user can select a list item and click a button to delete that item
b) the user can type text into a text box and click another button to add that value to the list box (and therefore the table sitting behind it)

i found a very useful starting point here http://www.thescripts.com/forum/thread634077.html

however, when i click to delete the item from the list, a Message box appears asking me to enter a parameter value. If the value entered is exactly the same as item chosen on the list then the item is removed, otherwise 0 items get removed. have i not done something right?

Private Sub cmdDelConsultant_Click()
On Error GoTo Err_cmdDelConsultant_Click
Dim strSQL As String
 
   strSQL = "DELETE * " & _
      "FROM [Consultant] " & _
      "WHERE [Consultant]=" & [lstConsultant].Column(0)
   DoCmd.RunSQL strSQL
   Me![lstConsultant].Requery ' requery the list
 
Exit_cmdDelConsultant_Click:
 
Err_cmdDelConsultant_Click:
       Exit Sub
   MsgBox Err.Description
   Resume Exit_cmdDelConsultant_Click
 
 
 
End Sub

any help much appreciated,
Thanks hooijdonk

Try (pay particular attention to Line #7):

Private Sub cmdDelConsultant_Click()
On Error GoTo Err_cmdDelConsultant_Click
Dim strSQL As String
 
   strSQL = "DELETE * " & _
      "FROM [Consultant] " & _
      "WHERE [Consultant]='" & Me![lstConsultant].Column(0) & "'"
   DoCmd.RunSQL strSQL
   Me![lstConsultant].Requery ' requery the list
 
Exit_cmdDelConsultant_Click:
 
Err_cmdDelConsultant_Click:
       Exit Sub
   MsgBox Err.Description
   Resume Exit_cmdDelConsultant_Click
End Sub

[hooijdonk]

Thanks for your replies. Sorry i should have stated that the code i place above was part of a working solution. Firstly i had the following code

Private Sub cmdDelConsultant_Click()
On Error GoTo Err_cmdDelConsultant_Click
Dim strSQL As String
 
   strSQL = "DELETE 'Consultant' " & _
      "FROM [Consultant] " & _
      "WHERE [Consultant].[Request ID] =" & Me![lstConsultant].Column(0)
   DoCmd.RunSQL strSQL
   Me![lstConsultant].Requery ' requery the list
 
Exit_cmdDelConsultant_Click:
 
Err_cmdDelConsultant_Click:
       Exit Sub
   MsgBox Err.Description
   Resume Exit_cmdDelConsultant_Click
 
End Sub

this prompted for the Parameter Value Consultant.Request ID and then for the Parameter Value of the selected Item. If both values entered were identical and in the list then the whole list would be deleted! To move on from here i changed the SQL statement to

"WHERE [Consultant] =" & Me![lstConsultant].Column(0)

this just asked for one parameter value (in the prompt box the value selected in the list is also displayed) and if the value entered matched the selected list item then this single item would be deleted. I therefore think one side of the WHERE clause isn't working correctly, however i'm not sure which side or why.

The listbox was just created using the wizard function and table structure is just a single column 'Consultant' from which i asked the listbox wizard tool to read the values of. Not sure if that helps. Sorry am still getting to grips with VB and VBA

[blad3runn69]

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
if u use forms use unbound controls then it is easy to delete anything in any record...

[ADezii]

Thanks for your replies. Sorry i should have stated that the code i place above was part of a working solution. Firstly i had the following code

Private Sub cmdDelConsultant_Click()
On Error GoTo Err_cmdDelConsultant_Click
Dim strSQL As String
 
   strSQL = "DELETE 'Consultant' " & _
      "FROM [Consultant] " & _
      "WHERE [Consultant].[Request ID] =" & Me![lstConsultant].Column(0)
   DoCmd.RunSQL strSQL
   Me![lstConsultant].Requery ' requery the list
 
Exit_cmdDelConsultant_Click:
 
Err_cmdDelConsultant_Click:
       Exit Sub
   MsgBox Err.Description
   Resume Exit_cmdDelConsultant_Click
 
End Sub

this prompted for the Parameter Value Consultant.Request ID and then for the Parameter Value of the selected Item. If both values entered were identical and in the list then the whole list would be deleted! To move on from here i changed the SQL statement to

"WHERE [Consultant] =" & Me![lstConsultant].Column(0)

this just asked for one parameter value (in the prompt box the value selected in the list is also displayed) and if the value entered matched the selected list item then this single item would be deleted. I therefore think one side of the WHERE clause isn't working correctly, however i'm not sure which side or why.

The listbox was just created using the wizard function and table structure is just a single column 'Consultant' from which i asked the listbox wizard tool to read the values of. Not sure if that helps. Sorry am still getting to grips with VB and VBA

convexcube had the right idea, kindly Post the Row Source for your List Box.

[hooijdonk]

convexcube had the right idea, kindly Post the Row Source for your List Box.

sorry i'm not sure what you mean by the row source. the Consultants table behind the listbox currently just has dummy values in of single and multiple random characters. In the properties of the Listbox the row source is simply Consultant. I'm aware this probably isn't what you mean?

thanks again for the replies,
Hooijdonk

[ADezii]

sorry i'm not sure what you mean by the row source. the Consultants table behind the listbox currently just has dummy values in of single and multiple random characters. In the properties of the Listbox the row source is simply Consultant. I'm aware this probably isn't what you mean?

thanks again for the replies,
Hooijdonk

If the Table Name is Consultants, then the Row Source should be the name of the Table itself, 'Consultants' and not 'Consultant'. Kindly post the Fields in the Consultants Table along with their Data Types in the proper order.

[hooijdonk]

Hi yes sorry about the error there, the table is Consultant and it currently just has one field 'Consultant' and it's data type is simply Text with field size 50.
could adding the ID field and somehow using that in part of the WHERE query help?

[ADezii]

Hi yes sorry about the error there, the table is Consultant and it currently just has one field 'Consultant' and it's data type is simply Text with field size 50.
could adding the ID field and somehow using that in part of the WHERE query help?

Given your statements, then:

Dim strSQL As String
 
strSQL = "DELETE [Consultant] FROM Consultant WHERE " & _
          "Consultant.[Consultant] = '" & Me![lstConsultant] & "'"
DoCmd.RunSQL strSQL
 
Me![lstConsultant].Requery ' requery the list

BTW, it is seldom a good idea to give your Table, and a Field contained within it, the same Name.