By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

Deleting List item using Form

P: 5
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelConsultant_Click()
  2. On Error GoTo Err_cmdDelConsultant_Click
  3. Dim strSQL As String
  4.  
  5.    strSQL = "DELETE * " & _
  6.       "FROM [Consultant] " & _
  7.       "WHERE [Consultant]=" & [lstConsultant].Column(0)
  8.    DoCmd.RunSQL strSQL
  9.    Me![lstConsultant].Requery ' requery the list
  10.  
  11. Exit_cmdDelConsultant_Click:
  12.  
  13. Err_cmdDelConsultant_Click:
  14.        Exit Sub
  15.    MsgBox Err.Description
  16.    Resume Exit_cmdDelConsultant_Click
  17.  
  18.  
  19.  
  20. End Sub
any help much appreciated,
Thanks hooijdonk
Feb 14 '08 #1
Share this Question
Share on Google+
9 Replies


convexcube
P: 47
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelConsultant_Click()
  2. On Error GoTo Err_cmdDelConsultant_Click
  3. Dim strSQL As String
  4.  
  5.    strSQL = "DELETE * " & _
  6.       "FROM [Consultant] " & _
  7.       "WHERE [Consultant]=" & [lstConsultant].Column(0)
  8.    DoCmd.RunSQL strSQL
  9.    Me![lstConsultant].Requery ' requery the list
  10.  
  11. Exit_cmdDelConsultant_Click:
  12.  
  13. Err_cmdDelConsultant_Click:
  14.        Exit Sub
  15.    MsgBox Err.Description
  16.    Resume Exit_cmdDelConsultant_Click
  17.  
  18.  
  19.  
  20. 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.
Feb 14 '08 #2

ADezii
Expert 5K+
P: 8,624
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelConsultant_Click()
  2. On Error GoTo Err_cmdDelConsultant_Click
  3. Dim strSQL As String
  4.  
  5.    strSQL = "DELETE * " & _
  6.       "FROM [Consultant] " & _
  7.       "WHERE [Consultant]=" & [lstConsultant].Column(0)
  8.    DoCmd.RunSQL strSQL
  9.    Me![lstConsultant].Requery ' requery the list
  10.  
  11. Exit_cmdDelConsultant_Click:
  12.  
  13. Err_cmdDelConsultant_Click:
  14.        Exit Sub
  15.    MsgBox Err.Description
  16.    Resume Exit_cmdDelConsultant_Click
  17.  
  18.  
  19.  
  20. End Sub
any help much appreciated,
Thanks hooijdonk
Try (pay particular attention to Line #7):
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelConsultant_Click()
  2. On Error GoTo Err_cmdDelConsultant_Click
  3. Dim strSQL As String
  4.  
  5.    strSQL = "DELETE * " & _
  6.       "FROM [Consultant] " & _
  7.       "WHERE [Consultant]='" & Me![lstConsultant].Column(0) & "'"
  8.    DoCmd.RunSQL strSQL
  9.    Me![lstConsultant].Requery ' requery the list
  10.  
  11. Exit_cmdDelConsultant_Click:
  12.  
  13. Err_cmdDelConsultant_Click:
  14.        Exit Sub
  15.    MsgBox Err.Description
  16.    Resume Exit_cmdDelConsultant_Click
  17. End Sub
Feb 14 '08 #3

P: 5
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelConsultant_Click()
  2. On Error GoTo Err_cmdDelConsultant_Click
  3. Dim strSQL As String
  4.  
  5.    strSQL = "DELETE 'Consultant' " & _
  6.       "FROM [Consultant] " & _
  7.       "WHERE [Consultant].[Request ID] =" & Me![lstConsultant].Column(0)
  8.    DoCmd.RunSQL strSQL
  9.    Me![lstConsultant].Requery ' requery the list
  10.  
  11. Exit_cmdDelConsultant_Click:
  12.  
  13. Err_cmdDelConsultant_Click:
  14.        Exit Sub
  15.    MsgBox Err.Description
  16.    Resume Exit_cmdDelConsultant_Click
  17.  
  18. 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
Expand|Select|Wrap|Line Numbers
  1. "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
Feb 15 '08 #4

P: 59
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
if u use forms use unbound controls then it is easy to delete anything in any record...
Feb 15 '08 #5

ADezii
Expert 5K+
P: 8,624
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelConsultant_Click()
  2. On Error GoTo Err_cmdDelConsultant_Click
  3. Dim strSQL As String
  4.  
  5.    strSQL = "DELETE 'Consultant' " & _
  6.       "FROM [Consultant] " & _
  7.       "WHERE [Consultant].[Request ID] =" & Me![lstConsultant].Column(0)
  8.    DoCmd.RunSQL strSQL
  9.    Me![lstConsultant].Requery ' requery the list
  10.  
  11. Exit_cmdDelConsultant_Click:
  12.  
  13. Err_cmdDelConsultant_Click:
  14.        Exit Sub
  15.    MsgBox Err.Description
  16.    Resume Exit_cmdDelConsultant_Click
  17.  
  18. 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
Expand|Select|Wrap|Line Numbers
  1. "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.
Feb 15 '08 #6

P: 5
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
Feb 15 '08 #7

ADezii
Expert 5K+
P: 8,624
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.
Feb 15 '08 #8

P: 5
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?
Feb 18 '08 #9

ADezii
Expert 5K+
P: 8,624
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "DELETE [Consultant] FROM Consultant WHERE " & _
  4.           "Consultant.[Consultant] = '" & Me![lstConsultant] & "'"
  5. DoCmd.RunSQL strSQL
  6.  
  7. 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.
Feb 19 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.