Rest Button Missing Results (1 Viewer)

Thumper75

Registered User.
Local time
Today, 08:55
Joined
Feb 6, 2017
Messages
37
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.

:banghead:

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) :banghead::banghead::banghead:
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. :confused:

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

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:55
Joined
Jan 20, 2009
Messages
12,849
You are testing controls for Null but resetting them to a NullString.
 

JHB

Have been here a while
Local time
Today, 14:55
Joined
Jun 17, 2012
Messages
7,732
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.
 

Thumper75

Registered User.
Local time
Today, 08:55
Joined
Feb 6, 2017
Messages
37
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
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:55
Joined
Sep 21, 2011
Messages
14,052
This threw me?

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

Users who are viewing this thread

Top Bottom