PeterBaileyUk
asked on
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:
the tables
the action is
The form here is where I add a student to that particular session_id in the link table
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
Else
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
.MoveFirst
Do While Not RstGroup.EOF
If SGroup = .Fields("Group").Value Then
Group_ID = .Fields("Group_ID").Value
End If
.MoveNext
Loop
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
.MoveLast
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
.AddNew
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
.Update
'
'
'
'End With
DoCmd.SetWarnings True
End Subthe tables
the action is
The form here is where I add a student to that particular session_id in the link table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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.AddNew
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
rs.Update
Set rsScope = db.OpenRecordset("SELECT @@IDENTITY as NewID")
ASKER
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?