Solved

Access VBA Must click cmd Button twice for form field to update.

Posted on 2015-02-13
8
363 Views
Last Modified: 2016-02-10
I have a cmd Button on a form that runs the following code.  For some reason the field FuelCharged on the form will only update if I click the cmd button twice.

Private Sub cmdRating_Click()

Dim db As DAO.Database
Dim qdfCharges As DAO.QueryDef
Dim strSQLCharges As String
Dim qdfFuelCharged As DAO.QueryDef
Dim strSQLFuelCharged As String
Dim strDb As String

strDb = "C:\Users\Austin\Documents\Access Development\RateTool.accdb"
Set db = OpenDatabase(strDb)

If TableExists.TableExists("tblCharges") = True Then        'Function TableExists

DoCmd.RunSQL "DROP TABLE tblCharges"

ElseIf TableExists.TableExists("tblCharges") = False Then

'Make tblCharges

strSQLCharges = "SELECT Sum(tblAdditionalCharges.Charge) AS SumOfCharge INTO tblCharges " & _
    "FROM (tblRate INNER JOIN tblRateCharges " & _
    "ON tblRate.RateID = tblRateCharges.RateID) INNER JOIN tblAdditionalCharges " & _
    "ON tblRateCharges.ChargeID = tblAdditionalCharges.ChargeID;"

Set qdfCharges = db.CreateQueryDef("", strSQLCharges)
    qdfCharges.Execute
   
'Update FuelCharged

strSQLFuelCharged = "UPDATE tblRate, tblCharges SET tblRate.FuelCharged = [FuelPercentage]*([RateBase]+Nz([SumOfCharge]));"

Set qdfFuelCharged = db.CreateQueryDef("", strSQLFuelCharged)
    qdfFuelCharged.Execute

Me.Requery
End If

End Sub
0
Comment
Question by:ExpressMan1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40608544
You probably need a refresh and also not the ElseIf but If only:

If TableExists.TableExists("tblCharges") = True Then        'Function TableExists
   DoCmd.RunSQL "DROP TABLE tblCharges"
   db.TableDfs.Refresh
End If
If TableExists.TableExists("tblCharges") = False Then

/gustav
0
 

Author Comment

by:ExpressMan1
ID: 40608584
Getting error "Method or Data Member not found" On db.TableDfs.refresh
0
 

Author Comment

by:ExpressMan1
ID: 40608605
Not sure if this is related but here are the references I have.

References:

Visual Basic For Applications
Microsoft Access 15.00 Object Library
OLE Automation
Microsoft Office 15.0 Access Database engine Objects
Microsoft ActiveX Data Objects Library
Microsoft Office 15.0 Object Library

When I try to add Microsoft DAO 3.6 Object Library I get "Error Loading DLL"
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:ExpressMan1
ID: 40608684
Ignore the last comment as I am using .accdb file format which does not use DAO 3.6 Object Library.
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40609034
It is, of course:  

    db.TableDefs.Refresh

/gustav
0
 

Author Comment

by:ExpressMan1
ID: 40611305
db.TableDefs. Refresh in the right place along with some other changes seems to have worked.

Private Sub cmdRating_Click()
 On Error GoTo ErrorHandler
    Dim db As Database
   Dim strSQL As String
   Dim strSQLFuelCharged As String
   Dim strTable As String
   strTable = "tblCharges"
   Set db = CurrentDb
   If Me.Dirty Then
    Me.Dirty = False
End If

DoCmd.Requery
   'Delete the table if it exists
   DoCmd.DeleteObject acTable, strTable
strSQL = "SELECT Sum(tblAdditionalCharges.Charge) AS SumOfCharge INTO tblCharges " & _
        "FROM tblRateCharges INNER JOIN tblAdditionalCharges ON tblRateCharges.ChargeID = tblAdditionalCharges.ChargeID;"
db.TableDefs.Refresh

CurrentDb.Execute strSQL
'Insert more code here to do something with temp table
strSQLFuelCharged = "UPDATE tblCharges, tblRate " & _
                    "SET tblRate.FuelCharged = [tblRate].[FuelPercentage]*[RateBase]+(Nz([SumOfCharge],0));"
                   
CurrentDb.Execute strSQLFuelCharged
If Me.Dirty Then
    Me.Dirty = False
End If

DoCmd.Requery

Exit Sub
ErrorHandler:
If Err.Number = 7874 Then
Resume Next 'Tried to delete a non-existing table, resume
End If
End Sub
0
 

Author Closing Comment

by:ExpressMan1
ID: 40611306
Thanks again Gustav.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40611751
You are welcome!

/gustav
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question