UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Afterupdate Event In Access, Any Versions    
 
   
LilAnnCC1
post Jul 8 2019, 02:34 PM
Post#1



Posts: 820
Joined: 31-May 04
From: Wisconsin, USA


Hello. I'm using SQL Server 2017 with Access 2016 Front End.

I created the following procedure that first checks to see if there is any reason why we should not charge sales tax (Customer Exemption, State does not required us to collect tax, and Part is not taxable), otherwise the procedure updates all the tax columns in the Invoice table.


CODE
ALTER Procedure [dbo].[spFOBID]
    (@intInvoice int)    
    AS

Set Nocount on;
--Check for exemption
    IF(Select TaxStatus From vwInvoiceTaxStatus WHERE vwInvoiceTaxStatus.InvoiceID=@intInvoice)=0 --Taxable
        BEGIN
        
                IF (SELECT FOBID FROM tInvoice i where i.ID=@intInvoice)= 1 --Collect
                    BEGIN
        
                    Update tInvoiceDetail
                    set TaxStateID=ab.StateID,
                        StateRate=ab.stateRate,
                        taxCountyID=ab.TaxCountyID,
                        TransitRate=ab.Transit,
                        CountyRate=ab.countyrate,
                        TaxCityID=ab.TaxCityID,
                        CityRate=ab.CityRate
                        ,TaxOtherID=ab.TaxOtherID
                        ,OtherRate=ab.OtherRate

                    FROM tInvoiceDetail d
                    INNER JOIN tInvoice i on d.InvoiceID=i.ID
                    INNER JOIN vwAddressTax aB on i.Ship=ab.AddressID    
                    where d.InvoiceID=@intInvoice

                    End
                ELSE --POLK County Tax Prepaid
                    Begin
                        Update tInvoiceDetail
                    set TaxStateID=51,
                        StateRate=5.0,
                        taxCountyID=135,
                        TransitRate=0,
                        CountyRate=.5,
                        TaxCityID=1,
                        CityRate=0
                        ,TaxOtherID=1
                        ,OtherRate=0

                    FROM tInvoiceDetail d
                    INNER JOIN tInvoice i on d.InvoiceID=i.ID    
                    where d.InvoiceID=@intInvoice

                    END
            END
        
        ELSE --Exempt
            BEGIN
                IF (SELECT FOBID FROM tInvoice i where i.ID=@intInvoice)= 1 --Collect
                    BEGIN
        
                    Update tInvoiceDetail
                    set TaxStateID=ab.StateID,
                        StateRate=0,
                        taxCountyID=ab.TaxCountyID,
                        TransitRate=0,
                        CountyRate=0,
                        TaxCityID=ab.TaxCityID,
                        CityRate=0
                        ,TaxOtherID=ab.TaxOtherID
                        ,OtherRate=0

                    FROM tInvoiceDetail d
                    INNER JOIN tInvoice i on d.InvoiceID=i.ID
                    INNER JOIN vwAddressTax aB on i.Ship=ab.AddressID    
                    where d.InvoiceID=@intInvoice

                    End
                ELSE --POLK County Tax Prepaid
                    Begin
                        Update tInvoiceDetail
                    set TaxStateID=51,
                        StateRate=0,
                        taxCountyID=135,
                        TransitRate=0,
                        CountyRate=0,
                        TaxCityID=1,
                        CityRate=0,
                        TaxOtherID=1,
                        OtherRate=0

                    FROM tInvoiceDetail d
                    INNER JOIN tInvoice i on d.InvoiceID=i.ID    
                    where d.InvoiceID=@intInvoice
                    END
        END




This is how I call the procedure in Access:

CODE
Private Sub FOBID_AfterUpdate()
On Error GoTo ErrorHandling_Error
    
'Save record
    If Me.Dirty Then Me.Dirty = False
    
'Determine Tax
    Dim intID As Integer
    
    intID = Me.ID
    
    
    With CurrentDb.QueryDefs("qPass")
        .SQL = "EXEC spFOBID " & intID
        .ReturnsRecords = False
        .Execute
    End With

    Me.sf_InvoiceDetail.Requery
    Me.sf_InvoiceTotal_Balance.Requery
    
ErrorHandling_Exit:
Exit Sub

ErrorHandling_Error:
'      If Err.Number = Then
'      Else
             MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & Error$, vbExclamation, "FOBID_AfterUpdate"
'      End If
Resume ErrorHandling_Exit
End Sub


