I have an Access 2016 database which has some VBA to import OXF files from my bank.
Once imported the data will go into a "Account Transactions" table. During the import the VBA checks to see if the payee name already exists in the "Merchant Reference" table which will be used to derive a shortened name for the merchant - there may be multiple entries in the OXF file for the same chain of stores but with different store codes, eg WAITROSE STORE 1 and WAITROSE STORE 2 etc.
i have used Dlookup to check if the merchant exists in the table "Merchant Reference" and if it doesn't then I want to open the form associated with this table and add it. I'm trying to do this with the command below which opens the form as a popup and is ready for new record entry.
DoCmd.OpenForm "MerchantReference", , , acFormAdd, , acDialog, "MerchantFullName = '" & strName & "'"
The bit I'm struggling with is getting the long name for the new merchant to load into the appropriate text box on the form. The long name is in the variable strName. The form also contains a combo-box that will allow a short version of the merchant name to be selected from the existing list or a new short name to be added.
Help getting the DoCmd to work or with an alternative route much appreciated.
I'm an Access VBA novice BTW. Full code below.
Sub importOFXFile()
Dim fDialog As Office.FileDialog
Dim objFSO As Object
Dim objTextStream As Object
Dim strTextLine As String
Dim strInputFileName As String
Dim intTransElmntCnt As Integer
Dim intTransCnt As Integer
Dim strTransType As String
Dim strDatePosted As String
Dim strTransAmnt As String
Dim strFitID As String
Dim strName As String
Dim strMemo As String
Dim varShortName As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
fDialog.AllowMultiSelect = False
fDialog.Title = "Please select one file"
fDialog.Filters.Clear
fDialog.Filters.Add "Account OFX File", "*.ofx"
FileChosen = fDialog.Show
If FileChosen <> -1 Then
' Cancel button was pressed
MsgBox "You chose cancel"
Exit Sub
Else
' A file was selected
strInputFileName = fDialog.SelectedItems(1)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextStream = objFSO.OpenTextFile(strInputFileName)
intTransCnt = 0
Do While Not (objTextStream.AtEndOfStream)
strTextLine = objTextStream.ReadLine
intTransElmntCnt = 0
' Find transaction groups <STMTRN>
If Left(Trim(strTextLine), 9) = "<STMTTRN>" Then
intTransCnt = intTransCnt + 1
Do While Not Left(Trim(strTextLine), 10) = "</STMTTRN>"
strTextLine = objTextStream.ReadLine
' Read in all elements within transaction group
If Left(Trim(strTextLine), 9) = "<TRNTYPE>" Then
strTransType = (Mid(strTextLine, 10, InStr(strTextLine, "</TRNTYPE>") - 10))
ElseIf Left(Trim(strTextLine), 10) = "<DTPOSTED>" Then
strDatePosted = (Mid(strTextLine, 11, InStr(strTextLine, "</DTPOSTED>") - 11 - 6))
strDatePosted = Format(Right(strDatePosted, 2) & "/" & Mid(strDatePosted, 5, 2) & "/" & Left(strDatePosted, 4), "dd/mm/yyyy")
ElseIf Left(Trim(strTextLine), 8) = "<TRNAMT>" Then
strTransAmnt = (Mid(strTextLine, 9, InStr(strTextLine, "</TRNAMT>") - 9))
ElseIf Left(Trim(strTextLine), 7) = "<FITID>" Then
strFitID = (Mid(strTextLine, 8, InStr(strTextLine, "</FITID>") - 8 - 4))
ElseIf Left(Trim(strTextLine), 6) = "<NAME>" Then
strName = (Replace(Mid(strTextLine, 7, InStr(strTextLine, "</NAME>") - 7), "&", "&"))
strName = Replace([strName], "'", "")
varShortName = DLookup("[MerchantShortName]", "MerchantReference", "[MerchantFullName] = '" & strName & "'")
If IsNull(varShortName) Then
varShortName = "UNALLOCATED"
DoCmd.OpenForm "MerchantReference", , , acFormAdd, , acDialog, "MerchantFullName = '" & strName & "'"
End If
ElseIf Left(Trim(strTextLine), 6) = "<MEMO>" Then
strMemo = (Mid(strTextLine, 7, InStr(strTextLine, "</MEMO>") - 7))
End If
intTransElmntCnt = intTransElmntCnt + 1
Loop
' Transaction details ready so add them to transaction table
MsgBox ("Transaction " & intTransCnt & vbNewLine & "Short Name " & varShortName)
End If
Loop
objTextStream.Close
Set objFSO = Nothing
Set objTextStream = Nothing
MsgBox ("Total transactions " & intTransCnt)
End If
End Sub