getting distinct req_no is a list box

[firoz.raj]

sir i want to show only distinct row should come in list box.
any help would be greately appreciated.

Dim con As adodb.Connection
Dim rs As adodb.Recordset
Dim i As Integer
Dim strReq As String * 10
Dim strName As String * 20
Dim strJob As String * 10
Dim strDate As String * 10
Dim x As Integer
Private Sub Form_Load()
Set con = New Connection
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\asfserver\itp$\Product_tabletest.mdb")
Set rs = New adodb.Recordset
rs.Open "SELECT * from mr ", con, adOpenDynamic, adLockOptimistic
' List1.AddItem (rs!req_no & vbNullString & " " & rs!emp_name & " " & rs!job_no & " " & rs!mr_date)
While (Not (rs.EOF))
strReq = rs!req_no & ""
strName = rs!emp_name & ""
strJob = rs!job_no & ""
strDate = rs!mr_date & ""
List1.Font = "Courier New"
List1.AddItem strReq & " " & strName & " " & strJob & " " & strDate
rs.MoveNext
Wend
End Sub

list.zip

[ryan311]

rs.Open "SELECT Distinct * from mr ", con, adOpenDynamic, adLockOptimistic

[firoz.raj]

i have tried but all the records is comming in a
list box1

rs.Open "SELECT distinct * from mr ", con, adOpenDynamic, adLockOptimistic

[ryan311]

rs.Open "SELECT distinct(*) from mr ", con, adOpenDynamic, adLockOptimistic

[debasisdas]

what do you mean by only distinct row .

i believe that means all the fields in a row are same.

distinct will definately filter that out.

[firoz.raj]

i got syntax error.

Private Sub Form_Load()
  Set con = New Connection
 con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
 Set rs = New adodb.Recordset
'rs.Open "SELECT distinct req_no,emp_name,job_no,mr_date from mr  ", con, adOpenDynamic, adLockOptimistic
 [B]rs.Open "SELECT distinct(*) from mr ", con, adOpenDynamic, adLockOptimistic[/B]
' List1.AddItem (rs!req_no & vbNullString & "         " & rs!emp_name & "    " & rs!job_no & "  " & rs!mr_date)
 While (Not (rs.EOF))
 strReq = rs!req_no & ""
 strName = rs!emp_name & ""
 strJob = rs!job_no & ""
 strDate = rs!mr_date & ""
 List1.Font = "Courier New"
 List1.AddItem strReq & " " & strName & " " & strJob & " " & strDate
 rs.MoveNext
 Wend
 End Sub

[ryan311]

try this

"Select DISTINCT req_no, emp_name, job_no, mr_date from NAME OF THE TABLE"

[firoz.raj]

MRPOST.zip

i have already written distinct row not working my friend.

Private Sub Form_Load()
Set con = New Connection
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\asfserver\itp$\Product_tabletest.mdb")
Set rs = New adodb.Recordset
rs.Open "SELECT distinct req_no,emp_name,job_no,mr_date from mr ", con, adOpenDynamic, adLockOptimistic
' List1.AddItem (rs!req_no & vbNullString & " " & rs!emp_name & " " & rs!job_no & " " & rs!mr_date)
While (Not (rs.EOF))
strReq = rs!req_no & ""
strName = rs!emp_name & ""
strJob = rs!job_no & ""
strDate = rs!mr_date & ""
List1.Font = "Courier New"
List1.AddItem strReq & " " & strName & " " & strJob & " " & strDate
rs.MoveNext
Wend
End Sub
Private Sub List1_Click()
rs.MoveFirst
MsgBox (List1.ListIndex)
'rs.Find "req_no= '" & STRREC & "' "
If List1.ListIndex >= 0 Then
If (rs.BOF = True) Or (rs.EOF = True) Then
MsgBox "Record not found"
Else
List2.AddItem rs.Fields("productname") & vbNullString
List2.AddItem rs.Fields("qty") & vbNullString
List2.AddItem rs.Fields("unit") & vbNullString
End If
End If
End Sub