?
Solved

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

Posted on 2015-02-13
8
Medium Priority
?
401 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 51

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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

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 51

Accepted Solution

by:
Gustav Brock earned 2000 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 51

Expert Comment

by:Gustav Brock
ID: 40611751
You are welcome!

/gustav
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

777 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