Additional record added in access table

I have a subform that allows me to add a new student record in a link table, that part works ok but I run code on the after update which populates more detail of that last record and then adds a new record in a table that is linked to the link table (tblbalance all this part makes the addition to tblbalance ok), I believe when that happens I end up with a duplicated student entry in the link table but with no detail filled in just the name. I am not sure what event to use now.
all the separate sections get what they need in the after update code.

My after insert code is:

Private Sub Form_AfterInsert()
Dim db As DAO.Database
Set db = CurrentDb
Dim sPK As Integer
Dim StrQuery As String
Dim RstStudentMaxProduct As DAO.Recordset
Dim RstLink As DAO.Recordset
Dim RstTransaction As DAO.Recordset
Dim RstGroupWorth As DAO.Recordset
Dim RstLastInvNumber As DAO.Recordset
Dim RstTotalCreditsPurchased As DAO.Recordset
Dim RstFirstClass As DAO.Recordset

Dim CurrentProductNumber As Integer
Dim NextProductNumber As Integer
Dim TotalCreditsPuchased As Integer
Dim Program_PK As Integer
Dim Session_PK As Integer
Dim StrQuery3 As String
Dim StrQuery4 As String

Dim RstGroup As DAO.Recordset

Dim InvPrefix As String
Dim LastInvoiceNumber As String
Dim NextInvoiceNum As String
Dim GrpID As Long
Dim TotalCreditsPurchased As Integer
Dim TotalCourseCreditsIncTrials As Integer
DoCmd.SetWarnings False

Dim StrQueryFirstClass As String

sPK = Me.Student_FK

Dim GrpWorth As Integer

'lets get the next product number by getting last and adding one
StrQuery = "SELECT TblStudent_Session_Link.Student_FK, Max(TblStudent_Session_Link.ProductNum) AS CurrentProdNum" _
& " FROM TblStudent_Session_Link" _
& " GROUP BY TblStudent_Session_Link.Student_FK" _
& " HAVING (((TblStudent_Session_Link.Student_FK)=" & sPK & ") AND ((Max(TblStudent_Session_Link.ProductNum))<>0));"

Set RstLink = db.OpenRecordset("TblStudent_Session_Link", dbOpenDynaset)
Set RstStudentMaxProduct = db.OpenRecordset(StrQuery, dbOpenDynaset)

With RstStudentMaxProduct

If RstStudentMaxProduct.BOF And RstStudentMaxProduct.EOF Then
    NextProductNumber = 1
    CurrentProductNumber = 0

    NextProductNumber = .Fields("CurrentProdNum").Value + 1
    CurrentProductNumber = .Fields("CurrentProdNum").Value
End If

End With

'get the relevant data from the form so we update the correct details

Program_PK = [Forms]![ClassEntryForm]![TblClassSessions subform3]![TblStudent_Session_Link subform6].[Form]![Programme_FK].Value
Session_PK = [Forms]![ClassEntryForm]![TblClassSessions subform3]![TblStudent_Session_Link subform6].[Form]![Class_FK].Value
SGroup = [Forms]![ClassEntryForm]![Group].Value
DateD = [Forms]![ClassEntryForm]![TblClassSessions subform3].[Form]![ClassDate].Value

Set RstGroup = db.OpenRecordset("TblGroups", dbOpenDynaset)

With RstGroup
Do While Not RstGroup.EOF
    If SGroup = .Fields("Group").Value Then

    Group_ID = .Fields("Group_ID").Value
End If

End With

StrGroupWorth = "SELECT TblGroup_Students_Link.Student_ID, TblGroup_Students_Link.GroupCreditWorth, TblGroup_Students_Link.Group_ID" _
& " FROM TblGroup_Students_Link" _
& " WHERE (((TblGroup_Students_Link.Student_ID)=" & sPK & ") AND ((TblGroup_Students_Link.Group_ID)=" & Group_ID & "));"
Debug.Print StrGroupWorth
Set RstGroupWorth = db.OpenRecordset(StrGroupWorth, dbOpenDynaset)

With RstGroupWorth
    GrpWorth = .Fields("GroupCreditWorth").Value

End With

Dim LinkID As Long

With RstLink

LinkID = .Fields("Link_ID").Value

End With

Dim StrQuery2 As String

StrQuery2 = "UPDATE TblStudent_Session_Link SET TblStudent_Session_Link.ProductNum = '" & CStr(NextProductNumber) & "' , TblStudent_Session_Link.Group_FK='" & CStr(Group_ID) & "' WHERE" _
& "(((TblStudent_Session_Link.Class_FK)=" & Session_PK & ") AND ((TblStudent_Session_Link.Student_FK)=" & sPK & ") AND ((TblStudent_Session_Link.Programme_FK)=" & Program_PK & " ));"
Debug.Print StrQuery2
DoCmd.RunSQL (StrQuery2)

Set RstTransaction = db.OpenRecordset("TblBalance", dbOpenDynaset)

With RstTransaction


RstTransaction![Qty] = GrpWorth * -1
RstTransaction![Student_FK] = sPK
RstTransaction![Type] = "DB Class"
RstTransaction![EventDate] = DateD
RstTransaction![Session_FK] = Session_PK
RstTransaction![ProdNum] = NextProductNumber
RstTransaction![Link_FK] = LinkID

'End With

DoCmd.SetWarnings True

End Sub

Open in new window

the tables
the action is
exThe form here is where I add a student to that particular session_id in the link table
Who is Participating?
NEVER, EVER change the current record in the form's AfterUpdate event.  That causes Access to go through its save cycle again.  I will run the BeforeUpdate event and then the AfterUpdate event, dirty the current record causing it to run the BeforeUpdate event and then the AfterUpdate event which dirties the record again causing ---- you get the message.  Earlier versions of Access used to freeze because they couldn't break out of this endless loop.  Newer versions are smarter and don't freeze.

I'm not at all surprised about the duplicate records.  Rework the code so that any final validation and updating is done in the form's BeforeUpdate event.  The AfterUpdate event is used for things you need to do when you are finished with updating such as sending an email or printing or creatng an audit log.
PeterBaileyUkAuthor Commented:
aha ok I anticipated that the event wasnt right, ok so I shift over to the before update event. One question on that I need the latest session id which i got here
Dim LinkID As Long

With RstLink

LinkID = .Fields("Link_ID").Value

End With

Open in new window

That link Id then allowed me to add that to tblbalance to create that record but in the before update event has that link id been created at that point?
If you are using Jet or ACE, the autonumber is created as soon as the first character is typed in to any control dirtying the form.  If you show the autonumber on the form, you will see this happen.  If you are linked to SQL Server or some other RDBMS, you are disconnected from the database and the identity column is not generated until Access sends the insert to the server and gets the ID which is returned from the server.  

I didn't read your code carefully.  You said you were modifying the record you were sitting on in the form.  You cannot do that in the AfterUpdate event as I explained.  But if you are creating a change log or something like that where you need the ID of the current record but you are not updating the current  record, then you would use the form's AfterUpdate event.

Doing something like a .moveLast isn't going to necessarily get you the ID of the record you just inserted unless you are the only person using the database at that point in time.  This simply doesn't work in a multi-user environment.  As long as you are using a bound form and the identity column is in the form's ControlSource, it will be populated by the time the AfterUpdate event runs.

If you are usind DAO or ADO to open a recordset and insert a row, you need to retrieve the identity column using this method:
            rs!ProcedureID = Me.lstProviders.Column(5, i) 'Me.cboProcedureID
            rs!ProviderID = Me.lstProviders.Column(6, i) 'Me.ProviderID
            rs!EffDT = Me.txtNewEffDT
            rs!PricePerUnit = Me.txtNewPricePerUnit
            rs!AppliedDT = Date                 'update providerprocedures to record that rate change was applied
            rs!ChangeBy = Forms!frmLogin!txtEmpID
            rs!ChangeDT = Now()
'            NewProvProcID = rs!ProvProcID
            Set rsScope = db.OpenRecordset("SELECT @@IDENTITY as NewID")

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.