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
Set db = OpenDatabase(strDb)
If TableExists.TableExists("t
DoCmd.RunSQL "DROP TABLE tblCharges"
ElseIf TableExists.TableExists("t
'Make tblCharges
strSQLCharges = "SELECT Sum(tblAdditionalCharges.C
"FROM (tblRate INNER JOIN tblRateCharges " & _
"ON tblRate.RateID = tblRateCharges.RateID) INNER JOIN tblAdditionalCharges " & _
"ON tblRateCharges.ChargeID = tblAdditionalCharges.Charg
Set qdfCharges = db.CreateQueryDef("", strSQLCharges)
qdfCharges.Execute
'Update FuelCharged
strSQLFuelCharged = "UPDATE tblRate, tblCharges SET tblRate.FuelCharged = [FuelPercentage]*([RateBas
Set qdfFuelCharged = db.CreateQueryDef("", strSQLFuelCharged)
qdfFuelCharged.Execute
Me.Requery
End If
End Sub
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
Set db = OpenDatabase(strDb)
If TableExists.TableExists("t
DoCmd.RunSQL "DROP TABLE tblCharges"
ElseIf TableExists.TableExists("t
'Make tblCharges
strSQLCharges = "SELECT Sum(tblAdditionalCharges.C
"FROM (tblRate INNER JOIN tblRateCharges " & _
"ON tblRate.RateID = tblRateCharges.RateID) INNER JOIN tblAdditionalCharges " & _
"ON tblRateCharges.ChargeID = tblAdditionalCharges.Charg
Set qdfCharges = db.CreateQueryDef("", strSQLCharges)
qdfCharges.Execute
'Update FuelCharged
strSQLFuelCharged = "UPDATE tblRate, tblCharges SET tblRate.FuelCharged = [FuelPercentage]*([RateBas
Set qdfFuelCharged = db.CreateQueryDef("", strSQLFuelCharged)
qdfFuelCharged.Execute
Me.Requery
End If
End Sub
ASKER
Getting error "Method or Data Member not found" On db.TableDfs.refresh
ASKER
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"
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"
ASKER
Ignore the last comment as I am using .accdb file format which does not use DAO 3.6 Object Library.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.C
"FROM tblRateCharges INNER JOIN tblAdditionalCharges ON tblRateCharges.ChargeID = tblAdditionalCharges.Charg
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]
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
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.C
"FROM tblRateCharges INNER JOIN tblAdditionalCharges ON tblRateCharges.ChargeID = tblAdditionalCharges.Charg
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]
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
ASKER
Thanks again Gustav.
You are welcome!
/gustav
/gustav
If TableExists.TableExists("t
DoCmd.RunSQL "DROP TABLE tblCharges"
db.TableDfs.Refresh
End If
If TableExists.TableExists("t
/gustav