Solved

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

Posted on 2015-02-13
8
298 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
  • 5
  • 3
8 Comments
 
LVL 49

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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 49

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 49

Expert Comment

by:Gustav Brock
ID: 40611751
You are welcome!

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now