Question Update from List box

[MOTOWN44]

Now then all

Is it possible to run an update query based on a list box??

The list box displays all the staff that a team leader has assigned to them containing an ID number on the database [ID] the staff name and business ID (M Number)

I want the team leader to be able to select a member of staff from the list box and complete 2 unbound text boxes with the new TL name and Business ID press a button and it runs the update query to change the old TL info to the New stuff that was put in the unbound boxes.

I think im nearly there I have the update query set to the values in the unbound boxes as the update to value the problem im having is with it using the staff member selected in the list box as the “target”

My update query SQL is as follows

UPDATE tblNewStaff
SET tblNewStaff.[Team Leader Name] = forms.frmReassign.txtTLName, tblNewStaff.[Team Leader M Number] = forms.frmReassign.txtMNumber
WHERE (((tblNewStaff.ID)=forms.frmReassign.lstResults.ID));

It seems to be having a problem with the WHERE clause because it brings a box up asking for the ID instead of taking it from the selection I have made on the list box.

The problem with that (other than it doesn’t do what I want it to) is that a user can put any ID in the box and update the record regardless of weather it appears in the list box or not.

My SQL writing skills are little hit and miss at best so if there's an easer way to write the query any suggestions are welcome.

Thank you

Matthew

 

[jdraw]

Apart from your listbox issue, I'd strongly recommend that you get a naming convention that does not uses spaces nor special characters in column and object names. These will come back to haunt you.

As for your listbox, usually there is an Id number associated with the listbox in a column and a name/text field for display. When you select an entry, the link field (the field that goes into the table/query) is the number which is not usually displayed. If you do a little reading on list boxes and bound column, you'll notice that the bound column usually has a width of 0", so that it is not displayed.

I'd suggest you try a sample form and using the the built in wizard to set up a listbox. Then look at the properties. I can't seem to find a simple example....

You could look at the properties of your list box and see what the bound coloumn and width is-- then let us know.