Base recordsource on values in list

[djeeten]

Hi,

I have these 2 forms frmProducts & frmSearchProducts. By clicking a button on frmProducts, frmSearchProducts is opened.

On the form frmProducts the user can navigate through all the records of the table tblProducts(all the fields are on the form). On the form frmSearchProducts, there is a textbox for every field in the table tblProducts too(but here the textboxes are not based on the fields in that table).

Now, on the form frmSearchProducts, there's also a listbox and a command button 'Search'. If 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.

What I would like now, is that when I for example double click on a product in the list on the form 'frmSearchProducts', I will return to the form frmProducts showing the choosen product. So, I actually want the record source of the form to be based on the rowsource of the list.


Thanks in advance for any help,

dj.

 

[WildHare]

So, I actually want the record source of the form to be based on the rowsource of the list.

Well, actually, you want the PRODUCTS form to be FILTERED on the value chosen in the list box from the other form. Simple enough. In the AFTER UPDATE event of the list box, use this code:

forms!frmProducts.Filter = "ProductName = '" & me!listBoxName & "'"
Forms!frmProducts.FilterOn = TRUE
Forms!frmProducts.SetFocus

This will FILTER the recordsource of the Products form based on the choice you make in the list box.

Jim





Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[djeeten]

Thanks Jim,

this works too, though :

Forms!frmProducts.Form.RecordSource = "SELECT * FROM " & _
" tblProducts WHERE ProductId=" & lstResultSet

'lstResultSet contains the productid of the selected product in the list



Thanks again,


dj.

 

[djeeten]

Hi,

I'm experiencing another problem now:

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
----------------------------------------------------------

This 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.


Thanks a lot in advance for any help,

dj.