Text search routine convert for ADP

[Massinova]

Hi, I'm attempting to utilize the below code in an ADP project. It works great with an MDB, but our data will be residing permanently on a SQL server. What I'm encountering is that I'm unable to get the results on the list-box, but the list box does see and read the field names of the table on the sql-server.

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT dbo.Area.Recno_Area, dbo.Area.Agreement_Number, dbo.Area.County, dbo.Area.Meridian, dbo.Area.State, dbo.Area.Township, dbo.Area.Township_Dir, dbo.Area.Range, dbo.Area.Range_Dir, dbo.Area.Section, dbo.Area.[Abstract (Texas)], dbo.Area.[Survey (Texas)] " & _
"FROM dbo.dbo.Area"

strWhere = "WHERE"

strOrder = "ORDER BY dbo.Area.Recno_dbo.Area;"


'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtAgrmnt) Then '<--If the textbox txtAgrmnt contains no data THEN do nothing
strWhere = strWhere & " (dbo.Area.Agreement_Number) Like '*" & Me.txtAgrmnt & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCounty) Then
strWhere = strWhere & " (dbo.Area.County) Like '*" & Me.txtCounty & "*' AND"
End If

If Not IsNull(Me.txtMeridian) Then
strWhere = strWhere & " (dbo.Area.Meridian) Like '*" & Me.txtMeridian & "*' AND"
End If

If Not IsNull(Me.txtState) Then
strWhere = strWhere & " (dbo.Area.State) Like '*" & Me.txtState & "*' AND"
End If

If Not IsNull(Me.txtTownship) Then
strWhere = strWhere & " (dbo.Area.Township) Like '*" & Me.txtTownship & "*' AND"
End If

If Not IsNull(Me.txtTownship_Dir) Then
strWhere = strWhere & " (dbo.Area.Township_Dir) Like '*" & Me.txtTownship_Dir & "*' AND"
End If

If Not IsNull(Me.txtRange) Then
strWhere = strWhere & " (dbo.Area.Range) Like '*" & Me.txtRange & "*' AND"
End If

If Not IsNull(Me.txtRange_Dir) Then
strWhere = strWhere & " (dbo.Area.Range_Dir) Like '*" & Me.txtRange_Dir & "*' AND"
End If

If Not IsNull(Me.txtSection) Then
strWhere = strWhere & " (dbo.Area.Section) Like '*" & Me.txtSection & "*' AND"
End If

If Not IsNull(Me.txtAbstract) Then
strWhere = strWhere & " (dbo.Area.[Abstract (Texas)]) Like '*" & Me.txtAbstract & "*' AND"
End If

If Not IsNull(Me.txtSurvey) Then
strWhere = strWhere & " (dbo.Area.[Survey (Texas)]) Like '*" & Me.txtSurvey & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstAreaInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub



Private Sub lstAreaInfo_DblClick(Cancel As Integer)
'Open frmArea based on the ID from lstAreaInfo listbox

DoCmd.OpenForm "frmArea", , , "[Recno_Area] = " & Me.lstAreaInfo, , acDialog

End Sub

The origin of this code was based on jfgambit's database search routine

http://www.access-programmers.co.uk/forums/showthread.php?t=62853

 

[cmmrfrds]

I am not sure what the question is, but the wildcard character for ANSI SQL is % not the * as in Access.

 

[Massinova]

Good grief, such a simple overlooked solution (Red-faced)

but at least I got it working now ...Thanks