Code to auto-export query record/row to text file

[funwithaccess]

Hi,

I hope that this is the correct place to post this. My VBA knowledge is expanding, however, this is way over my head in terms of technical know-how.

I have 3 queries that provide the same printer information. Each one is queried by a different field: IP address, asset tag, and serial number. This may not mean anything in the long run, though I figured it is worth mentioning.

The users need to be able to quickly query a printer utilizing one of those criteria and then copy and paste it into our ticketing system. Is there a way to automatically export the record from the query to a text file? I have extensively searched online and have tried to come up with something but I have found that I don't know where to start. This is the code for the query:

Dim intCount As Integer
    intCount = 0
    
    If DCount("Location", "Phone numbers Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "", acViewNormal, acReadOnly
    End If

    If DCount("Heading", "Service Desk Manual Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Service Desk Manual Query", acViewNormal, acReadOnly
    End If
    
    If DCount("Alias", "SD Documentation Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "SD Documentation Query", acViewNormal, acReadOnly
    End If

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

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

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

    If intCount = 0 Then MsgBox "No results found in ServiceBase." & vbCrLf & "Please provide a specific word, phrase, or alias.", vbExclamation + vbOKOnly, "ServiceBase Search Results"

Thank you in advance!! (Mod, please feel free to move this post if it is in the incorrect location.)

Nate

 

[CJ_London]

Look at the TransferText functin - this will export to a text file

http://msdn.microsoft.com/en-us/library/office/aa220768(v=office.11).aspx

But if the user is to copy and paste, why not just use an inputbox?

http://office.microsoft.com/en-gb/access-help/inputbox-function-HA001228856.aspx

Populate the default parameter with what you want the user to copy

 

[funwithaccess]

Hi again CJ,

So far, I have created a button on the form to open the inputbox, however, I cannot get it to run the query and show the results within the input box. Also, it needs to populate the entire record, not just one field. Is this possible? My code:

Private Sub cmdFindprinter_Click()
    
    Dim StudentName As String

    [Xerox IP Query]![IP Address] = InputBox("Enter Printer Information:", _
                           "Printer Search")
                           
    MsgBox "Enter Printer Information: & IP Address"
    
cmdFindprinter_Click_Exit:
    Exit Sub
    
cmdFindprinter_Click_Err:
    MsgBox Error$
    Resume cmdFindprinter_Click_Exit

 

[CJ_London]

Sorry, I don't actually understand what you are trying to do, I thought you wanted to copy and paste a bit of information into your ticketing system.

MsgBox "Enter Printer Information: & IP Address"

What does this do?

I cannot get it to run the query

Where is the code that doesn't run?

 

[funwithaccess]

I apologize if I'm not providing enough info. The way I have it set up and working now is that you type in the printer's info into my form's textbox and it then runs the correct queries. The issue comes down to the fact that you can't easily just copy and paste a record without copying the field names too. What I'd like to happen is have the record spit out into a copying-capable format. The inputbox would be great if the queried record returned in either the same inputbox or a new text box. I'm still getting used to all of the different names.

 

[CJ_London]

How about you show me - provide some sample data of what each of the queries return and what you want to copy.

If you are getting the headers this implies you are copying more than one field from the query

 

[funwithaccess]

Correct, I am copying more than one field from the query. Also, I don't need all of the fields, only a few. I'll try to provide some sample data later.

 

[funwithaccess]

Hi CJ,

I apologize, I have not gotten the sample for you, however, I have found a bit of code that kind of does what I need:

Private Sub cmdFindprinter_Click()

On Error GoTo Err_Handler

Dim strPath As String

strPath = InputBox("Enter file path", , "C:\Users\public\Desktop\Test.txt")

If Len(strPath) = 0 Then Exit Sub

DoCmd.TransferText acExportDelim, , "Xerox IP Query", strPath

MsgBox "Done", vbInformation

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

The only issue is that it just exports the entire queried table. I need to to only export the one record that was queried from my form's "search" textbox. In other words, if you type the IP of 157.229.243.58 into the search field, click "Find", the resulting query runs and pops up in a new tab with the one record that has that specific IP address in it. THAT is what I need to export. Is that a bit more clear? I'm terrible at being specific haha. Thank you for all of your help!!!

Nate

 

[funwithaccess]

Here is the result of a search of the IP listed above:

 

[funwithaccess]

Okay, so I've made some serious strides so far. This is the updated code that now works with the textbox and cmdFindprinter button:

Private Sub cmdFindprinter_Click()

On Error GoTo Err_Handler

Dim strPath As String

strPath = InputBox("Enter file path", , "C:\Users\blankn\Desktop\Test.txt")

If Len(strPath) = 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
    End If

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

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

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

    DoCmd.TransferText acExportDelim, , "Xerox IP Query", strPath

    MsgBox "Done", vbInformation

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Now, I just need to eliminate the quotes that surround each field in the text file (I would really like it to automatically go into a specific format within the text file). Also, it will throw a MsgBox at the user if the search comes back empty, however, the export still occurs with an empty text file and a MsgBox still appears that says "Done". How do I eliminate both of these things from happening if the search returns no results?

Sorry for all of the updates! Haha

Nate