Export query to text file with specific formatting?

[funwithaccess]

Hi,

I have successfully put together some VBA code that will accept a user's input into a textbox on a form. A cmdFindprinter button is clicked and the query is ran. The user is asked via an inputbox for their login ID so that the query is exported to a text file on the desktop. The query then pops up in a new tab and also automatically exports the queried record to a text file on the desktop. I have upward of 30 users using this form at any given time and I need the following automated.

What I would like to do is to have the text file go into a specific format. For example:

IP address:
Serial number:
Location:

These are just a few to give you an idea. The name of one of the queries is "Xerox IP Query", the field names would be "IP Address", "SerialNumber", and "Site Name".

This is what comes up in the text file right now:

"CXF345946","157.229.243.58","123 Happy Ave"

I'd like to remove the quotes and have the info fall into place as shown in the example above.

This is the code so far:

Private Sub cmdFindprinter_Click()
On Error GoTo cmdFindprinter_Click_Err

Dim strPath As String

userNT = InputBox("Please enter your NT ID", "ServiceBase Xerox Printer Query", "Enter your NT ID")
strPath = "C:\Users\" & userNT & "\Desktop\PrinterQuery.txt"

If Heading = 0 Then Exit Sub

Dim intCount As Integer
    intCount = 0

    If DCount("AssetNumber", "Xerox Assets Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly
        DoCmd.TransferText acExportDelim, , "Xerox Assets Query", strPath
    End If

    If DCount("[IP Address]", "Xerox IP Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox IP Query", acViewNormal, acReadOnly
        DoCmd.TransferText acExportDelim, , "Xerox IP Query", strPath
    End If

    If DCount("SerialNumber", "Xerox Serial Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Serial Query", acViewNormal, acReadOnly
        DoCmd.TransferText acExportDelim, , "Xerox Serial Query", strPath
    End If

    If intCount = 0 Then MsgBox "No results found in ServiceBase" & vbCrLf & "Please verify that the printer inforomation is valid", vbExclamation + vbOKOnly, "ServiceBase Search Results"

    
    MsgBox "PrinterQuery has been updated on your desktop", vbInformation + vbOKOnly, "ServiceBase Export Complete"

cmdFindprinter_Click_Exit:
Exit Sub

cmdFindprinter_Click_Err:
MsgBox "Export failed. Please enter a valid NT ID to export the printer information.", vbExclamation, "Please Enter a Valid NT ID"
Resume cmdFindprinter_Click_Exit

End Sub

Thanks for any help that you can provide!

Nate

 

[Pat Hartman]

The built in functions such as TransferText work ONLY with tables/queries. They cannot produce output the way you want it. You will need to write your own output procedure using DAO/ADO to read a query and "Print #" to write it so you can write multiple output lines per single query line. Look up Open, Output, Print using Google for code samples

 

[funwithaccess]

Hi Pat,

I have a VBScript that does exactly what I need it to do that is completely outside of the database. Is there a way that I could use the VBA to open and run that script? In other words, what VBA code would I use to open it?

 

[funwithaccess]

Update:
I have modified my VBA to open the corresponding VBScript. Is there a way that the VBScript could pull the IP address from the Access form that the user is searching?

 

[Pat Hartman]

No need to run the script externally. You can usually just copy the VBScript and paste it into a VBA procedure and run it there. The syntax is the same except I think VBScript doesn't define variables so you will need to add Dim statements for your variables. Once the code is in your application, it can refer to forms, etc. the way anything else does.