firoz.raj -3 Posting Pro in Training

Right know i want when user click on requisition no from list box1.corresponding records product name,quantity,unit should come in another listbox of qform.frm. can anyone tell me.how should i do it .when user click on listbox1 other detail should come in listbox2.here is the code.but i additional want when user click on make
quotaiton same listbox2 data should come in another listbox1 of another form.

Private Sub List1_Click()
 Dim testvalue() As String ' we need an array for splitting the rows into different columns
 Dim tmpRS As ADODB.Recordset
 Dim strReq As String
 Dim sqlQuery As String
 Dim strProduct As String, strQty As String, strUnit As String

 If List1.ListIndex >= 0 Then
       'The correct way to get the selected item is
       strReq = List1.List(List1.ListIndex) ' now we have the full row, items are separated with vbTab
       ' lets split them into differebt strings each columns vallue now as one item in the string array
       testvalue = Split(strReq, vbTab)
       'the very first item in your list is the requ_no
       strlistrequest = testvalue(0)
       Set tmpRS = New ADODB.Recordset
     '  strSql = "SELECT  m.productname,m.unit,e.quantity FROM (MaterialRequisitionOrder AS p INNER JOIN MaterialRequisitionDetail AS e ON p.requisition_no = e.requisition_no) INNER JOIN Materials AS m ON e.item_code = m.item_code where p.requisition_no=" & strlistrequest
       strSql = "SELECT m.Productname, m.unit, e.quantity FROM Materials AS m INNER JOIN (MaterialRequisitionOrder AS p INNER JOIN MaterialRequisitionDetail AS e ON p.requisition_no = e.requisition_no) ON (m.item_type = e.item_type) AND (m.item_code = e.item_code) WHERE p.requisition_no= " & strlistrequest

'      sqlQuery = "SELECT ProductName, qty, unit FROM [mrtemp] WHERE req_no=" & strlistrequest
'      StrSql = "SELECT " & FieldName & "," & FieldName & "ID FROM " & TableName & " WHERE " & FieldName & " Like '" & strValue & "'"
'      sqlQuery = "SELECT " & productname & "," & Qty & "," & unit & " from " & mrtemp & "where " & req_no & " & strlistrequest"
       Debug.Print strSql
       tmpRS.Open _
       strSql, con, adOpenDynamic, adLockOptimistic
       If tmpRS.BOF And tmpRS.EOF Then
         MsgBox "Record not found"
         Exit Sub
       End If
      'Load the records to ListBox 2
       List2.Clear
       tmpRS.MoveFirst
       Do While (Not (tmpRS.EOF))
          'Read the Data
          strProduct = tmpRS!Productname & ""
          strQty = tmpRS!Quantity & ""
          strUnit = tmpRS!Unit & ""
          'Add to List box
          List2.AddItem strProduct & vbTab & strQty & "   " & strUnit
         'Move to Next record
          tmpRS.MoveNext
       Loop
       End If
       List2.FontSize = 10
       List2.FontBold = True
       List2.FontName = "Arial"
       End Sub
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.