Solved

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

Posted on 2015-02-13
8
374 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

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