select only the currect records in a table and use it to update another table

[Emmanuel Ayivi]

I am creating a billing system for a school with Ms Access. When I prepare the first Academic year and term bill the code below works well. After this, If I intend to prepare the second term and any other next academic and termly bill the code duplicates all the previous data, before adding the new(expected)da ta. This previous data need not to be added. This is not a duplicate but similar record.

In other words, I have a table "tblfeesdetails " which contains the bills for all classes or leveles by term and Academic year and applied or updated from a command button. Now this bill must be updated into "tblfeestra ns" by names of pupils in the respective above classes or levels. It works well for the 1 term but duplicate the previous or existing records in the "tblfeesdetails " into "tblfeestra ns" before updating the new ones. The previous records once updated no longer needed to be updated for any other time. Rather the current records base on term and academic year in "tblfeesdetails " is what is needed to used by the code below. Find hi-lighted in the attached is a snapshot of the tables silimar (duplicated) records for your perusal. please help me.



strsqldetails = "Select * from tblfeesdetails where groupid = """ & mgroupid & """; "
Set rstd = CurrentDb.OpenR ecordset(strsql details)
With rstd


Do Until .EOF
mtrans_code = !TRANS_CODE
mdebit = !DEBIT
mcredit = 0
With rstfeestrans
.AddNew
!idno = macctno
!CLASSID = mclassid
!academic_year = macademic
!term = mterm
!TRANS_CODE = mtrans_code
!DEBIT = Nz(mdebit)
!CREDIT = 0
!TDATE = mdate
.Update
End With
If Not .EOF Then
.MoveNext
End If
Loop

End With