Delete Row from List Box 1

[Ajwebb]

Hi All,

I have a form with a list box which the control source for the list box is a query. Is it possible to delete the selected row from the List box from clicking a command button.

I have looked at the commands available but none of them seem to be able to do the task. Maybe it will need to be done in code.

Thanks in Advance All


Anthony

 

[jgoodman00]

If the listbox is bound to a query or select statement then calling

controlname.requery should update its contents & thus remove any non-existant entries...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[Ajwebb]

Thanks James,

Any ideas on how to delete the row though from my form with the list box?

Regards

Anthony

 

[jgoodman00]

D'Oh! Misread the question.

Yes, assuming a single value is selected you should be able to do this:

I created a form with a listbox control & a command button. The listbox was named lstTest:
If IsNull(Me.lstTest) Then
MsgBox "You must select a value", vbCritical
Else
'DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM Test WHERE TestID = " & Me.lstTest
'DoCmd.SetWarnings True
Me.lstTest.Requery
End If


The underlying table was called Test.
I also commented out the DoCmd.SetWarnings code so that a warning is still displayed.

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[Ajwebb]

Thanks James,

Really appreciated.

Ant

 

[Ajwebb]

Just one other thing,

How do i go about the above if i have three Primary Keys in my table.

Thanks

Anthony

 

[jgoodman00]

Hmm, for this reason I always try to give my tables an autonumber column, even if it is not set as the primary key.

However, you should still be able to do the above, but you might need to modify the WHERE clause so that it is something like:

"WHERE (Field1 = " & me.lstField.Column & " AND Field2 = " & me.lstField.Column

And so on...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[Ajwebb]

Thanks Again James.

Ant