VBA loop through joined table and export csv

[northerner29]

Hi, I am fairly new to access VBA although been doing Excel for a long time so understand the coding structure etc.

I have a table of 130 invoicees and a list of activity which includes the invoicee in a field.

I would like access to loop through my activity database and generate a csv file for each batch of invoicee data where there is activity for them.

Does anyone know how to do this?

thanks for your help

 

[tranchemontaigne]

I'm not sure exactly what you intend to do in looping through the records, but typical syntax would look something like this: Please be advised that this is a quick rendition of code to address your question, but has not actually been pre-tested for you in MS Access. I suspect that you will also require a reference to the Microsoft Scripting Runtime library, Microsoft ActiveX Data Object library, and/or OLE automation library to get the file I/O to work.

fnGenerateCSV()
 
dim rst1 as adodb.recordset
dim strsql as string
dim intRecordCount as integer
 
strsql = <insert an SQL string that pulls the relevent elements from your invoices table>
 
set rst1 = new adodb.recordset
rst1.open strsql, currentproject.connection, adopenforwardonly, adlockoptimistic
 
'test for empty recordset
intRecordCount = 0
do until rst1.eof 
   intRecordCount = intRecordCount +1
   rst1.movenext
loop
 
if inbtRecordCount = 0 then exit function
 
'loop through records
do until rst1.eof 
   'test for conditions that merit a new invoice and when appropriate, 
   'construct the export file body, remember to use a chr(10) at the 
   'end of each record
 
   if <something> then
      strText = <concatenate elements to build the CSV file>
   end if
 
   rst1.movenext
loop
 
strOutputFileName = <define output file name - it could be "Invoices.csv" or anything you desire>
 
call fnPrintMethod(strText, strOutputFileName)
 
end function

you can also send a string of data to an external document using code similar to this

Public Function fnPrintMethod(strText as string, strOutputFileName as string)
 
Dim strFilePath As String
Dim strText As String
 
strFilePath = CurrentProject.Path & strOutputFileName 
 
'open/create document
Open strFilePath For Output As #2
 
Print #2, strText
 
'Close before reopening in another mode.
Close #2
MsgBox "done"
End Function

________
Top penny stocks