VBA Search

[k209310]

I have a line of code in vba which searches a database and collects a information. The info that it collects is then put in to a combo box.

Is there anyway of getting the combo box to display only unique values (ie not repeat data)

the query in the code is currently
("SELECT * FROM tblESTIMATE WHERE tblEstimate.IWO")

i have tried using DISTINCT after the select part but this doesnt seem to work correctly. It displays the numbers of the unique rows of data in the Db table not the unique values held in the collumn IWO.

 

[vandewi]

"SELECT [tblESTIMATE ].[YourFieldName] FROM [tblESTIMATE ] GROUP BY [tblESTIMATE ].[YourFieldName] ORDER BY [tblESTIMATE ].[YourFieldName]"


This will give you your unique values in ascending order.

 

[k209310]

do i need to do anything with the code?

Do the brackets need to be there?

or should i use the code as i was before ie: ("SLELECT * From tblEstimate WHERE IWO")

 

[vandewi]

Just use the code exactly as it appears (including brackets). The only thing you need to change is "YourFieldName". I could not tell what your field name was from your post, so you will have to fill it in where ever your see "YourFieldName".

 

[k209310]

i have this line of code but am getting a syntax error

Set rst = Db.OpenRecordset "SELECT [tblESTIMATE].[IWO] FROM [tblESTIMATE ] GROUP BY [tblESTIMATE].[IWO] ORDER BY [tblESTIMATE ].[IWO]"

it is exactly what you said as far as i can see. am using this in the right way?

If i put () in the line then i get an erro telling me the item is not found in the collection

 

[vandewi]

Why would you be putting this code into a recordset? If you want to populate a combo box, just enter the code into the record source property of the combo box.

 

[k209310]

I thought i needed to put the code in to the recordset to get the data from the database.

How else would i populate a combo box using data from a database?

 

[Rich]

Drag the combo box off the toolbox, the wizard will build it for you

 

[k209310]

could you explain this in more detail please. I didnt quite get what you meant.

 

[Rich]

Sorry I didn't read back far enough, try
SELECT DISTINCTROW ;