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