The trouble with WHERE

T

Thumper75

Registered User.
Local time
Today, 00:32
Joined
Feb 6, 2017
Messages
37
Hi everyone, many thanks for the help on the last few items. Mistakes and face-palms on my part.

This one is truly perplexing me.

I have a simple form in which I enter in search parameters to search a particular table. The table is tbl_Log_Pages. Ordinarily I would simply add a control source to the form from the get go, but in this case, for what ever reason, when I add the control source I get Run Time error 3008 and the whole thing stops at the last line of code.

So I went back and removed the control source, but now I wish to utilize the WHERE condition. I have 4 variables which I will be utilizing as search parameters. You can see them in the code below.

Code: 
Private Sub cmd_search_Click()

Dim t As String 'Tail number variable
Dim l As String 'Log page type variable
Dim s As String 'Status variable
Dim v As String 'Severity variable

If IsNull(Me.txt_tail) Then
    t = "*" 'If no value is given make wildcard
Else
    t = Me.txt_tail.Value
End If

If IsNull(Me.cmb_type) Then
    l = "*" 'If no value is given make wildcard
Else
    l = Me.cmb_type.Value
End If

If IsNull(Me.cmb_status) Then
    s = "*" 'If no value is given make wildcard
Else
    s = Me.cmb_status.Value
End If

If IsNull(Me.cmb_sev) Then
    v = "*" 'If no value is given make wildcard
Else
    v = Me.cmb_sev.Value
End If

MsgBox ("The Search string is Tail = " & t & " Type = " & l & " Status = " & s & " Severity = " & v) 'Test the string

DoCmd.Close
DoCmd.OpenForm "frm_Log_Results", acNormal

End Sub

My issue is how to use the WHERE condition as there is no control table linked to the Search form to reference. I understand using the field names of the table, but if the form doesn't have a control table to reference to begin with, the field names are all but useless anyway because the code has no place to look.
It occurs to me that I might be able to put the table to search in the code, but I can find no examples of this to begin with.

Any suggestions or even a reference to fall back onto. I'm actually very close to getting real results if I can figure out just a few more items, I think I may have it.
 
Gina thanks a million for the suggestion! I was able to make everything work......sort of.

I'm still working trough error and or 0 record issues, but I'm making steady progress.

Right now I'm scratching my head with this reset. When I open the form and fill in my parameters it returns the results the way that I want it to, if I change the parameters and hit search again, it still continues to work. However when I hit the reset, and attempt to enter parameters, it hangs up and gives me a Error 3021 "No Current Record"

Code: 
Private Sub cmd_reset_click()

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

Dim ctl As Control

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

Me.lbl_head.Caption = "Log Page Search"
Me.FilterOn = True

End Sub

The problems seems to be with generating the header. I used a recordclone and record count to build my search results. It works fine till I hit the reset. Its hanging up at the first clone.Movelast line.

Code: 
'+++++++++++++++++++++++++++++++
'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.MoveLast 'Its hanging up right here.
cnt = Me.RecordsetClone.RecordCount


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

End Sub

I tried using a refresh method in the Reset code, but it didn't seem to help. I'm thinking I need to set the record count to 0 somehow but I can't seem to figure out how. Any suggestions?
 
Hmm, you might need to add...

Code: 
strHeader = Null

...to the Reset button
 
Gina that didn't seem to work. Then again the strHeader variable is not a part of the Reset subroutine.
 
i'd guess it's because you have no current recordset to create a clone of.
Try this which will check there are records first
Code: 
cnt = 0
If not (Me.RecordsetClone.EOF or Me.RecordsetClone.BOF) Then
Me.RecordsetClone.MoveLast 
cnt = Me.RecordsetClone.RecordCount
End If
 
Minty that corrected the error, but now its not returning anything when I run the search. Any thoughts. I'm putting the code for the entire form in just in case it might help.

Code: 
Option Compare Database

Private Sub cmd_search_Click()

Me.Form.Refresh

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
    cnt = Me.RecordsetClone.RecordCount
End If

'Me.RecordsetClone.MoveLast
'cnt = Me.RecordsetClone.RecordCount

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



'Me.RecordsetClone.Close


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

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


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


End Sub
 
Thumper75 said:
.. but now its not returning anything when I run the search.
Click to expand...
Maybe there are no data there fulfilled the search criteria.
Activate the "'Debug.Print strWhere " and look at the string.
 
where should I place the debug in the code. As for results, I manually checked the table and it should be returning something no matter what conditions I put in. Each condition has at least one record to display, I made sure of that for testing purposes.
 
Just before you use it.
Like you did in the post #7 where it was commented out.
 
Unfortunately I don't seem to be getting anywhere with this one so I'm going to move it to a new thread. I will mark this one solved because Gina provided me with the answer that I originally needed. This one however is making me scratch the skin off my head, and I have more information to share about the troubleshooting process.
 
You must log in or register to reply here.

Similar threads

Y
New with a problem
Replies
4
Views
415
Pat Hartman
P
J
Solved Query v Function
Replies
7
Views
518
John Sh
J
L
Problem typing searh text field
Replies
15
Views
423
LjushaMisha
L
J
Solved searching with a partial string
Replies
15
Views
377
John Sh
J
J
Solved Variable not defined in sql string
Replies
11
Views
458
John Sh
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom