Limit Combo.Additem to Distinct value? 1

[rtshort]

I have the following code to fill a Combo Box (Combo1.AddItem):

sql = "Select * from SysObjects Where Name like '%" & cboEngMake.Text & "%'"
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If Not rs.EOF And Not rs.BOF Then
rs.MoveFirst
Item = rs.Fields("Name&quot.Value
'MsgBox (Item)
Model = Mid(Item, 15, 9)
With cboEngModel
Do Until rs.EOF = True
If cboEngModel.Text <> Model Then
cboEngModel.AddItem Model
rs.MoveNext
ElseIf cboEngModel.Text = Model Then
rs.MoveNext
End If
Loop
End With
End If

It still adds all of the models as many times as they appear.(The model variable comes from trimming the table name as above) How do I code it to just add an item only one time even though the recordset will return more than one distinct value. The sql &quot;Distinct&quot; keyword will not help since the tables have similar names with just the end of the table name different. Any help is greatly appreciated.
Rob
Just my $.02.

 

[jasek78]

You need to loop through all of the items in the combo box, not just the visible one.

try this code

jason

...
Dim inTemp as integer
Dim blAdd as boolean

blAdd = True

...
Do Until rs.EOF = True
For inTemp = 0 to cboEngModel.ListCount Step 1
cboEngModel.ListIndex = inTemp
If cboEngModel.Text = Model Then
blAdd = False
End if
Next inTemp
If Not blAdd Then
cboEngModel.AddItem Model
rs.MoveNext
Else
rs.MoveNext
End If
Loop...

 

[WilMead]

sql = &quot;Select DISTINCT * from ...


My SQL is rusty... Does this work?

Wil Mead
wmead@optonline.net

 

[75cl]

OR group by function in your sql

 

[rtshort]

Thanks for the help guys. Jasek78 had just what I needed. Thanks again to all of you though. Rob
Just my $.02.

 

[rtshort]

Well, I have come up with the following code:

Set rs = New ADODB.Recordset
sql = &quot;Select * from sysobjects Where Name Like '%&quot; & cboEngMake.Text & &quot;%'&quot; & &quot;And Name Like '%&quot; & cboEngModel.Text & &quot;%'&quot;
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If Not rs.EOF And Not rs.BOF Then
rs.MoveFirst
Item = rs.Fields(&quot;Name&quot.Value
HorsePower = Mid(Item, 26, 3)
AddToList = True
Do Until rs.EOF = True
For i = 0 To cboEngHorsePower.ListCount - 1
If HorsePower = cboEngHorsePower.List(i) Then
AddToList = False
Exit For
End If
Next i
If AddToList = True Then
cboEngHorsePower.AddItem HorsePower
End If
rs.MoveNext
Loop
End If
rs.Close


It works fine on the first form. After all of the Combo Boxes are filled in and the continue button is clicked I have the following code:

Private Sub cmdContinue_Click()
Load frmCategories
sql = &quot;Select * from SysObjects Where Name Like '%&quot; & cboEngType.Text & &quot;%'&quot; & &quot;And Name Like '%&quot; & cboEngMake.Text & &quot;%'&quot; & &quot;And Name Like '%&quot; & cboEngModel.Text & &quot;%'&quot; & &quot;And Name Like '%&quot; & cboEngHorsePower.Text & &quot;%'&quot; & &quot;And Name Like '%&quot; & cboEngYear.Text & &quot;%'&quot; & &quot;And name Like '%&quot; & cboEngFuelType.Text & &quot;%'&quot; & &quot;And Name Like '%&quot; & cboEngInjectionType.Text & &quot;%'&quot; & &quot;And Name Like '%&quot; & cboEngSerialNo.Text & &quot;%'&quot;
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
Item = rs.Fields(&quot;Name&quot.Value
txtEngSql.Text = Item
Main = Mid(Item, 1, 14)
frmCategories.lstMain.AddItem Main
'frmVslInfo.Hide
frmCategories.Show
rs.Close
End Sub

The txtEngSQL.text is a hidden text box. I hide the form when the continue button is cliked so I can still have the sql Query statement on the form and use it on the new form. The new form has 2 List Boxes. The first is filled with the engine name, from getting the Mid(of the query). When Double Clicked in the 1st List Box I want to fill the 2nd List Box with all of the table names that match the query. It comes back with the same name 3 times if there are that many tables instead of 3 different names as it should? Any one know what's going on or does this even make sense? Rob
Just my $.02.

 

[rtshort]

Sorry, here is the Query from the next form:

Private Sub lstMain_DblClick()
Set rs = New ADODB.Recordset
EngSQL = frmVslInfo.txtEngSql
EngQuery = Mid(EngSQL, 1, 65)
MsgBox (EngQuery)
sql = &quot;Select * from SysObjects Where Name Like '%&quot; & EngQuery & &quot;%'&quot;
MsgBox (sql)
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If Not rs.BOF = True And Not rs.EOF = True Then
rs.MoveFirst
Item = rs.Fields(&quot;Name&quot.Value
EngSubList = Mid(Item, 66, 100)
AddToSubList = True
Do Until rs.EOF = True
For i = 0 To lstSub.ListCount - 1
If EngSubList = lstSub.List(i) Then
AddToSubList = False
Exit For
End If
Next i
If AddToSubList = True Then
lstSub.AddItem EngSubList
End If
rs.MoveNext
Loop
End If
rs.Close
End Sub

The Query works fine in the SQL Query Analizer. It returns all of the tables differently. In my VB Code however, as stated above, if there are 3 different tables it returns the same name 3 times. The table names are rather lenghty, about 60 ot 75 characters, but the SQL Server 7.0 limit is 128. It should work. Am I just missing the obvious? Rob
Just my $.02.

 

[rtshort]

Thanks all. I've finally figured it out. Rob
Just my $.02.