Search Form

[Soegstad]

Hi guys!
I have a nifty little searchform which I actually found here. It works great, but I would like to trim the results it gives.

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  tblProsjekt.ProsjektNr, tblProsjekt.Selger, tblProsjekt.Kundenavn, tblKalkyle.Truck, tblProsjekt.Konsernnavn, tblProsjekt.Dato, tblKalkyle.Prisliste " & _
"FROM qryProsjektTilSøk"


strWhere = "WHERE"

strOrder = "ORDER BY tblProsjekt.ProsjektNr;"



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

If Not IsNull(Me.txtSøk) Then
strWhere = strWhere & " (qryProsjektTilSøk.Søkekriterier) Like '*" & Me.txtSøk & "*'  AND"
End If

If Not IsNull(Me.txtKunde) Then
strWhere = strWhere & " (qryProsjektTilSøk.Kundenavn) Like '*" & Me.txtKunde & "*'  AND"
End If

If Not IsNull(Me.txtTruck) Then
strWhere = strWhere & " (qryProsjektTilSøk.Truck) Like '*" & Me.txtTruck & "*'  AND"
End If

If Not IsNull(Me.txtDato) Then
strWhere = strWhere & " (qryProsjektTilSøk.Dato) Like '*" & Me.txtDato & "*'  AND"
End If

If Not IsNull(Me.txtKonsern) Then
strWhere = strWhere & " (qryProsjektTilSøk.Konsernnavn) Like '*" & Me.txtKonsern & "*'  AND"
End If

If Not IsNull(Me.txtKNummer) Then
strWhere = strWhere & " (qryProsjektTilSøk.Kundenummer) Like '*" & Me.txtKNummer & "*'  AND"
End If

If Not IsNull(Me.txtSelger) Then
strWhere = strWhere & " (qryProsjektTilSøk.Selger) Like '*" & Me.txtSelger & "*'  AND"
End If

If Not IsNull(Me.Prisliste) Then '<--If the textbox txtFName contains no data THEN do nothing
strWhere = strWhere & " (qryProsjektTilSøk.Prisliste) Like '*" & Me.Prisliste & "*'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
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.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

If lstCustInfo.ListCount - 1 > 0 Then
Me![Count] = "Søket ga " & (lstCustInfo.ListCount - 1) & " treff"
Else
Me![Count] = "Søket ga ingen treff"
End If
End Sub

As you see it queries data from two tables:
-tblProsjekt (one)
-tblKalkyle (many)
These two are combined in a one-to-many relationship by the field (prosjektnummer)

Now, what I would like to do is only show one record for each value of the field (prosjektnummer).

Any help is apprechiated.
Thanks