Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-08-2018, 05:02 PM   #1
Thumper75
Newly Registered User
 
Join Date: Feb 2017
Posts: 28
Thanks: 3
Thanked 0 Times in 0 Posts
Thumper75 is on a distinguished road
Rest Button Missing Results

I can't express my thanks enough to the members of this community. You have all been so great helping me find my solutions. I'm learning a great deal everyday and appreciate the welcome that I have received.



I'm working with a form that searches my records based on 4 possible input parameters. Thus far the form has been doing exactly what I need it to. My problem resides with my reset button. The goal is to reset the form to the same exact condition it was when it was first opened.

Code:
Option Compare Database

Private Sub cmd_search_Click()

Dim t As String ' Tail Number [af_reg]
Dim s As String ' Log Page Status [status]
Dim l As String ' Log Page Type [log_type]
Dim v As String ' Discrepancy severity [disc_sev]
Dim lp As String ' Log page number
Dim c As String ' ATA Chapter Number
Dim strWhere As String ' The conglomerated result of all the possible inputs.  The string is built as the code runs.
Dim strHead As String ' Conglomerated result of the value of the inputs to establish the change of the header.  The string is built as the code runs.
Dim lngLen As Long ' Total count of items in the string.  lng = Long, Len = Length


'++++++++++++++++++++++++++++++++++++++++++++++++
'Start checking the inputs for search parameters
'++++++++++++++++++++++++++++++++++++++++++++++++

'Look for a tail number

