recordset - VBA on click_ delete records of a table -

i trying delete records of table rather deleting records of form. have following code, not work:

please can help.

private sub cmd_x_click()  dim db dao.database dim rs dao.recordset dim rscount integer  dim bizno field dim bank_role field  dim i, j integer  set db = currentdb() set rs_date = db.openrecordset("trd_pricing-in date_real")  set pe_id = rs_date.fields("pricing_element_id") rs_date.movelast rs_datecount = rs_date.recordcount  msgbox (rs_datecount) msgbox (me.pricing_element_id) msgbox (pe_id.value)  rs_date.movelast   = 1 rs_datecount      if me!pricing_element_id = pe_id     rs_date.delete     else     rs_date.moveprevious     end if  next   end sub 

in loop not comparing right elements. when do:

set pe_id = rs_date.fields("pricing_element_id") 

you set pe_id value of pricing_element_id of first record. intend set reference it, , everytime recordset advances next record, want reference updated. that's not how works. have field in current record every time advance recordset. have write:

if me!pricing_element_id = rs_date.fields("pricing_element_id") 

note: experience found count of recordset not accurate. rather loop, use while loop:

while (not eof(rs_date))     if me!pricing_element_id = rs_date.fields("pricing_element_id")         rs_date.delete     else         rs_date.movenext     end if wend 

note there no need proces recordset last first; advance next until eof.