Write Text File From 2 tables

[gregch]

I have a situation where I need to write a text file from Access. There are two related tables that need to be written to this file. Below is the format of the file that I need write to:

BEGIN:
Inspection #23423423DVA
Tag#: 47BHY1926
Tag#: 47VDK1262
Tag#: 47VCC4861
END:

As you can see, I have an Inspection table that has multiple Tag#s associated with each inspection. Has anyone run into this situation? Any pointers would be greatly appreciated.

 

[boblarson]

Probably but how is your data set up? You can use a recordset to iterate through and then use the Open FilePathAndNameHere For Output As #1 type of coding.

 

[mdlueck]

You will need to order your query by Inspection Number so that you are guaranteed grouped output based on Inspection Number.

Then as you step through the result set, you keep in a varaible the current Inspection Number. When you notice it change, then you know to output the necessary lines when ending one Inspection Number and beginning a new one, then go back to outputting the Tag Numbers for the current Inspection Number.

Perhaps even order first by Inspection Number, then by Tag Number for a consistent report.

 

[gregch]

Inspection table has the following fields:

Inspection ID - This field is set as the primary key

Inspection_Detail table has the following fields:

Inspection - This field is set as the foreign key
Tag#

Do you have any examples on how to write the data from these two tables to the above format. I need to have the Inspection data written once and all the tag # data written below.

Thanks for your help!

 

[boblarson]

Here you go. This is really only "air code" meaning not tested. I may have some stuff out of sequence and all, but hopefully you can troubleshoot it.

To call it you would use

SendInsp2Text "C:\MyFolder\MySubfolder\MyData.txt"

(the file path and name of the file must be used. This is the file path and name where you want the exported info to end up. For Output will replace the entire file. Changed to For Append will just add the data to the end of the text file.

Function SendInsp2Text(strOutputPathAndFile As String)
Dim i As Integer
Dim strHold As String
Dim rst As DAO.Recordset
Dim blnNewRecord As Boolean
Dim strInspectionNum As String
 
Set rst = CurrentDb.OpenRecordset("Select Inspection, [Tag#] From Inspection_Detail ORDER BY Inpection, [Tag#]", , dbOpenForwardOnly)
 
i = FreeFile
 
Open strOutputPathAndFile For Output As #i

strInspectionNum = rst!Inspection

Do Until rst.EOF
    strHold = "BEGIN:"

    Print i, strHold
    If blnNewRecord Then
        strHold = "Inspection #" & rst!Inspection
    End If
    
    If strInspectionNum <> rst!Inspection Then
        strHold = "END:"
        Print i, strHold
        strInspectionNum = rst!Inspection
        blnNewRecord = True
    Else
        blnNewRecord = False
    End If
    strHold = "Tag#:" & rst![Tag#]
    Print i, strHold

Loop
Close i

rst.Close
Set rst = Nothing

End Function

 

[gregch]

Thank you! I will take your suggestions and run with them!