Search button stop working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

My search button stop working in my form. It was working before, now it
stop. How can I fix it? This is what I have in my VBA,

Private Sub cmdSearch_Click()
Dim db As Database, rec As Recordset, lngCount As Long, intRecord As
Integer, rcd As DAO.Recordset
Dim whereAV As String, strOdate As String, strSQL As String
strSQL = "select * from AV Services where AVID"

If Not IsNothing(Me!AVID) Then
whereAV = "[AVID] Like " & Me!AVID
End If

If Not IsNothing(Me!LastName) Then
If IsNothing(whereAV) Then
whereAV = "[CLastName] like " & Chr$(34) & (Me!LastName)
Else
whereAV = whereAV & "AND [CLastName] like " & Chr$(34) &
(Me!LastName)
End If
If Right$(Me!LastName, 1) = "*" Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If

If Not IsNothing(Me!CustomerID) Then
If IsNothing(whereAV) Then
whereAV = "[CFirstName] like " & Chr$(34) & (Me!CustomerID)
Else
whereAV = whereAV & "AND [CfirstName] like " & Chr$(34) &
(Me!CustomerID)
End If
If Right$(Me!CustomerID, 1) = "*" Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If
' ********For request Date*********
If Not IsNothing(Me!Date) Then
If IsNothing(whereAV) Then
whereAV = "[Date] Like " & Chr$(34) & (Me!Date)
Visible = True
Date.ForeColor = vbRed
Else
whereAV = whereAV & "AND [Date] like " & Chr$(34) & (Me!Date)
Date.ForeColor = vbRed
End If
If Right$(Me!Date, 1) = "*" Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If

If Not IsNothing(Me!Entrydate) Then
If IsNothing(whereAV) Then
whereAV = "[EntryDate] Like " & Chr$(34) & (Me!Entrydate)
Else
whereAV = whereAV & "AND [EntryDate] like " & Chr$(34) & (Me!Entrydate)
End If
If Right$(Me!Entrydate, 1) = "*" Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If

If Not IsNothing(Me!Account) Then
If IsNothing(whereAV) Then
whereAV = "[Account] like " & Chr$(34) & Me!Account
Else
whereAV = whereAV & "AND [Account] like " & Chr$(34) & Me!Account
End If
If Right$(Me!Account, 1) = "*" Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If

If Not IsNothing(Me!Department) Then
If IsNothing(whereAV) Then
whereAV = "[Department] like " & Chr$(34) & Me!Department
Else
whereAV = whereAV & "AND [Department] like " & Chr$(34) &
Me!Department
End If
If Right$(Me!Department, 1) = "*" Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If


If Not IsNothing(Me!Start) Then
If IsNothing(whereAV) Then
whereAV = "[start] Like " & Chr$(34) & Me!Start
Else
whereAV = whereAV & "AND [start] " & Chr$(34) & Me!Start
End If
If Right$(Me!Start, 1) = "* " Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If

If IsNothing(whereAV) Then
MsgBox "No criteria specified.", 32
Exit Sub
End If
Me.Visible = False
' Hide myself and turn on Hourglass
DoCmd.Hourglass True
If IsLoaded("AVSummary") Then ' If AV form already open, Then just
filter it
Forms!AVForm.SetFocus
DoCmd.ApplyFilter , whereAV
If Forms![AVForm].RecordsetClone.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No Record meet your criteria 1st message", 64
DoCmd.ShowAllRecords
Forms!AVForm!CmdAddNew.Visible = True
Forms!AVForm!CmdShowAll.Visible = False

Exit Sub
End If
DoCmd.Hourglass False
Forms!AVForm!CmdAddNew.Visible = False
Forms!AVForm!CmdShowAll.Visible = True
Else
' Find out if any Record satisfy the Where clause
Set db = CurrentDb
Set rec = db.OpenRecordset("AV Services")
' If none found, then tell them and make me visible to try again
If rec.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No AV Record meet your criteria", vbInformation
whereAV = " "
Me!Visible = True
rec.Close
Exit Sub
End If
' Move to last row to get an accurate record count
rec.MoveLast
lngCount = rec.RecordCount
DoCmd.Hourglass False
' If more than 5, then ask if they want to see a summary
If lngCount >= 5 Then
intRecord = MsgBox("Would you like to review this record?,",
vbInformation + vbOKCancel)

Select Case intRecord
Case vbCancel ' Cancel - Try again
Me.Visible = True
Exit Sub
Case vbYes ' Yes - show summary form
DoCmd.OpenForm FormName:="AVSummary",
wherecondition:=whereAV
DoCmd.Close acForm, Me.Name
Forms!AVsummary.SetFocus
Exit Sub
End Select
End If
' Replied NO or not more than 2, show full details
DoCmd.OpenForm FormName:="AVSummary",
wherecondition:=whereAV

End If
DoCmd.Close acForm, Me.Name
' Close me, and we're done Yeah

End Sub
 
Comments inline....

Bessie said:
Hello,

My search button stop working in my form. It was working before, now it
stop. How can I fix it? This is what I have in my VBA,
Click to expand...

Did it Stop working after you made changes? What changes have you made???
Private Sub cmdSearch_Click()
Dim db As Database, rec As Recordset, lngCount As Long, intRecord As
Integer, rcd As DAO.Recordset
Dim whereAV As String, strOdate As String, strSQL As String
strSQL = "select * from AV Services where AVID"

If Not IsNothing(Me!AVID) Then
whereAV = "[AVID] Like " & Me!AVID
End If

If Not IsNothing(Me!LastName) Then
If IsNothing(whereAV) Then
whereAV = "[CLastName] like " & Chr$(34) & (Me!LastName)
Else
whereAV = whereAV & "AND [CLastName] like " & Chr$(34) &
(Me!LastName)
End If
If Right$(Me!LastName, 1) = "*" Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If

If Not IsNothing(Me!CustomerID) Then
If IsNothing(whereAV) Then
whereAV = "[CFirstName] like " & Chr$(34) & (Me!CustomerID)
Else
whereAV = whereAV & "AND [CfirstName] like " & Chr$(34) &
(Me!CustomerID)
Click to expand...

Why are you looking at Me!CustomerID (numeric?) and using the field
[CfirstName] (string)?
End If
If Right$(Me!CustomerID, 1) = "*" Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If
' ********For request Date*********
If Not IsNothing(Me!Date) Then
If IsNothing(whereAV) Then
whereAV = "[Date] Like " & Chr$(34) & (Me!Date)
Visible = True
Date.ForeColor = vbRed
Else
whereAV = whereAV & "AND [Date] like " & Chr$(34) & (Me!Date)
Date.ForeColor = vbRed
End If
If Right$(Me!Date, 1) = "*" Then
Click to expand...

Shouldn't use "DATE" as a control/field name; it is a reserved word.
The code above will give you

"[Date] Like 1/1/2005*"

I don't think that is what you want.
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If

If Not IsNothing(Me!Entrydate) Then
If IsNothing(whereAV) Then
whereAV = "[EntryDate] Like " & Chr$(34) & (Me!Entrydate)
Else
whereAV = whereAV & "AND [EntryDate] like " & Chr$(34) & (Me!Entrydate)
End If
If Right$(Me!Entrydate, 1) = "*" Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If
Click to expand...

This also results in:

"[EntryDate] Like 1/20/2005*"
If Not IsNothing(Me!Account) Then
If IsNothing(whereAV) Then
whereAV = "[Account] like " & Chr$(34) & Me!Account
Else
whereAV = whereAV & "AND [Account] like " & Chr$(34) & Me!Account
End If
If Right$(Me!Account, 1) = "*" Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If
Click to expand...

Is the Account field a number or text? If it is a number, you shouldn't
enclose it in quotes.
If Not IsNothing(Me!Department) Then
If IsNothing(whereAV) Then
whereAV = "[Department] like " & Chr$(34) & Me!Department
Else
whereAV = whereAV & "AND [Department] like " & Chr$(34) &
Me!Department
End If
If Right$(Me!Department, 1) = "*" Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If


If Not IsNothing(Me!Start) Then
If IsNothing(whereAV) Then
whereAV = "[start] Like " & Chr$(34) & Me!Start
Else
whereAV = whereAV & "AND [start] " & Chr$(34) & Me!Start
End If
If Right$(Me!Start, 1) = "* " Then
whereAV = whereAV & Chr$(34)
Else
whereAV = whereAV & "*" & Chr$(34)
End If
End If
Click to expand...

I made a form and added the 8 controls from above, then ran the code. I
added a

Debug.Print whereAV

line to see what whereAV looked like. This is the result:

[AVID] Like 123AND [CLastName] like "Smith*"AND [CfirstName] like "456*"AND
[Date] like "1/1/2005*"AND [EntryDate] like "1/10/2005*"AND [Account] like
"abc789*"AND [Department] like "Sales*"AND [start] "Now*"


Note that there is no spaces in front of any of the AND's which, at best,
will cause the filter to return zero records, and at worst, result in an
error.

If IsNothing(whereAV) Then
MsgBox "No criteria specified.", 32
Exit Sub
End If
Me.Visible = False
' Hide myself and turn on Hourglass
DoCmd.Hourglass True
If IsLoaded("AVSummary") Then ' If AV form already open, Then just
filter it
Forms!AVForm.SetFocus
DoCmd.ApplyFilter , whereAV
If Forms![AVForm].RecordsetClone.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No Record meet your criteria 1st message", 64
DoCmd.ShowAllRecords
Forms!AVForm!CmdAddNew.Visible = True
Forms!AVForm!CmdShowAll.Visible = False

Exit Sub
End If
DoCmd.Hourglass False
Forms!AVForm!CmdAddNew.Visible = False
Forms!AVForm!CmdShowAll.Visible = True
Else
' Find out if any Record satisfy the Where clause
Set db = CurrentDb
Set rec = db.OpenRecordset("AV Services")
' If none found, then tell them and make me visible to try again
If rec.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No AV Record meet your criteria", vbInformation
whereAV = " "
Me!Visible = True
rec.Close
Exit Sub
End If
' Move to last row to get an accurate record count
rec.MoveLast
lngCount = rec.RecordCount
DoCmd.Hourglass False
' If more than 5, then ask if they want to see a summary
If lngCount >= 5 Then
intRecord = MsgBox("Would you like to review this record?,",
vbInformation + vbOKCancel)

Select Case intRecord
Case vbCancel ' Cancel - Try again
Me.Visible = True
Exit Sub
Case vbYes ' Yes - show summary form
DoCmd.OpenForm FormName:="AVSummary",
wherecondition:=whereAV
DoCmd.Close acForm, Me.Name
Forms!AVsummary.SetFocus
Exit Sub
End Select
End If
' Replied NO or not more than 2, show full details
DoCmd.OpenForm FormName:="AVSummary",
wherecondition:=whereAV

End If
DoCmd.Close acForm, Me.Name
' Close me, and we're done Yeah
Click to expand...

There are several places, after you open recordset 'rec', where you use
'Exit Sub' without closing the recordset.

And you should destroy the objects you create:

Set rec = Nothing
Set db = Nothing

End Sub
Click to expand...

If this doesn't help, post back with more information: error message, field
types, where it stops....

I don't want to tell you how many hours I spent trying to get some code to
work, only to find I was missing a space in front of an 'AND' in a SQL string
I was creating on the fly. :D

Steve
 
Back
Top