We have an app where users can import transactions into from excel.
When users import transactions, they get a window with the results:
"X records found
X records were imported"
The X records found is working properly. The X records were imported is always 0. Although transactions are being successfully imported.
This is the code:
I appreciate your help. I am a beginner in Access. Thank you.
When users import transactions, they get a window with the results:
"X records found
X records were imported"
The X records found is working properly. The X records were imported is always 0. Although transactions are being successfully imported.
This is the code:
Code:
Option Compare Database
Private Sub btnImport_Click()
On Error Resume Next
Dim strFilter As String
Dim strInputFileName As String
Dim bGoodToGo As Boolean
Dim ao As AccessObject
Dim iStartRecs As Integer
Dim iEndRecs As Integer
Dim iLoop As Integer
Dim iRecID As Integer
Dim MultiRS As New ADODB.Recordset
iStartRecs = DCount("*", "dbo_tCCTransactions")
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryClearLocalTransactions", acViewNormal
DoCmd.SetWarnings True
bGoodToGo = True
strFilter = ahtAddFilterItem(strFilter, "Excel File (*.xlsx)", "*.xlsx")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
Me.txtFileName = strInputFileName
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tCCTransactions", strInputFileName, True, "A1:AT7500"
DoCmd.OpenQuery "qryExpandLast4", acViewNormal
DoCmd.SetWarnings True
'clear all import errors
For Each ao In CurrentData.AllTables
If InStr(ao.Name, "ImportErrors") > 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DROP TABLE " & ao.Name
DoCmd.SetWarnings True
End If
Next ao
'alphabetize all multipart transactions
If DCount("*", "qryMultiPartTransactions") > 0 Then
MultiRS.Open "SELECT * FROM qryMultiPartTransactions ", CurrentProject.Connection, adOpenStatic, adLockReadOnly
MultiRS.MoveFirst
While Not MultiRS.EOF
For iLoop = 1 To CInt(MultiRS.Fields(1)) Step 1
If IsNull(DLookup("MAXOFAUTOID", "qryMultiPartTransactions", "[Reference Number] = '" & MultiRS.Fields(0) & "'")) Then
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tCCTransactions Set [Reference Number] = '" & MultiRS.Fields(0) & Chr(64 + iLoop) & "' WHERE [Reference Number] = '" & MultiRS.Fields(0) & "'", False
DoCmd.SetWarnings True
Else
iRecID = DLookup("MAXOFAUTOID", "qryMultiPartTransactions", "[Reference Number] = '" & MultiRS.Fields(0) & "'")
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tCCTransactions Set [Reference Number] = '" & MultiRS.Fields(0) & Chr(64 + iLoop) & "' WHERE [AUTOID] = " & iRecID, False
DoCmd.SetWarnings True
End If
Next iLoop
MultiRS.MoveNext
Wend
End If
'merge account numbers
'attempt to append import to MSSQL table
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryClearOldTransactions", acViewNormal
DoCmd.OpenQuery "qryAppendCCTransactions", acViewNormal
DoCmd.SetWarnings True
iEndRecs = DCount("*", "dbo_tCCTransactions")
MsgBox DCount("*", "tCCTransactions") & " records found " & vbCrLf & iEndRecs - iStartRecs & " records were imported"
End Sub
Private Sub Label2_DblClick(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUnImport", acViewNormal
DoCmd.SetWarnings True
End Sub
I appreciate your help. I am a beginner in Access. Thank you.