If Not IsNull(Me.txt_tail.Value) Then
    t = Me.txt_tail.Value
    strWhere = strWhere & "([af_reg] = """ & t & """) AND "
End If

'Look for status
If Not IsNull(Me.cmb_stat.Value) Then
    s = Me.cmb_stat.Value
    strWhere = strWhere & "([status] = """ & s & """) AND "
End If

'look for a log page type
If Not IsNull(Me.cmb_type.Value) Then
    l = Me.cmb_type.Value
    strWhere = strWhere & "([log_type] = """ & l & """) AND "
End If

If Not IsNull(Me.cmb_sev.Value) Then
    v = Me.cmb_sev.Value
    strWhere = strWhere & "([disc_sev] = """ & v & """) AND "
End If

'************************************************************************
'Chop off the trailing " AND ", and use the string as the forms's filter
'************************************************************************

'See if the string has more than 5 characters ( a trailing " AND ") to remove.
lngLen = Len(strWhere) - 5 'This line removes the last five characters of the string

If lngLen <= 0 Then 'There was nothing in the string
    MsgBox "No Criteria selected", vbInformation, "Nothing to do."
Else
    strWhere = Left$(strWhere, lngLen)
    'Debug.Print strWhere
    Me.Filter = strWhere ' Applies the where clause to the filter in the forms properties
    Me.FilterOn = True 'Turns the filter on and executes the search code.
End If

'+++++++++++++++++++++++++++++++
'Build the header
' Total number of records | Log Status Option | Log Page Type | & " Records for " & | Tail number
'+++++++++++++++++++++++++++++++

If Not IsNull(Me.cmb_stat) Then
    strHeader = strHeader & s
End If

If Not IsNull(Me.cmb_type) Then
    strHeader = strHeader & " " & l
End If

If Not IsNull(Me.txt_tail) Then
    strHeader = strHeader & " Log Pages for " & t
Else
    strHeader = strHeader & " Log Pages"
End If

'+++++++++++++++++++++++++++
'Count the number of records
'+++++++++++++++++++++++++++

'Add the record count to the total string
'Me.RecordsetClone.OpenRecordset 'Tried this and it didn't work

'cnt = 0
If Not (Me.RecordsetClone.EOF Or Me.RecordsetClone.BOF) Then
    Me.RecordsetClone.MoveLast  'find the error withn this code
    cnt = Me.RecordsetClone.RecordCount
End If

Me.lbl_head.Caption = "Found " & cnt & " " & strHeader

'cnt = Null

End Sub

Private Sub form_open(Cancel As Integer)

'Make sure that there are no records showing when the form opens

Me.Filter = "(False)"
Me.FilterOn = True

End Sub

Private Sub cmd_reset_click()

'Clear all the search boxes in the form header, and clear the results section

'Dim ctl As Control

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'This Section works

Me.lbl_head.Caption = "Log Page Search"
Me.Form.Filter = "(False)"
Me.Form.FilterOn = True

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

'As soon as I try to reset the fields it stops counting records.  Its like its not even running the search code again.

'Me.txt_tail.Value = ""  'this method had the same result as the For Each case
'Me.cmb_stat.Value = ""
'Me.cmb_type.Value = ""
'Me.cmb_sev.Value = ""



'For Each ctl In Me.FormHeader.Controls
 '   Select Case ctl.ControlType
  '  Case acTextBox, acComboBox
   '     ctl.Value = ctl.DefaultValue
    'End Select
'Next

End Sub

Private Sub cmd_view_Click()
Dim lp As String

lp = Me.txt_logpg

DoCmd.OpenForm "frm_Log_Details", acNormal, , "[Logpg] = '" & lp & "'"


End Sub
So here is what has been happening. If I open the form and enter any combination of parameters, I get the results that I am anticipating. If I change any of the inputs and again click the search button I continue to get the results that I am anticipating.

(this is were it gets weird)
Oddly the reset button works perfectly in that I get a clean form. But after clearing the form and entering new parameters, when I hit search I get 0 results. The strange thing is that the header will reflect the 0 records and parameters as I set it up to do. You can see here where I have commented out the reset of the actual input fields. I have tried a For Each Loop, I have tried manually resetting the control input values. I have moved the actual location within the code of resetting the lbl_Header.caption. What I found is that the first run of the input parameters always seems to work, and each subsequent run also works as long as I don't hit the reset button.
I've been messing with it for two days and discovered something interesting today. When I comment out the reset of the inputs I get no results showing but the inputs are not blanked out. However if I hit the reset with the inputs commented out, change the parameters and then hit search, amazingly I get a new set of results.



Code:
Private Sub cmd_reset_click()

'Clear all the search boxes in the form header, and clear the results section

'Dim ctl As Control

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'This Section works

Me.lbl_head.Caption = "Log Page Search"
Me.Form.Filter = "(False)"
Me.Form.FilterOn = True

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

'As soon as I try to reset the fields it stops counting records.  Its like its not even running the search code again.

'Me.txt_tail.Value = ""  'this method had the same result as the For Each case
'Me.cmb_stat.Value = ""
'Me.cmb_type.Value = ""
'Me.cmb_sev.Value = ""

'For Each ctl In Me.FormHeader.Controls
 '   Select Case ctl.ControlType
  '  Case acTextBox, acComboBox
   '     ctl.Value = ctl.DefaultValue
    'End Select
'Next

End Sub
I'm not entirely sure what is going on here, but it appears that as soon as I attempt to clear the inputs and reset them to null, I stop getting results. It's almost like its not running the code anymore. I know that this can't be true because the Header will change and tell me there are 0 results.

I am wide open to suggestions here. I'm not sure what is going on here.

Thumper75 is offline   Reply With Quote
Old 11-08-2018, 06:28 PM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,280
Thanks: 78
Thanked 1,409 Times in 1,329 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Rest Button Missing Results

You are testing controls for Null but resetting them to a NullString.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
Thumper75 (11-08-2018)
Old 11-08-2018, 07:32 PM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,427
Thanks: 2
Thanked 1,993 Times in 1,949 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Rest Button Missing Results

So to carry out what Galaxiom pointed out:
Code:
Me.txt_tail = Null 
Me.cmb_stat = Null 
Me.cmb_type = Null 
Me.cmb_sev = Null
Then an empty string isn't the same a Null value, and you're testing for a Null value with the IsNull function.

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
Thumper75 (11-08-2018)
Old 11-08-2018, 07:49 PM   #4
Thumper75
Newly Registered User
 
Join Date: Feb 2017
Posts: 28
Thanks: 3
Thanked 0 Times in 0 Posts
Thumper75 is on a distinguished road
Re: Rest Button Missing Results

OMG where is the face palm emoji when you need it. I feel really stupid now. My thanks to both of you that was the solution. In the end I decided to stick with the For Each loop since it works when you tell it to set the correct value. My great thanks to the both of you that just made my night.

Code:
Private Sub cmd_reset_click()

'Clear all the search boxes in the form header, and clear the results section

Dim ctl As Control

Me.lbl_head.Caption = "Log Page Search"
Me.Form.Filter = "(False)"
Me.Form.FilterOn = True

For Each ctl In Me.FormHeader.Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox
        ctl.Value = Null
    End Select
Next

End Sub
Thumper75 is offline   Reply With Quote
Old 11-09-2018, 02:27 AM   #5
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,567
Thanks: 307
Thanked 401 Times in 386 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Rest Button Missing Results

This threw me?

Code:
Me.Form.Filter = "(False)"

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question CLose Button Missing teric2 General 0 11-08-2012 07:49 PM
Query Joins Missing Results hotrodsue Queries 3 07-14-2009 03:03 PM
Missing data after sorting query results AlistairRoylance Queries 10 07-15-2008 11:24 AM
Select Query: Missing results due to two tables JimmyG Queries 2 09-15-2004 06:01 AM
Missing taskbar button for form smercer Forms 0 07-31-2004 03:53 AM




All times are GMT -8. The time now is 10:44 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World