Hi,
Hope someone can help, I am completely stuck right now. I have a vba script that pulls data from a query base on certain criteria. I am then using the DSum function to add the values in a particular field. I am using the same script to with the DCount function, and it works fine. See sample code below.
The funny thing is, the data is inserted into the table, but the insert error still appears, and the script stops at the first "CurrentDb......".
NB: Although not important, the task is being executed using a form.
Option Compare Database
Private Sub cmdClose_Click()
DoCmd.Close
End Sub
Private Sub cmdGenerate_Click()
Dim DateVal
Dim Monday
Dim period
Dim str
Dim Name
Dim XCD
Dim USD
DateVal = EndDate.Value
If Frame13 = 1 Then
period = " [Date]=#" & DateVal & "#"
ElseIf Frame13 = 2 Then
'check for friday
If Weekday(DateVal) = vbFriday Then
Monday = DateAdd("d", -4, DateVal)
period = " [Date] between #" & Monday & "# and #" & DateVal & "#"
Else
MsgBox "Please select a Fridays' Date", , "End of Week Error"
Exit Sub
End If
ElseIf Frame13 = 3 Then
period = " [Date] between #" & DateSerial(Year(DateVal), Month(DateVal), 1) & _
"# and #" & DateSerial(Year(DateVal), Month(DateVal) + 1, 0) & "#"
End If
DateStore.Value = period
'MsgBox period
LabelMsg.Caption = "Reporting for " & period
str = "delete * from ABC_On_NBA_Amt"
CurrentDb.Execute str
For i = 4 To 10
'Name = DLookup("AB_Name", "AB_Names_Nos", "[AB_no]=" & i)
If i < 10 Then
XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ0000" & i & _
"' and [Currency_Code]= 951 " & " and " & period)
USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ0000" & i & _
"' and [Currency_Code]= 840 " & " and " & period)
Name = DLookup("[AB_Name]", "[AB_Names_Nos]", "[AB_no]='XYZ0000" & i & "'")
Else
XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]= 951 " & " and " & period)
USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]= 840 " & " and " & period)
Name = DLookup("[AB_Name]", "[AB_Names_Nos]", "[AB_no]='XYZ000" & i & "'")
End If
The data is calculated and inserted into the table, the error still appears.
CurrentDb.Execute "Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD) values(#" & DateVal & "#," _
& XCD & ",' " & Name & " '," & USD & ")"
Below does not execute because of the insert syntax error.
Next
For i = 15 To 19
XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]=951 " & " and " & period)
USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]=840 " & " and " & period)
Name = DLookup("[AB_Name]", "[AB_Names_Nos]", "[AB_no]='XYZ000" & i & "'")
CurrentDb.Execute "Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD) values(#" & DateVal & "#," _
& XCD & ",'" & Name & "'," & USD & ")"
Next
If Frame13 = 1 Then
DoCmd.OpenReport "NBA_AB_Transactions_by_DEF_Numbers", acViewPreview
ElseIf Frame13 = 2 Then
DoCmd.OpenReport "ABC_AB_Transactions_By_DEF_Weekly", acViewPreview
ElseIf Frame13 = 3 Then
'DoCmd.OpenReport "ABC_AB_Transactions_By__Monthly", acViewPreview
End If
'Dim str As String
'str = "select * from ABC_CCC_AB_Trans where locationID in string(,,)"
End Sub
Private Sub Form_Unload(Cancel As Integer)
'Forms!Switchboard.Visible = True
End Sub
Hope someone can help, I am completely stuck right now. I have a vba script that pulls data from a query base on certain criteria. I am then using the DSum function to add the values in a particular field. I am using the same script to with the DCount function, and it works fine. See sample code below.
The funny thing is, the data is inserted into the table, but the insert error still appears, and the script stops at the first "CurrentDb......".
NB: Although not important, the task is being executed using a form.
Option Compare Database
Private Sub cmdClose_Click()
DoCmd.Close
End Sub
Private Sub cmdGenerate_Click()
Dim DateVal
Dim Monday
Dim period
Dim str
Dim Name
Dim XCD
Dim USD
DateVal = EndDate.Value
If Frame13 = 1 Then
period = " [Date]=#" & DateVal & "#"
ElseIf Frame13 = 2 Then
'check for friday
If Weekday(DateVal) = vbFriday Then
Monday = DateAdd("d", -4, DateVal)
period = " [Date] between #" & Monday & "# and #" & DateVal & "#"
Else
MsgBox "Please select a Fridays' Date", , "End of Week Error"
Exit Sub
End If
ElseIf Frame13 = 3 Then
period = " [Date] between #" & DateSerial(Year(DateVal), Month(DateVal), 1) & _
"# and #" & DateSerial(Year(DateVal), Month(DateVal) + 1, 0) & "#"
End If
DateStore.Value = period
'MsgBox period
LabelMsg.Caption = "Reporting for " & period
str = "delete * from ABC_On_NBA_Amt"
CurrentDb.Execute str
For i = 4 To 10
'Name = DLookup("AB_Name", "AB_Names_Nos", "[AB_no]=" & i)
If i < 10 Then
XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ0000" & i & _
"' and [Currency_Code]= 951 " & " and " & period)
USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ0000" & i & _
"' and [Currency_Code]= 840 " & " and " & period)
Name = DLookup("[AB_Name]", "[AB_Names_Nos]", "[AB_no]='XYZ0000" & i & "'")
Else
XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]= 951 " & " and " & period)
USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]= 840 " & " and " & period)
Name = DLookup("[AB_Name]", "[AB_Names_Nos]", "[AB_no]='XYZ000" & i & "'")
End If
The data is calculated and inserted into the table, the error still appears.
CurrentDb.Execute "Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD) values(#" & DateVal & "#," _
& XCD & ",' " & Name & " '," & USD & ")"
Below does not execute because of the insert syntax error.
Next
For i = 15 To 19
XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]=951 " & " and " & period)
USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]=840 " & " and " & period)
Name = DLookup("[AB_Name]", "[AB_Names_Nos]", "[AB_no]='XYZ000" & i & "'")
CurrentDb.Execute "Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD) values(#" & DateVal & "#," _
& XCD & ",'" & Name & "'," & USD & ")"
Next
If Frame13 = 1 Then
DoCmd.OpenReport "NBA_AB_Transactions_by_DEF_Numbers", acViewPreview
ElseIf Frame13 = 2 Then
DoCmd.OpenReport "ABC_AB_Transactions_By_DEF_Weekly", acViewPreview
ElseIf Frame13 = 3 Then
'DoCmd.OpenReport "ABC_AB_Transactions_By__Monthly", acViewPreview
End If
'Dim str As String
'str = "select * from ABC_CCC_AB_Trans where locationID in string(,,)"
End Sub
Private Sub Form_Unload(Cancel As Integer)
'Forms!Switchboard.Visible = True
End Sub