Deleting using VBA

[AllyJM]

I have an access 2003 database table for songs containing the Artist's name and song title. On the form based on the table I have a combo box which is populated using a 'select distinct' statement. There is also a list box which displays all songs by selected artist. I am trying to find a way to delete a record based on the song I click on in the listbox, but it won't delete the correct record. Although the table is sorted, new entries are being appended often. I have tried using the acGoTo method but when I try to get the record's position in the table using the Find method, it comes up, say, number 11, when in fact on looking at the table itself I find the record is number 24 in the table, therefore when I try to delete it, the the wrong record gets deleted. There is only one table because I'm very inexperienced with relational databases. I'm not too bad with VBA in other programs, but Access is kinda tricky. Any suggestions?

 

[Ranman256]

make a delete query that looks at the list box.
docmd.openquery "qdDel1ListItm"

the sql = "delete * from table where [id]=forms!frmMain!lstBox

 

[AllyJM]

Thank you Ranman256. That works a treat. Cheers

 

[Acropolis]

Is deleting the right option? I find it fairly final and once confirmed it can't be undone. What happens if someone deletes the wrong record? You can't bring it back.

What I tend to do, is have a field in the table "isLive", default is 1, then instead of deleting records I mark them as isLive = 0, then for everywhere that looks for those records just add the criteria of WHERE isLive = 1, that way it can be undone if needs be.

 

[AllyJM]

That's an interesting idea. I hadn't thought about doing something like that, but it makes sense. There is no real need to delete anyway as the table isn't very big. Thanks Acropolis, I'll try that.