Delete selected value from listbox

Hi,
I am trying to run a delete sql statement. i want it to delete from a list box, and delete the one which is currently selected.
i have tried a few things, but hane not had success

Please help[

What have you tried?

Hi,
I am trying to run a delete sql statement. i want it to delete from a list box, and delete the one which is currently selected.
i have tried a few things, but hane not had success

Please help[

What is the row source of the listbox (in SQL)?

My list is called lstViewRequest, and this is i have added a delete query into is, and right now, it will ask you for the record you weant to delete.
this is the code:

You can't refer to the selected item on the list like this

Try something like this ...
Mary

that still asks for the ID as well, but this time, the message that pops up is the name this is linked with the record ID.
so if record 10 is selected, the the name (i.e. Frank) comes up on the message box.
Is there no way for that message box not to come up, but jus remove automatically

that still asks for the ID as well, but this time, the message that pops up is the name this is linked with the record ID.
so if record 10 is selected, the the name (i.e. Frank) comes up on the message box.
Is there no way for that message box not to come up, but jus remove automatically

The listbox is obviously bound to the name and not the ID.

What is the Row Source SQL of the listbox?
What is the Bound Column Set to?

this is the sql statement:

and youre right, it is bounded to column 2, the name field.
Will I have to remove that list, and add another list, and make it have no bounds

this is the sql statement:

and youre right, it is bounded to column 2, the name field.
Will I have to remove that list, and add another list, and make it have no bounds.

The reason why i bounded it is so that the information on that filed goes there.
I was typing sql insert statements in sql, but it wasnt working.

basically, i have two lists there, both which add to the actual form (wihci is linked to the a job table). The first list box adds the person who is going to participate in that job, and the second list adds the service that is being provided for that job.

this is the sql statement:

SELECT tblRequest.[Request ID], tblRequest.[Request By], tblRequest.[Request Description], tblRequest.[Requested Date], tblRequest.Budget, tblRequest.[Duration (Hours)] FROM tblRequest;

and youre right, it is bounded to column 2, the name field.
Will I have to remove that list, and add another list, and make it have no bounds

Just set the Bound column to column 1 and try running the code I gave you again.

Just set the Bound column to column 1 and try running the code I gave you again.

If i bind it there, it wont enable me to add whats in that field to the required field.

Change to ...

In code the columns start at 0

Mary

yay. thank you very much. it works.

PS: wen an update or a delte query is run, message box comes up confirming this, is there a possible way to avoid or stop these messages from comming up

You're welcome. The following additions will turn the warning off for the duration of the query.

Mary

Thank you.
Sorry to be a bother, but i have one more question.
i have a login page, where members can log, and their user name and password is fetched from the members table.
I want to have 2 different authorisation level.
One members authorisation, so wen they login, only their records will show up, no others, and they cannot access it as well, and one admin level, where obviously they can do whateva they please.

Any suggestions?

You're welcome. The following additions will turn the warning off for the duration of the query.

Expand|Select|Wrap|Line Numbers

1. Private Sub cmdSaveRecord_Click()
2. On Error GoTo Err_cmdSaveRecord_Click
3. Dim strSQL As String
4.  
5.    DoCmd.SetWarnings False
6.    strSQL = "DELETE * " & _
7.       "FROM [tblRequest] " & _
8.       "WHERE [tblRequest].[Request ID]=" & Me![lstViewRequest].Column(0)
9.    DoCmd.RunSQL strSQL
10.    Me![lstViewRequest].Requery ' requery the list
11.  
12.    DoCmd.SetWarnings True
13.  
14. Exit_cmdSaveRecord_Click:
15.  
16. Err_cmdSaveRecord_Click:
17.        Exit Sub
18.    MsgBox Err.Description
19.    Resume Exit_cmdSaveRecord_Click
20.  
21.     End Sub
22.  

Mary

Hi,
i have used this code and found it very useful so thankyou very much.
however when i click to delete the item from the list, a Message box appears asking me to enter a prameter 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?

any help gratefully received,
Thanks

Hello, hooijdonk.

Please refrain from posting your questions in another member's thread in the future. It results in your question not being viewed nor understood. This is also called thread hijacking and isn't allowed per the site rules. Go ahead and start a new thread explaining your situation and if needed include a hyperlink to the old thread but copying the URL in your browser's URL window, at the top of the Message window on this site you will see an icon that looks like a globe. Clicking on it will show the URL tag dialog box. Paste your URL in the second window of the dialog box and click OK.

Thanks,
Scott MODERATOR