code to delete record in a subform from a main form

[chuckgoss]

Hi all...

I have a need to know what the code would be to delete a record in a subform from a delete button in the main form. There may be a few records there that are related to the mainform, but I'm pretty confident that I could get all of them, if I could only get to the first one and tell it to go away. Any ideas?

Thanks in advance,

chuck

 

[chrismcbride]

Given the information you list I can figure how to delete all records in the sub form's recordsource that are related to the current record in the master form. Also deleting the first relatated record can be done...

1. DoCmd.RunSql "DELETE * FROM tblWhatever WHERE rec_no = " & Forms!frmMaster!uid & ";"

This will work where the sub form's source table is tblWhatever and the foreign key of that table is rec_no and rec_no. The primary key of the master table is called uid and there is a bound control on the master form that is called uid.

If you want to delete a particular value then the easiest way is to populate a recordset with all the sub form values that are related to the current master record. Then move to the first record and run the delete then.

dim dbs as database
dim rst as recordset
dim strSql as string
dim tempID as Long

set dbs = currentdb
strSql = "SELECT * FROM tblWhatever WHERE rec_no = " & forms!frmMaster!uid & "' ORDER BY trans_no;"

set rst = dbs.Openrecordset(strSql, dbOpenDynaset)

If rst.RecordCount > 0 then
rst.move last
rst.movefirst

tempID = rst!trans_no
docmd.setwarnings False
docmd.runsql "DELETE * FROM tblWhatever WHERE trans_no = " & tempID & ";"

End if

docmd.setwarnings true
set rst = nothing
set dbs = nothing


Give these a try and I hope they are helpful
Chris

 

[chuckgoss]

Thanks for your quick reply Chris...I try those when I get to work!

chuck