Results 1 to 8 of 8
  1. #1
    omeara4pheonix is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7

    How can i clear a query with vba or macro?


    I have a clear button in my form but it only hides the results, how can i make it clear all of the data in the results query so that it is ready to be ran again?

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    What do you mean?
    A query returns a subset of records and fields from underlying table sources. Deleting the data out of a query would actually delete it out of the underlying tables.
    Is that really what you want to do?

    Or do you have some sort of dynamic query builder that you are talking about, and just want to clear the SQL code of that query so you can build new SQL code?

  3. #3
    omeara4pheonix is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    I have a query that looks up data from various tables and then passes them to a from, I want to have a button on the form that will clear the data out of the query but not affect the tables it depends on.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Data is not "stored" in the query, it is still stored in the underlying form. The query is "bound" to the data tables behind it.
    So making changes to any data that is returned by a query will actually change the data in the underlying table.

    The query just selects which records and fields that you want to see. The Form is just presenting that information to you in the visual manner that you set. But all the data you see in the forms belongs to tables. So edited/deleted it there affects the date int he underlying tables.

    If you want to change what your form returns, you need to "unbind" the query from your Form, or alter the SQL code that your query is returning.

  5. #5
    omeara4pheonix is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    Well there is a Text box on the form in which an item number is entered. Once the search button is pressed the query is opened then closed which allows it to update and only return records with a matching item number. When the clear button is pressed the text box is cleared and i tried opening and closing the query again thinking that no records would appear in the query but the old records are still shown, I there a way i can make it update to showing no records again like it does when the DB is first opened?

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK, what you are talking about is a Search form. That works a little differently. It is either editing the SQL code of the query behind the scenes, or feeding a parameter to a Parameter Query based on the text box entry.

    Can you paste the SQL code behind the Search button? That will show us how it works.
    And also provide any other SQL code associated with that form (in case any defaults are set up there).

  7. #7
    omeara4pheonix is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    My form works like the latter example you mentioned.
    Here is all of the VBA code used for the search form including the search and clear buttons:
    Code:
    Option Compare Database
    
    Public Sub Search_Click()
    On Error GoTo Search_Click_Err
        
        DoCmd.OpenQuery "RM - Raw Material Listing Query", acViewNormal, acReadOnly
        DoCmd.Close acQuery, "RM - Raw Material Listing Query"
        Forms!Search!TestSub.Visible = True
        
        
        
        
    Search_Click_Exit:
        Exit Sub
    
    
    Search_Click_Err:
        MsgBox Error$
        Resume Search_Click_Exit
    
    
    End Sub
    Public Sub Clear_Click()
    On Error GoTo Clear_Click_Err
    
    
        Forms!Search!L1.Value = ""
        Forms!Search!L2.Value = ""
        Forms!Search!L3.Value = ""
        Forms!Search!L4.Value = ""
        Forms!Search!L5.Value = ""
        Forms!Search!L6.Value = ""
        DoCmd.OpenQuery "RM - Raw Material Listing Query", acViewNormal, acReadOnly
        DoCmd.Close acQuery, "RM - Raw Material Listing Query"
        Forms!Search!TestSub.Visible = False
        
    Clear_Click_Exit:
        Exit Sub
    
    
    Clear_Click_Err:
        MsgBox Error$
        Resume Clear_Click_Exit
    
    
    End Sub
    And here is the SQL from the query:
    Code:
    SELECT [RM - Raw Material Listing].Adhesive, Static.[steel-1], Static.[steel-2], Static.[steel-3], Static.[glass-1], Static.[glass-2], Static.[glass-3], Static.[poly-1], Static.[poly-2], Static.[poly-3], Static.[ldp-1], Static.[ldp-2], Static.[ldp-3], Static.[hdp-1], Static.[hdp-2], Static.[hdp-3], Static.[corr-1], Static.[corr-2], Static.[corr-3], Opacity.total_1, Opacity.total_2, Opacity.total_3, Opacity.Face_1, Opacity.Face_2, Opacity.Face_3, Opacity.Liner_1, Opacity.Liner_2, Opacity.Liner_3, Opacity.Total_avg, Opacity.Face_avg, Opacity.Liner_avg, Stiffness.Face_MD_1, Stiffness.Face_MD_2, Stiffness.Face_MD_3, Stiffness.Face_CD_1, Stiffness.Face_CD_2, Stiffness.Face_CD_3, Stiffness.Liner_MD_1, Stiffness.Liner_MD_2, Stiffness.Liner_MD_3, Stiffness.Liner_CD_1, Stiffness.Liner_CD_2, Stiffness.Liner_CD_3, Tear.[g-force-Md-1], Tear.[lb-force-Md-1], Tear.[mN-Md-1], Tear.[g-force-Md-2], Tear.[lb-force-Md-2], Tear.[mN-Md-2], Tear.[g-force-Md-3], Tear.[lb-force-Md-3], Tear.[mN-Md-3], Tear.[g-force-Cd-1], Tear.[lb-force-Cd-1], Tear.[mN-Cd-1], Tear.[g-force-Cd-2], Tear.[lb-force-Cd-2], Tear.[mN-Cd-2], Tear.[g-force-Cd-3], Tear.[lb-force-Cd-3], Tear.[mN-Cd-3], Tensile.Peak_MD_1, Tensile.Peak_MD_2, Tensile.Peak_MD_3, Tensile.Peak_MD_4, Tensile.Peak_CD_1, Tensile.Peak_CD_2, Tensile.Peak_CD_3, Tensile.Peak_CD_4, Tensile.Fail_MD_1, Tensile.Fail_MD_2, Tensile.Fail_MD_3, Tensile.Fail_MD_4, Tensile.Fail_CD_1, Tensile.Fail_CD_2, Tensile.Fail_CD_3, Tensile.Fail_CD_4, [Weights and Thickness].[Total Weight], [Weights and Thickness].[Face Weight], [Weights and Thickness].[Liner weight], [Weights and Thickness].[Adhesive Weight], [Weights and Thickness].[Total thickness 1], [Weights and Thickness].[Total Thickness 2], [Weights and Thickness].[Total Thickness 3], [Weights and Thickness].[Face Thickness 1], [Weights and Thickness].[Face Thickness 2], [Weights and Thickness].[Face Thickness 3], [Weights and Thickness].[Liner Thickness 1], [Weights and Thickness].[Liner Thickness 2], [Weights and Thickness].[Liner Thickness 3], [Weights and Thickness].[Adhesive Thickness 1], [Weights and Thickness].[Adhisive Thickness 2], [Weights and Thickness].[Adhesive Thickness 3], [Low Speed Release].[Average 1], [Low Speed Release].[Max 1], [Low Speed Release].[Min 1], [Low Speed Release].[Average 2], [Low Speed Release].[Max 2], [Low Speed Release].[Min 2], [Low Speed Release].[Average 3], [Low Speed Release].[Max 3], [Low Speed Release].[Min 3], Looptack.Steel_load_1, Looptack.Steel_mode_1, Looptack.steel_load_2, Looptack.steel_mode_2, Looptack.eel_load_3, Looptack.steel_mode_3, Looptack.glass_load_1, Looptack.glass_mode_1, Looptack.glass_load_2, Looptack.glass_mode_2, Looptack.glass_load_3, Looptack.glass_mode_3, Looptack.ploy_load_1, Looptack.poly_mode_1, Looptack.poly_Load_2, Looptack.poly_mode_2, Looptack.poly_load_3, Looptack.poly_mode_3, Looptack.ldp_load_1, Looptack.ldp_mode_1, Looptack.ldp_load_2, Looptack.ldp_mode_2, Looptack.ldp_load_3, Looptack.ldp_mode_3, Looptack.hdp_load_1, Looptack.hdp_mode_1, Looptack.hdp_load_2, Looptack.hdp_mode_2, Looptack.hdp_load_3, Looptack.hdp_load_3, Looptack.hdp_mode_3, Looptack.corr_load_1, Looptack.corr_mode_1, Looptack.corr_load_2, Looptack.corr_mode_2, Looptack.corr_load_3, Looptack.corr_mode_3, [L#].[L#]FROM (([RM - Raw Material Listing] LEFT JOIN Static ON [RM - Raw Material Listing].[Adhesive] = Static.[Adhesive]) LEFT JOIN (((((([L#] LEFT JOIN Opacity ON [L#].[L#] = Opacity.[L#]) LEFT JOIN Stiffness ON [L#].[L#] = Stiffness.[L#]) LEFT JOIN Tear ON [L#].[L#] = Tear.[L#]) LEFT JOIN Tensile ON [L#].[L#] = Tensile.[L#]) LEFT JOIN [Weights and Thickness] ON [L#].[L#] = [Weights and Thickness].[L#]) LEFT JOIN [Low Speed Release] ON [L#].[L#] = [Low Speed Release].[L#]) ON [RM - Raw Material Listing].[Part Number] = [L#].[L#]) LEFT JOIN Looptack ON [RM - Raw Material Listing].[Adhesive] = Looptack.[Adhesive]
    WHERE ((([L#].[L#])=[Forms]![Search]![L1] Or ([L#].[L#])=[Forms]![Search]![L2] Or ([L#].[L#])=[Forms]![Search]![L3] Or ([L#].[L#])=[Forms]![Search]![L4] Or ([L#].[L#])=[Forms]![Search]![L5] Or ([L#].[L#])=[Forms]![Search]![L6]));

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    After you clear all of your Form values, you may need to refresh the query with a statement like:
    Code:
    Me.Refresh
    Also, what are you trying to do by opening the query after removing all the search values, and then immediately closing it again?

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

Similar Threads

  1. Macro to add and clear fields
    By Skroof in forum Access
    Replies: 1
    Last Post: 05-14-2012, 01:13 PM
  2. Replies: 2
    Last Post: 05-05-2012, 02:34 AM
  3. Replies: 0
    Last Post: 01-12-2011, 12:43 PM
  4. How to clear the form?
    By Mrcams in forum Access
    Replies: 3
    Last Post: 01-03-2011, 12:15 AM
  5. Clear all checkboxes?
    By thekruser in forum Forms
    Replies: 2
    Last Post: 09-16-2010, 09:50 AM

Tags for this Thread

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