Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    25

    Unanswered: Clear Button on a Form

    I'm trying to create a search form with drop down boxes to select criteria.

    I would like a "Clear Form" button that removes any data on the form to start a new search with. I have the following command in an "on click" event. But when I use this, it does clear the form. But from then on out, I get no results from my form. Even submitting the form with every entry blank, I get no results, when I know I have data in the form. I verified with my tables that the data in them didn't get deleted by using the code to clear the form.

    Private Sub ClearForm_Click()

    Me.[cmbNCTool].Value = ""
    Me.[cmbNCDivision].Value = ""
    Me.[cmbToolMaterial].Value = ""
    Me.[cmbProfile].Value = ""
    Me.[cmbToolType].Value = ""
    Me.[cmbProfileType].Value = ""
    Me.[cmbBoardThick].Value = ""
    Me.[cmbMinorD].Value = ""
    Me.[cmbShankD].Value = ""
    Me.[cmbMaxCut].Value = ""
    Me.[txtNotes].Value = ""
    Me.[txtDesc].Value = ""
    Me.[chkUsedArksCtyVeneer].Value = ""
    Me.[chkUsedCorbinFoil].Value = ""
    Me.[chkUsedFFallsVeneer].Value = ""
    Me.[chkUsedFFallsFoil].Value = ""
    Me.[chkUsedVbrgVeneer].Value = ""
    Me.[chkUsedVbrgWood].Value = ""

    End Sub

    From what I know, this should only clear my form, not cause it to mess with results after it's used to clear the form once. I've even deleted this line of code from my database itself and I still get 0 responses back when I search.

    Any ideas what I'm doing wrong, or is there a better way to clear a form with a button to click on?

    Thanks for any help you can provide.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    For correctness I would set check baoxes to False. For any other help I think you will need to provide the code that creates the search string (SQL).

  3. #3
    Join Date
    May 2006
    Posts
    25
    Quote Originally Posted by DCKunkle
    For correctness I would set check baoxes to False. For any other help I think you will need to provide the code that creates the search string (SQL).
    Here is the code that performs the search when the "Search" button is clicked

    PHP Code:
    Private Sub Search_Click()

    Dim strSelectstrWherestrFrom As String
    Dim strNcTool
    strNcDivstrMaterial As String
    Dim defQuery 
    As QueryDef



        strSelect 
    "SELECT [Tooling Information].[NC Tool #], [Tooling Information].[NC Division], [Tooling Information].[Profile # or Name], [Tooling Information].[Profile Type], [Tooling Information].[Tool Type], [Tooling Information].Notes, [Tooling Information].[Tool Material], [Tooling Information].[Shank Diameter], [Tooling Information].[Minor Diameter], [Tooling Information].[Max Cut Depth], [Tooling Information].[Board Thickness], [Tooling Information].[Tool Description], [Tooling Information].[Tool used in Fergus Falls - Veneer], [Tooling Information].[Tool used in Corbin - Thermofoil], [Tooling Information].[Tool used in Vanceburg, KY - Veneer], [Tooling Information].[Tool used in Vanceburg, KY - Wood], [Tooling Information].[Tool used in Arkansas City, KS - Veneer], [Tooling Information].[Tool used in Fergus Falls - Thermofoil], [Tooling Information].[AutoCAD File], [Tooling Information].[PDF File]"
        
    strFrom "  FROM [Tooling Information]"
        
        
    If IsNull(Me.cmbNCTool) = False Then
            strWhere 
    " Where ([NC Tool #]='" Me.cmbNCTool "')"
        
    End If
        
        If 
    IsNull(Me.cmbNCDivision) = False Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([NC Division]='" Me.cmbNCDivision "')"
            
    Else
                
    strWhere " Where  ([NC Division]='" Me.cmbNCDivision "')"
            
    End If
        
    End If
        
        If 
    IsNull(Me.cmbToolMaterial) = False Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Tool Material]='" Me.cmbToolMaterial "')"
            
    Else
                
    strWhere " Where([Tool Material]='" Me.cmbToolMaterial "')"
            
    End If
        
    End If
        
         If 
    IsNull(Me.cmbProfile) = False Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Profile # or Name]='" Me.cmbProfile "')"
            
    Else
                
    strWhere " Where([Profile # or Name]='" Me.cmbProfile "')"
            
    End If
        
    End If
        
         If 
    IsNull(Me.cmbToolType) = False Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Tool Type]='" Me.cmbToolType "')"
            
    Else
                
    strWhere " Where([Tool Type]='" Me.cmbToolType "')"
            
    End If
        
    End If
        
        If 
    IsNull(Me.cmbProfileType) = False Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Profile Type]='" Me.cmbProfileType "')"
            
    Else
                
    strWhere " Where([Profile Type]='" Me.cmbProfileType "')"
            
    End If
        
    End If
        
         If 
    IsNull(Me.cmbBoardThick) = False Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Board Thickness]='" Me.cmbBoardThick "')"
            
    Else
                
    strWhere " Where([Board Thickness]='" Me.cmbBoardThick "')"
            
    End If
        
    End If
        
        If 
    IsNull(Me.cmbMinorD) = False Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Minor Diameter]='" Me.cmbMinorD "')"
            
    Else
                
    strWhere " Where([Minor Diameter]='" Me.cmbMinorD "')"
            
    End If
        
    End If
        
         If 
    IsNull(Me.cmbShankD) = False Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Shank Diameter]='" Me.cmbShankD "')"
            
    Else
                
    strWhere " Where([Shank Diameter]='" Me.cmbShankD "')"
            
    End If
        
    End If
        
        If 
    IsNull(Me.cmbMaxCut) = False Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Max Cut Depth]='" Me.cmbMaxCut "')"
            
    Else
                
    strWhere " Where([Max Cut Depth]='" Me.cmbMaxCut "')"
            
    End If
        
    End If

         If 
    Me.[chkUsedArksCtyVeneer] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool used in Arkansas City, KS - Veneer]=-1"
            
    Else
              
    strWhere " Where [Tool used in Arkansas City, KS - Veneer]=-1"
            
    End If
        
    End If
        
        If 
    Me.[chkUsedCorbinFoil] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool used in Corbin - Thermofoil]=-1"
            
    Else
              
    strWhere " Where [Tool used in Corbin - Thermofoil]=-1"
            
    End If
        
    End If
        
         If 
    Me.[chkUsedFFallsVeneer] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool Used in Fergus Falls - Veneer]=-1"
            
    Else
              
    strWhere " Where [Tool Used in Fergus Falls - Veneer]=-1"
            
    End If
        
    End If
        
         If 
    Me.[chkUsedFFallsFoil] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool used in Fergus Falls - Thermofoil]=-1"
            
    Else
              
    strWhere " Where [Tool used in Fergus Falls - Thermofoil]=-1"
            
    End If
        
    End If
            
         If 
    Me.[chkUsedVbrgVeneer] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool used in Vanceburg, KY - Veneer]=-1"
            
    Else
              
    strWhere " Where [Tool used in Vanceburg, KY - Veneer]=-1"
            
    End If
        
    End If
        
        If 
    Me.[chkUsedVbrgWood] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool used in Vanceburg, KY - Wood]=-1"
            
    Else
              
    strWhere " Where [Tool used in Vanceburg, KY - Wood]=-1"
            
    End If
        
    End If
            
         If 
    IsNull(Me.txtNotes) = False Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Notes] Like '*" Me.txtNotes "*'"
            
    Else
              
    strWhere " Where [Notes] Like '*" Me.txtNotes "*'"
            
    End If
        
    End If
        
        If 
    IsNull(Me.txtDesc) = False Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool Description] Like '*" Me.txtDesc "*'"
            
    Else
              
    strWhere " Where [Tool Description] Like '*" Me.txtDesc "*'"
            
    End If
        
    End If
             
        
    Set defQuery CurrentDb.QueryDefs("Query1")
        
        
    defQuery.SQL strSelect strFrom strWhere
        defQuery
    .Close
        
        DoCmd
    .OpenForm "Tooling Information"
        
        
        
    End Sub 

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    If your combo boxes are linked to data, you don't want to assign a blank value as a way to clear away visible text. To clear, I would try
    Me!cmbName.Requery

    Good Luck!
    Jerry

  5. #5
    Join Date
    May 2006
    Posts
    25
    I got my coding to work. Here's the final results

    PHP Code:
    Option Compare Database

    Private Sub Search_Click()

    Dim strSelectstrWherestrFrom As String
    Dim strNcTool
    strNcDivstrMaterial As String
    Dim defQuery 
    As QueryDef



        strSelect 
    "SELECT [Tooling Information].[NC Tool #], [Tooling Information].[NC Division], [Tooling Information].[Profile # or Name], [Tooling Information].[Profile Type], [Tooling Information].[Tool Type], [Tooling Information].Notes, [Tooling Information].[Tool Material], [Tooling Information].[Shank Diameter], [Tooling Information].[Minor Diameter], [Tooling Information].[Max Cut Depth], [Tooling Information].[Board Thickness], [Tooling Information].[Tool Description], [Tooling Information].[Tool used in Fergus Falls - Veneer], [Tooling Information].[Tool used in Corbin - Thermofoil], [Tooling Information].[Tool used in Vanceburg, KY - Veneer], [Tooling Information].[Tool used in Vanceburg, KY - Wood], [Tooling Information].[Tool used in Arkansas City, KS - Veneer], [Tooling Information].[Tool used in Fergus Falls - Thermofoil], [Tooling Information].[AutoCAD File], [Tooling Information].[PDF File]"
        
    strFrom "  FROM [Tooling Information]"
        
        
    If IsNull(Me.cmbNCTool) = False And Len(Me.[cmbNCTool]) > 1 Then
            strWhere 
    " Where ([NC Tool #]='" Me.cmbNCTool "')"
        
    End If
        
        If 
    IsNull(Me.cmbNCDivision) = False And Len(Me.[cmbNCDivision]) > 1 Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([NC Division]='" Me.cmbNCDivision "')"
            
    Else
                
    strWhere " Where  ([NC Division]='" Me.cmbNCDivision "')"
            
    End If
        
    End If
        
        If 
    IsNull(Me.cmbToolMaterial) = False And Len(Me.[cmbToolMaterial]) > 1 Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Tool Material]='" Me.cmbToolMaterial "')"
            
    Else
                
    strWhere " Where([Tool Material]='" Me.cmbToolMaterial "')"
            
    End If
        
    End If
        
         If 
    IsNull(Me.cmbProfile) = False And Len(Me.[cmbProfile]) > 1 Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Profile # or Name]='" Me.cmbProfile "')"
            
    Else
                
    strWhere " Where([Profile # or Name]='" Me.cmbProfile "')"
            
    End If
        
    End If
        
         If 
    IsNull(Me.cmbToolType) = False And Len(Me.[cmbToolType]) > 1 Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Tool Type]='" Me.cmbToolType "')"
            
    Else
                
    strWhere " Where([Tool Type]='" Me.cmbToolType "')"
            
    End If
        
    End If
        
        If 
    IsNull(Me.cmbProfileType) = False And Len(Me.[cmbProfileType]) > 1 Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Profile Type]='" Me.cmbProfileType "')"
            
    Else
                
    strWhere " Where([Profile Type]='" Me.cmbProfileType "')"
            
    End If
        
    End If
        
         If 
    IsNull(Me.cmbBoardThick) = False And Len(Me.[cmbBoardThick]) > 1 Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Board Thickness]='" Me.cmbBoardThick "')"
            
    Else
                
    strWhere " Where([Board Thickness]='" Me.cmbBoardThick "')"
            
    End If
        
    End If
        
        If 
    IsNull(Me.cmbMinorD) = False And Len(Me.[cmbMinorD]) > 1 Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Minor Diameter]='" Me.cmbMinorD "')"
            
    Else
                
    strWhere " Where([Minor Diameter]='" Me.cmbMinorD "')"
            
    End If
        
    End If
        
         If 
    IsNull(Me.cmbShankD) = False And Len(Me.[cmbShankD]) > 1 Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Shank Diameter]='" Me.cmbShankD "')"
            
    Else
                
    strWhere " Where([Shank Diameter]='" Me.cmbShankD "')"
            
    End If
        
    End If
        
        If 
    IsNull(Me.cmbMaxCut) = False And Len(Me.[cmbMaxCut]) > 1 Then
            
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere 
    strWhere " AND ([Max Cut Depth]='" Me.cmbMaxCut "')"
            
    Else
                
    strWhere " Where([Max Cut Depth]='" Me.cmbMaxCut "')"
            
    End If
        
    End If

         If 
    Me.[chkUsedArksCtyVeneer] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool used in Arkansas City, KS - Veneer]=-1"
            
    Else
              
    strWhere " Where [Tool used in Arkansas City, KS - Veneer]=-1"
            
    End If
        
    End If
        
        If 
    Me.[chkUsedCorbinFoil] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool used in Corbin - Thermofoil]=-1"
            
    Else
              
    strWhere " Where [Tool used in Corbin - Thermofoil]=-1"
            
    End If
        
    End If
        
         If 
    Me.[chkUsedFFallsVeneer] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool Used in Fergus Falls - Veneer]=-1"
            
    Else
              
    strWhere " Where [Tool Used in Fergus Falls - Veneer]=-1"
            
    End If
        
    End If
        
         If 
    Me.[chkUsedFFallsFoil] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool used in Fergus Falls - Thermofoil]=-1"
            
    Else
              
    strWhere " Where [Tool used in Fergus Falls - Thermofoil]=-1"
            
    End If
        
    End If
            
         If 
    Me.[chkUsedVbrgVeneer] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool used in Vanceburg, KY - Veneer]=-1"
            
    Else
              
    strWhere " Where [Tool used in Vanceburg, KY - Veneer]=-1"
            
    End If
        
    End If
        
        If 
    Me.[chkUsedVbrgWood] = -1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool used in Vanceburg, KY - Wood]=-1"
            
    Else
              
    strWhere " Where [Tool used in Vanceburg, KY - Wood]=-1"
            
    End If
        
    End If
            
         If 
    IsNull(Me.txtNotes) = False And Len(Me.[txtNotes]) > 1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Notes] Like '*" Me.txtNotes "*'"
            
    Else
              
    strWhere " Where [Notes] Like '*" Me.txtNotes "*'"
            
    End If
        
    End If
        
        If 
    IsNull(Me.txtDesc) = False And Len(Me.[txtDesc]) > 1 Then
            
    If Len(strWhere) > 2 Then
              strWhere 
    strWhere " AND [Tool Description] Like '*" Me.txtDesc "*'"
            
    Else
              
    strWhere " Where [Tool Description] Like '*" Me.txtDesc "*'"
            
    End If
        
    End If
             
        
    Set defQuery CurrentDb.QueryDefs("Query1")
        
        
    defQuery.SQL strSelect strFrom strWhere
        defQuery
    .Close
        
        DoCmd
    .OpenForm "Tooling Information"
        
        
        
    End Sub


    Private Sub ClearForm_Click()

        
    Me.[cmbNCTool].Value ""
        
    Me.[cmbNCDivision].Value ""
        
    Me.[cmbToolMaterial].Value ""
        
    Me.[cmbProfile].Value ""
        
    Me.[cmbToolType].Value ""
        
    Me.[cmbProfileType].Value ""
        
    Me.[cmbBoardThick].Value ""
        
    Me.[cmbMinorD].Value ""
        
    Me.[cmbShankD].Value ""
        
    Me.[cmbMaxCut].Value ""
        
    Me.[txtNotes].Value ""
        
    Me.[txtDesc].Value ""
        
    Me.[chkUsedArksCtyVeneer].Value ""
        
    Me.[chkUsedCorbinFoil].Value ""
        
    Me.[chkUsedFFallsVeneer].Value ""
        
    Me.[chkUsedFFallsFoil].Value ""
        
    Me.[chkUsedVbrgVeneer].Value ""
        
    Me.[chkUsedVbrgWood].Value ""
        
        
    CurrentDb.QueryDefs("Query1").SQL "SELECT [Tooling Information].[NC Tool #], [Tooling Information].[NC Division], [Tooling Information].[Profile # or Name], [Tooling Information].[Profile Type], [Tooling Information].[Tool Type], [Tooling Information].Notes, [Tooling Information].[Tool Material], [Tooling Information].[Shank Diameter], [Tooling Information].[Minor Diameter], [Tooling Information].[Max Cut Depth], [Tooling Information].[Board Thickness], [Tooling Information].[Tool Description], [Tooling Information].[Tool used in Fergus Falls - Veneer], [Tooling Information].[Tool used in Corbin - Thermofoil], [Tooling Information].[Tool used in Vanceburg, KY - Veneer], [Tooling Information].[Tool used in Vanceburg, KY - Wood], [Tooling Information].[Tool used in Arkansas City, KS - Veneer], [Tooling Information].[Tool used in Fergus Falls - Thermofoil], [Tooling Information].[AutoCAD File], [Tooling Information].[PDF File] FROM [Tooling Information]"
        
    CurrentDb.QueryDefs("Query1").Close
        
    End Sub 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •