Delete issue

[compwizard]

hello all,

Just a quick query

I currently have an hires form where clients place orders,
When an item is selected this would drop the item within a listbox and save within a itemhireline table. If clear is selected half way through an order i want the items within the listbox to be deleted from the tblitemhireline so when the next order is placed the listbox should be empty. It sounds pretty simple but i cannot get it to delete all the items that are linked to this itemhireno.

This is what i have been using - it only deletes one item every time the form is cleared

Set rstclear = database.OpenRecordset("Select * from itemhireline Where itemhirno=" & Forms!frmitemhireform!txtitemhireno)
rstclear.Delete

[Megalog]

Set rstclear = database.OpenRecordset("Select * from itemhireline
Where itemhirno=" & Forms!frmitemhireform!txtitemhireno)
rstclear.Delete

Try something like this... There's probably a better way to handle the looping than using a goto.. <shrug> Back up your data first!

Dim rstclear as DAO.Recordset
Dim itm as Variant
 
Set rstclear = CurrentDb.OpenRecordset("itemhireline", dbOpenDynaset)
Repeat:
rst.FindFirst "itemhirno = " & [Forms]![frmitemhireform].[txtitemhireno]
if rst.nomatch = False Then
     rstclear.Delete
     goto Repeat
end if
Set rstclear = nothing

[NeoPa]

Do [criteria]
...
Loop [criteria]

While [criteria]
...
Wend

For Var = X To Y [Step Z]
...
Next Var