Results 1 to 10 of 10
  1. #1
    humtake is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    26

    Exporting from text search

    Hey all. I have a form I use to display results from a search. There is a text box, someone enters in a keyword, then click Search and it then displays every record with that keyword individually. Meaning, if 5 records match, the form shows the first record and then I have a Next and Back button that the user can use to view each record.



    I'd like to export those records obtained from the Search to an Excel file.

    Here is the code I have for the Search (which is admittedly borrowed from another site I believe):
    Code:
    Private Sub cmd_SearchbyCompID_Click()
        Dim strsearch As String
        Dim Task As String
        'Check if a keyword entered or not
        If IsNull(Me.txt_SearchOne) Or Me.txt_SearchOne = "" Then
            MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
            Me.txt_SearchOne.BackColor = vbYellow
            Me.txt_SearchOne.SetFocus
        Else
            strsearch = Me.txt_SearchOne.Value
            Task = "SELECT * FROM tbl_Exceptions WHERE ((Exception_ID Like ""*" & strsearch & "*""))"
        Me.RecordSource = Task
        Me.txt_SearchOne.BackColor = vbWhite
    End If
    End Sub
    Is there any way to do this by adding a single button? After my attempts, the process has become complex and isn't efficient at all for the target audience of the form. Any help is appreciated. Thanks!

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Task can be changed into a query which is then exported using DoCmd.TransferSpreadsheet, easily from a button. In the query, change your WHERE to read the field from the form - add Forms!formname in front of strsearch.

  3. #3
    humtake is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    26
    Nice to see you again aytee and thanks for the reply. You cleared up the major hurdle that was making it hard for me so that's good. I kind of follow what you are saying but have other questions. So the only parts of the button should be a macro that runs a SQL query that includes everything in the "Task =" line and a command to export the spreadsheet, correct? Below is what I got from reading your help. Also, what confuses me is how does this statement know what strsearch is since I'm not using the rest of the code to declare it?

    Code:
    SELECT * 
    FROM tbl_Exceptions 
    WHERE ((txt_SearchOne Like ""*" & Forms!frm_Menu.strsearch & "*""))
    I'm sure this post proves my amateur skills in SQL but any other help would be great. Thanks!

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Thanks!

    Make it a real query with a real name. That will be what you use in your export statement. Your button doesn't need to open the query or tun the SQL, not unless you want to view it as you are exporting it. Easy to test the query too, open the form, type something into strsearch and then run the query, see if it is doing what you want it to do. Change the "." to a "!".

    Unless you are an ace at SQL I would also suggest letting Access create the SQL for you.

  5. #5
    humtake is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    26
    I finally get the logic and realize how simple this is. But I still can't get it to work. So, it looks to me like we are ignoring the keyword search altogether. Meaning...I don't have to actually click the "Search by Comp ID" button. I get that. The new query should do that based on whatever is in the text box. One thing though, the text box is named "txt_SearchOne" whereas you are mentioning strsearch which is actually just the declaration in the code. But for this query, we are not declaring that strsearch = the value of the text box, so if I use strsearch the query doesn't know that I mean the value in the box.

    That being said, I am using txt_SearchOne in the query. Below are the fields exactly as I have them in the Design View of the Query:
    Field: Exception_ID
    Table: tbl_Exceptions
    Sort: blank
    Show: checked
    Critieria: Like "*" & [Forms]![frm_Menu]![txt_SearchOne] & "*"
    or: blank

    Note: Access added the brackets, they were not there when I added in the criteria.

    When I run this, I get just the Exception_ID column of every record in the table, and it doesn't exclude the items that do not have what I type in txt_SearchOne. For instance, all of the Exception IDs start with date and a number, like 2016-1. When I type in just 2016 and run the query, it gives me all records...even those that are completely different like 2013-23.

    What I need is the full record with all columns of each Exception_ID that has 2016 in it (or whatever I type in the text box). Any ideas?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Go to the query and manually type in a criteria, give it an actual value. Once you have the query working and know what it is expecting you to enter, then you can go back and reference the form field. You did right in changing the field, it must point to the field on the form using the correct name.

    When you test the query using the form make sure you have exited from the text box first.

    Where is the "or: blank" - is it referring to blank Exception_ID's?

  7. #7
    humtake is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    26
    Ok, I got it working. The form name in my dev database was slightly different than production for some reason, so I was using the wrong form name. I apologize for wasting your time with that. Once I changed to the right form name and kept testing it different ways, it is working great now and even exports. I appreciate your help.

    One related function that may or may not be possible. I have three buttons in my form that all search by a different field (I've only been discussing one of those buttons so far). For example, if someone puts in a name in the txt_SearchOne field, they can press the "Search by Owner" button which will return all findings in which that person is assigned to (which is a different field than Exception_ID). Everything for this search is exactly the same as the code used in this thread. Now, I need to make 3 buttons that all export the findings depending on what they search for. Is there a way to do this by just one button that knows which search button the user pressed? Or any other way?

    If this is a complex answer, I'll pass and just make three buttons. But if there are some simple, quick tips you could give, I can at least give it a shot to see if I can get it to work. Again, thanks for everything!

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Instead of three buttons you can have an option group with just one button. Still, the user will have to choose. Or each of the search fields can set the other two to null in the AfterUpdate, then your export can see which isn't null. Many ways to do this.

    To have one set of code, create a function that is called by all three buttons - do you know about passing parameters in VBA?
    In a nutshell, each button will have something like "Call ExportTheQuery(parameter1, parm2,...)

    Then you will create a Private Function ExportTheQuery(parameter1,parm2,..)

    In this function you will use these parameter(s) to know what to do. For instance, one of the parameters can be the query name which you will use in the export.

  9. #9
    humtake is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    26
    Thank you aytee. That gives me direction and lots to figure out. I'll give it a shot and see what I can do.

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Good luck!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Macros for exporting search results into excel?
    By Mahendra1000 in forum Access
    Replies: 1
    Last Post: 10-09-2015, 06:52 AM
  2. Replies: 3
    Last Post: 09-02-2013, 04:33 PM
  3. Exporting text to acomba
    By Grooz13 in forum Import/Export Data
    Replies: 2
    Last Post: 01-28-2011, 08:34 AM
  4. Exporting to Text File
    By blandow in forum Import/Export Data
    Replies: 2
    Last Post: 08-06-2010, 06:02 PM
  5. exporting text produces a number
    By greend in forum Import/Export Data
    Replies: 0
    Last Post: 07-12-2006, 03:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums