Help creating various flatfiles

[ssmith001]

I have the following code that I have been working on and the requirements have changed so now I need to create a new flatfile each time the LOC field in the PRESOF table changes. I would like to name each file spoolSOF_<LOC>.txt

I'm not sure I know where to begin. Can someone give me some ideas?

Public Function CreateTextFile()

'This function creates a fixed-width text file using the <plannercode>_PRESOF table
    
 Dim strId As String * 3            'specifies width of 3 characters
 Dim strDlrSplrCode As String * 5   'specifies width of 5 characters
 Dim strLoc As String * 10          'specifies width of 10 characters
 Dim strItem As String * 20         'specifies width of 20 characters
 Dim strQty As String * 5           'specifies width of 5 characters
 Dim strMessage As String * 30      'specifies width of 30 characters
 Dim mydb As DAO.Database, myset As DAO.Recordset
 Dim intFile As Integer
 Dim iResponse As Integer
 
 Dim planner As String

 planner = Forms!frm_Main!txtPlannerCode

 Set mydb = CurrentDb()
 Set myset = mydb.OpenRecordset(planner & "_PRESOF", dbOpenTable)

 intFile = FreeFile

 Open "C:\temp\spoolSOF.txt" For Output As intFile

 'This section puts the records from the PRESOF table in the text file.
  myset.MoveFirst
  Do Until myset.EOF
      
      LSet strId = myset![ID] 'Field name in brackets
      LSet strDlrSplrCode = Format(myset![DlrSplrCode])
      LSet strLoc = Format(myset![Loc])
      LSet strItem = Format(myset![Item])
      strQty = Format(myset![Qty], "00000")
      LSet strMessage = Format(myset![Message])
      
      'Concatenate all of the variables together
      Print #intFile, strId & strDlrSplrCode & strLoc & strItem & strQty & strMessage
      myset.MoveNext
  Loop

  Close intFile
  myset.Close
  mydb.Close

  iResponse = MsgBox("The file C:\temp\spoolSOF.txt was created successfully" & vbCrLf & vbCrLf & _
        "Do you wish to Open it in Notepad?", vbYesNo, _
        "SOF File Output Results")
        
  If iResponse = vbYes Then
     Shell "Notepad.exe C:\temp\spoolSOF.txt", vbMaximizedFocus
  End If

End Function