[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

Access 2007 - vb code

My OS is windows 7, and the code below was submitted by one of the Gurus.  I would like to modify it so that I will be asked if I want to export to a ASCII file or Just as the code is.  If the response yes to the first Question, then could u please write me the code to export to a file.
Thank u
Private Sub btnDistList_Click()
Dim rst As DAO.Recordset
Dim strDistList As String
Set rst = CurrentDb.OpenRecordset("Seniors Club")

Do While Not rst.EOF
    strDistList = strDistList & rst!Email & ";"
    rst.MoveNext
Loop

    strDistList = Left(strDistList, Len(strDistList) - 1)

    Me.txtDistList = strDistList

rst.Close
Set rst = Nothing
End Sub

Open in new window

0
jegajothy
Asked:
jegajothy
2 Solutions
 
peter57rCommented:
Export what?
There doesn't appear to be anything to export.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use a MsgBox if you wish to "export" your strDistList variable:

If Msgbox("Print Email distribution list to file?", vbYesNo, "Print?") = vbYes Then
  Dim lFile As Long
  '/get the next available File handle
  lFile = FreeFileHandle
  '/delete the file if it exists
  If Dir(CurrentProject.Path & "\EmailList.txt") <> "" Then Kill CurrentProject.Path & "\EmailList.txt"  
  '/Now open the file
  Open CurrentProject.Path & "\EmailList.txt" FOR Output As #lFile
  '/write to that file
  Print #lFile, strDistList
  '/now close the file
  Close #lFile
End If


 
 
0
 
Helen FeddemaCommented:
Why not just export the emails directly from the table that the recordset is based on, rather than constructing a string of them?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Helen FeddemaCommented:
Make a select query with just the email field, and then export it using TransferText:
DoCmd.TransferText transfertype:=acExportDelim, _
      tablename:="qryEmail", _
      FileName:="D:\Documents\Emails.csv", _
      hasfieldnames:=True

Open in new window

0
 
jegajothyAuthor Commented:
In response to LSM Consulting, when the code was executed I got a Run time error 52 and it says "Bad file name or number".
Maybe if we  also a define a path, like : C:\Documents, it will prevent the code from going crazy.
Hope u can fix the code.  Thank u for your response.
0
 
jegajothyAuthor Commented:
In response to Helen Fedema, sorry being a novice.  But can u please document your code.
What is tablename set to : Is it a query?
0
 
Helen FeddemaCommented:
transfertype, tablename, FileName, and HasFieldNames are the arguments of the TransferText method.  I like to use named arguments, for greater clarity.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now