Search in table

[djeeten]

Hi,

I have this form frmSearchProducts with several textboxes where the user can fill in a value.

My code works perfectly for all the fields on the form, except for the fields where the Productcode and Analytical code have to be filled in.

So, when the user enters for example the letter 'p' in the textbox 'txtProductName' and then presses the command button on the form, all the products that haven a name starting with 'p' from the table tblProducts are shown in the listbox. But when the user enters for example the analytical code '6050' all records from the table tblProducts are shown, while only those with the choosen analytical code should be in the list.

this is my code behind the form frmSearchProducts:

-------------------------------------------
Option Compare Database
Option Explicit
-------------------------------------------

Private Sub cmdSearchNow_Click()
Dim strSql As String
Dim strCriteria As String
Dim strRowsource As String

strSql = "SELECT * FROM tblProducts"
strCriteria = ""

AddArgument NameProduct, "NameProduct", strCriteria ArgumentToevoegen Price, "Price", strCriteria
......
AddArgument Productcode_Supplier_, "Productcode_Supplier_", strCriteria
AddArgument Analytical_code, "Analytical_code", strCriteria

If strCriteria <> &quot;&quot; Then
strSql = strSql & &quot; WHERE &quot;
End If

strRowsource = strSql & strCriteria
lstResultSet.RowSource = strRowsource

lstResultSet.Requery
End Sub
---------------------------------------------------------
Public Sub AddArgument(ByVal varFieldvalue As Variant, ByVal strFieldName As String, ByRef strCriteria As String)
Dim intL As Integer

If varFieldvalue <> &quot;&quot; Then
If strCriteria <> &quot;&quot; Then
strCriteria = strCriteria & &quot; And &quot;
End If
intL = Len(varFieldValue)

strCriteria = strCriteria & &quot; LEFT(&quot; & strFieldName & _
&quot;,&quot; & intL & &quot = '&quot; & varFieldvalue & &quot;'&quot;

End If
End Sub
-----------------------------------------------------------
Private Sub cmdSelect_Click()
Dim bytDummy As Byte

If IsNull(lstResultSet) Then
bytDummy = MsgBox(&quot;Select an item from the list&quot;, _
vbOKOnly + vbExclamation, &quot;Controle &quot
lstResultSet.SetFocus
Exit Sub
End If
Forms!frmProducts.Form.RecordSource = &quot;SELECT * FROM &quot; & _
&quot; tblProducts WHERE ProductId=&quot; & lstResultSet
DoCmd.Close
End Sub
-----------------------------------------------------------
Private Sub lstResultSet_DblClick(Cancel As Integer)
cmdSelect_Click
End Sub
----------------------------------------------------------



Thanks a lot in advance for any help,

dj.

 

[nath]

Hi Djeeten,

What kind of data type are your Product and analytical codes? If they are number and not text the result you get would make sense.

Nath

 

[djeeten]

Hi Nath,

thanks for trying to help me. To answer your question: No,
Product and analytical codes are textfields.

I just figured out what went wrong though. I need to use square brackets in order to make it work correctly (as Analytical code is not one word and Productcode(Supplier) has brackets in it?).

So:

AddArgument Productcode_Supplier_, &quot;[Productcode(Supplier)]&quot;, strCriteria
AddArgument Analytical_code, &quot;[Analytical code]&quot;, strCriteria

That's all I needed,

Thanks again,

dj.