Results 1 to 6 of 6
  1. #1
    Crossdr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    2

    Open form for new record and add value to text box

    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), "&amp;", "&"))
    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

  2. #2
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    After you open the form in Add mode just add another line, something like
    Forms!formname!merchantfullname.Value = strname
    . I just tested it and it works just fine.,

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You've put values in the OpenArgs argument. That will pass a value to the form being opened, but not do anything with it. You'd need code in the current event to load the value into the appropriate textbox.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by warmslime View Post
    After you open the form in Add mode just add another line, something like
    Forms!formname!merchantfullname.Value = strname
    . I just tested it and it works just fine.,
    You can't do that with acDialog; the next line wouldn't execute until the form is closed or hidden. If acDialog is dropped your suggestion will work fine.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Crossdr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    2
    I have quickly tried the following which does indeed put the contents of variable strName in the forms text box.

    DoCmd.OpenForm "MerchantReference", , , acFormAdd, , , "MerchantFullName = '" & strName & "'"
    Forms!MerchantReference!merchantfullname.Value = strName

    Without the acDialog the VBA code does not stop allowing the short name to be selected from the combobox on the form.

    I think I need to understand the comment about needing code in the current event to load the value from the OpenArgs argument. So much to learn!

    Thanks for the pointers.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This is more complicated than you need:

    http://www.baldyweb.com/OpenArgs.htm

    You basically just need to set a control value to the OpenArgs value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 06-16-2015, 06:18 AM
  2. Replies: 4
    Last Post: 12-16-2014, 05:08 PM
  3. Replies: 2
    Last Post: 03-13-2013, 06:13 PM
  4. Replies: 3
    Last Post: 08-26-2012, 10:04 PM
  5. Replies: 1
    Last Post: 05-03-2012, 02:25 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums