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

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
ExpressMan1Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
It is, of course:  

    db.TableDefs.Refresh

/gustav
0
 
Gustav BrockCIOCommented:
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
 
ExpressMan1Author Commented:
Getting error "Method or Data Member not found" On db.TableDfs.refresh
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ExpressMan1Author Commented:
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
 
ExpressMan1Author Commented:
Ignore the last comment as I am using .accdb file format which does not use DAO 3.6 Object Library.
0
 
ExpressMan1Author Commented:
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
 
ExpressMan1Author Commented:
Thanks again Gustav.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.