Listbox adds criteria to Delete query

[jim11]

Hi all,

Im new to access and i need some help. This is my situation. I have a listbox (called list0) on a form (called frmDeleteClientContact). The listbox displays a list of clients by Lastname and Firstname from table tblClient. tblClient contains other fields but they are not relevent as all i need is Lastname and Firstname for the query. This is my row source for the listbox:

SELECT tblClient.ClientID, tblClient.LastName, tblClient.FirstName FROM tblClient ORDER BY [LastName];

So the listbox displays data like this from tblClient:
Lastname Firstname. Eg. Littlewood Rob.

I am creating a Delete action query that has all the fields from tblClient in it. I want the fields Lastname and Firstname to hold the criteria for the query. So when a client is selected in the listbox (list0) the lastname in the listbox is the criteria for Lastname and the firstname in the listbox is the criteria for Firstname if you understand me. So then when the query is run it deletes the selected record in the tblClient and the listbox is updated so the deleted client is no longer displayed. I can get the delete query working if i put [Please Enter Surname of client to delete:] into the criteria of the Lastname field. But i want it so it runs through a form and both the Firstname & Lastname is selected from the listbox.

I really am stuck,
Any help would be greatly appriciated.

Thanks
Jim

 

[pbaldy]

If the listbox is single select, something like this should work:

DELETE * FROM tblClient
WHERE ClientID = Forms!frmDeleteClientContact.list0

This obviously uses the client ID selected, rather than the name, as the ID should be more precise.

 

[jim11]

ah yeh. That would work well and would be alot easier to do, however i want my system user to see the client lastname and client firstname so they can relate to who the client actually is, because they wont be able to tell which client is which by looking at the ID. Is there a way i can have the listbox with ClientID in it, but also lookup client firstname and surname as well so the system user can see who they are deleting?

Thanks alot
Jim

 

[jim11]

Got it working , it was a mix of what you said.
This is what i did:-

The Row Source for List0 is:

SELECT tblClient.ClientID, tblClient.LastName, tblClient.FirstName FROM tblClient ORDER BY [LastName];

This displays the clientID, lastname, firstname in the listbox, i set the first column (ClientID to 0cm), therefore i could see Surname and Forename. I also set bound column to 1.

Then in my delete query i used the SQL syntax of:-

DELETE * FROM tblClient
WHERE ClientID = Forms!frmDeleteClientContact.list0

It worked

Thanks for all the help
Jim

 

[pbaldy]

So my SQL was right, huh?

According to your first post, ClientID was already the first column of the row source of the listbox, so I assumed it was the bound column. Looks like that assumption was wrong. The way you described how the listbox displayed in the first post indicated ClientID was already set not to display.

 

[jim11]

I think it was m8, ur right. I get confused easily. The bit i was wrong with was the syntax for qdelDeleteClient query. Ur syntax was right and when i replaced it with the old syntax it worked.

Thanks for all ur help.

ps. No doubt u will b seeing me on these forums again many a times with different problems untill i get my system running lol.