I have to literally chose the FOBID twice on the access form to get this to update the form accordingly.

What am I doing wrong? Is there a better way to handle this? Please bear in mind that I am really new to SQL Server and just as dangerous in Access!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
Doug Steele
post Jul 8 2019, 08:31 PM
Post#2


UtterAccess VIP
Posts: 22,184
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Not quite sure what you mean by
QUOTE
I have to literally chose the FOBID twice on the access form to get this to update the form accordingly.

Choosing a control doesn't fire its AfterUpdate event: you need to change the value in the control, then more to another control (or at least hit Enter) to have the event occur. h

What happens when you only choose it once? Does tInvoiceDetail get changed, but what's shown on sf_InvoiceDetail and sf_InvoiceTotal_Balance not get updated? Perhaps you need to insert some sort of delay in your procedure to ensure that sufficient time has occurred for the SQL Server table to get updated so that the results are shown on a requery.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
LilAnnCC1
post Jul 8 2019, 10:31 PM
Post#3



Posts: 820
Joined: 31-May 04
From: Wisconsin, USA


Hi Doug!

So the FOBID is a combo box on the form. It has 2 options: 1=Collect and 2=Prepaid. The users would see FOB in the label.

Before I added the exemption check (the first IF in the procedure), the after-update fired immediately and reflected in the form--so I knew it worked prior to the exemption check.

When I run the whole procedure in the SQL Server, it appears to work immediately, but when I switch back to Access, I can only get it to work if I choose the FOB a second time.

The main form is the Invoice where the FOBID control is located. The procedure affects the InvoiceDetail table (a subform on the Invoice form). In order for the Invoice to calculate the sales tax, the user needs to choose the FOB. Once that fires, it updates the tax fields in the invoice detail table and it is reflected on the form. The Invoice controls on the form are on a Tab Control and the Invoice Detail subform is on another page on the tab control. So you can't actually see the tax fields updated unless you move to the Detail page of the tab control. That being said, there is another third form that just reflects the Invoice Totals (Detail Total, Deposit, Credit, Retainage, Sales Tax and finally Invoice Total). This is visible at all times and is based on a view. When the procedure runs, the totals are immediately updated, so I know when the AfterUpdate event works.

I'm fairly certain that the exemption check is what is causing my procedure not to work the first time--I will actually choose the FOB option then I will click save on the form. Then I will move to the SQL Management Studio and run a query to select those invoice details to see if the update fired--and it shows me that it did not work. When I go back to Access and choose the FOB for the second time (same choice), following the sames steps, the SQL server shows the updated records.

I'm open to any suggestions that you might have.

Thank you!


--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
Doug Steele
post Jul 9 2019, 07:19 PM
Post#4


UtterAccess VIP
Posts: 22,184
Joined: 8-January 07
From: St. Catharines, ON (Canada)


One approach is to add a DoEvents statement as a sync point:

CODE
Private Sub FOBID_AfterUpdate()
On Error GoTo ErrorHandling_Error
    
'Save record
    If Me.Dirty Then Me.Dirty = False
    
'Determine Tax
    Dim intID As Integer
    
    intID = Me.ID
    
    
    With CurrentDb.QueryDefs("qPass")
        .SQL = "EXEC spFOBID " & intID
        .ReturnsRecords = False
        .Execute
    End With
  
    DoEvents
  
    Me.sf_InvoiceDetail.Requery
    Me.sf_InvoiceTotal_Balance.Requery
    
ErrorHandling_Exit:
Exit Sub

ErrorHandling_Error:
'      If Err.Number = Then
'      Else
             MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & Error$, vbExclamation, "FOBID_AfterUpdate"
'      End If
Resume ErrorHandling_Exit
  
End Sub

OTOH, there's also the suggestions put forward in FMS's Avoid Using DoEvents to Wait in Microsoft Access, VBA, and VB6.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
LilAnnCC1
post Jul 9 2019, 07:29 PM
Post#5



Posts: 820
Joined: 31-May 04
From: Wisconsin, USA


Thank you for sticking with me.

I disabled the first If statement (checking for the exemptions). It seems to work better, sometimes immediately, and other times I have to choose the FOB twice. I will try to add your suggestion for just a few seconds and see if that helps. It seems strange that it works immediately on some records, but similar records it doesn't.

I will have to come up with another way to check for the exemptions before calculating the total invoice.

Thank you for your help!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th August 2019 - 10:00 PM