Hi again!
I am developing a database that supports a video store. I have a table named tblInvoiceDetails that is in a one-to-one relation with a table named tblLateFees and the foreign key is in the tblInvoiceDetails. When a customer rents a video and after some days goes back to the store to return the video the employee must open his invoice and in the invoice detail section he must enter the returned date. I have a procedure that after the ReturnDate is updated is checks if the video has been returned overdue. If so, a new record is created in the tblLateFees and the foreign key in the tblInvoceDetails gets this new record's primary key. This InvoiceDetails subform is displayed in datasheet view and I want this changes to be automatically reflected. The problem is that when the new record is inserted in late fees and the foreign key field is updated with the primary key the changes are not automatically reflected and a message appears saying that my record has been changed by another user since I started editing it and asks me if I want to copy my changes to the clipboard or save the record or drop the changes. What should I do to avoid all of this? I just want my record to be updated and show this changes while I am editing it. Here is the code that executes the actions if a video is returned overdue:
Private Sub txtReturnDate_AfterUpdate()
'If the Return date is after the dueback date a late fee is calculated and related to the current invoice
If txtReturnDate.Value > DueBack.Value Then
Dim daysLate As Integer
Dim strSQL As String
Dim LateFeeAmount As Integer
Dim temp_fk_val As Integer
daysLate = ReturnDate.Value - DueBack.Value
LateFeeAmount = daysLate * PricePerUnitRental.Value 'Calculates the late fee amount
If LateFeeAmount >= PurchasePrice.Value Then LateFeeAmount = PurchasePrice.Value
strSQL = "INSERT INTO tblLateFees (LateFeeAmount) VALUES (" & LateFeeAmount & ");"
DoCmd.RunSQL (strSQL)
strSQL = "SELECT max(LateFeeID) As LastAdded FROM tblLateFees;" 'Get the lastest added record
temp_fk_val = CurrentDb.OpenRecordset(strSQL).Fields("LastAdded")
strSQL = "UPDATE tblInvoiceDetails SET LateFeeID = " & temp_fk_val & " WHERE InvoiceID = " & InvoiceID.Value & " AND VideoCopyID = " & VideoCopyID.Value & ";"
MsgBox temp_fk_val
DoCmd.RunSQL (strSQL)
End If
End Sub
I am developing a database that supports a video store. I have a table named tblInvoiceDetails that is in a one-to-one relation with a table named tblLateFees and the foreign key is in the tblInvoiceDetails. When a customer rents a video and after some days goes back to the store to return the video the employee must open his invoice and in the invoice detail section he must enter the returned date. I have a procedure that after the ReturnDate is updated is checks if the video has been returned overdue. If so, a new record is created in the tblLateFees and the foreign key in the tblInvoceDetails gets this new record's primary key. This InvoiceDetails subform is displayed in datasheet view and I want this changes to be automatically reflected. The problem is that when the new record is inserted in late fees and the foreign key field is updated with the primary key the changes are not automatically reflected and a message appears saying that my record has been changed by another user since I started editing it and asks me if I want to copy my changes to the clipboard or save the record or drop the changes. What should I do to avoid all of this? I just want my record to be updated and show this changes while I am editing it. Here is the code that executes the actions if a video is returned overdue:
Private Sub txtReturnDate_AfterUpdate()
'If the Return date is after the dueback date a late fee is calculated and related to the current invoice
If txtReturnDate.Value > DueBack.Value Then
Dim daysLate As Integer
Dim strSQL As String
Dim LateFeeAmount As Integer
Dim temp_fk_val As Integer
daysLate = ReturnDate.Value - DueBack.Value
LateFeeAmount = daysLate * PricePerUnitRental.Value 'Calculates the late fee amount
If LateFeeAmount >= PurchasePrice.Value Then LateFeeAmount = PurchasePrice.Value
strSQL = "INSERT INTO tblLateFees (LateFeeAmount) VALUES (" & LateFeeAmount & ");"
DoCmd.RunSQL (strSQL)
strSQL = "SELECT max(LateFeeID) As LastAdded FROM tblLateFees;" 'Get the lastest added record
temp_fk_val = CurrentDb.OpenRecordset(strSQL).Fields("LastAdded")
strSQL = "UPDATE tblInvoiceDetails SET LateFeeID = " & temp_fk_val & " WHERE InvoiceID = " & InvoiceID.Value & " AND VideoCopyID = " & VideoCopyID.Value & ";"
MsgBox temp_fk_val
DoCmd.RunSQL (strSQL)
End If
End Sub
