Why Me.Requery fails inside After_Insert?

[prabha_friend]

Private Sub Form_AfterInsert()
Dim CurrentTransDate As Date
CurrentTransDate = Me.DateVal.Value
Dim Transet As Recordset
Set Transet = CurrentDb.OpenRecordset("SELECT Amount FROM Transactions where To=" & Me.To.Value & " AND Head=" & Me.Head.Value & " AND DateVal<#" & Format(CurrentTransDate, "mm/dd/yyyy hh:mm:ss") & "#;")
'Basic Pay
Dim BasicPay As Currency
With Transet
    If .EOF = True Then
        BasicPay = 100000
        Me.DateVal.Value = Format(#1/1/2020#, "mm/dd/yyyy hh:mm:ss")
    Else
        .MoveLast
        BasicPay = Transet.Fields("Amount").Value
    End If
End With
Me.Amount.Value = BasicPay

'Hikes
Dim HikeStatus As Boolean
HikeStatus = IIf(DCount("ID", "Hikes", "Effective_Date<#" & CurrentTransDate & "#") > 0, False, True)
If Not HikeStatus = True Then
    Dim Hikeset As Recordset
    Set Hikeset = CurrentDb.OpenRecordset("SELECT Percentage, Status, Effective_Date FROM Hikes where Staff=" & Me.To.Value & " AND Record_Date<=#" & Format(CurrentTransDate, "mm/dd/yyyy hh:mm:ss") & "# AND Status = FALSE" & ";")
    With Hikeset
        If Not .EOF = True Then
            .MoveLast
            BasicPay = BasicPay + (BasicPay * .Fields("Percentage").Value)
            
            'Arrears
            Dim ArrearStatus As Boolean
            ArrearStatus = IIf(DCount("ID", "Transactions", "To=" & Me.To.Value & " AND DateVal<#" & CurrentTransDate & "# AND Head=30") > 0, True, False)
            If ArrearStatus = False Then
                CurrentDb.Execute ("INSER-T INTO Transactions (To,Head,Amount) VALUES (" & Me.To.Value & "," & 30 & "," & (BasicPay + (BasicPay * Hikeset.Fields("Percentage").Value)) * (DateDiff("m", Hikeset.Fields("Effective_Date"), CurrentTransDate)) & ");")
                Me.Requery
                With Hikeset
                    .Edit
                    .Fields("Status").Value = True
                    .Update
                End With
            End If
        End If
    End With
End If
Me.Amount.Value = BasicPay
DoCmd.Save
Me.Requery
End Sub

 

[MarkK]

I just successfully ran this code in a form...

Private Sub Form_AfterInsert()
    Me.Requery
End Sub

... so I cannot confirm your claim that Requery fails inside of AfterUpdate. Maybe remove all your code, then add it back one section at a time, and see if the code you are running is affecting the outcome.

 

[The_Doc_Man]

@prabha_friend - can you be more specific as to why you say your .Requery fails? What did you expect to see but didn't see? What message or visible result of data-record result did you get to make you think it failed? Showing us code without knowing its purpose is not really conducive to problem solving.

 

[Pat Hartman]

1. Why are you updating the current record AFTER you insert it? If you want to capture additional data, do it BEFORE you add the incomplete record. It is really poor practice to save incomplete records.
2. Why are you including time as part of date?
3. Are you aware that the Format() function converts a date to a string? The only time you should need to format a date for internal use in a query built with VBA is when your Windows default date is not the US standard of mm/dd/yyyy and that is because SQL Server assumes all ambiguous dates to be mm/dd/yyyy so 3/2/2023 is March second, rather than February third.
4. Dim Transet As Recordset --- is ambiguous. ALWAYS disambiguate when data types can be confused. Use DAO.Recordset to avoid confusion.
5. When you are updating multiple records as part of a "transaction", your code should ALWAYS enclose the entire update procedure within a Transaction. That will ensure that ALL updates are applied or the entire set is rolled back so that none are applied.

 

[prabha_friend]

Will update soon... Thanks for your patience..