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.
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.
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.
: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.
I am wide open to suggestions here. I'm not sure what is going on here.