VBA Search function

[k209310]

I have created a form that is run from within excel (using VBA). It consists of a combo box and three labels. The data in the combo box taken from a database table and displayed.

I have two questions regarding this.

A) Is it possible to have only unique the values displayed in the combo box. (Ie values are not repeadted)

B) Is it possible to have data displayed in the labels that is taken from another collum of data stored in the same table as the data in the combo box

 

[dk]

On 2002-11-04 06:23, k209310 wrote:
I have created a form that is run from within excel (using VBA). It consists of a combo box and three labels. The data in the combo box taken from a database table and displayed.

I have two questions regarding this.

A) Is it possible to have only unique the values displayed in the combo box. (Ie values are not repeadted)

B) Is it possible to have data displayed in the labels that is taken from another collum of data stored in the same table as the data in the combo box

Hello,

What is your database? Is it a database which you are querying to fill your combobox, or is it an Excel worksheet? There isn't a property of a combobox which will display unique values only. You'd normally get round this by either using an advanced filter or by using an SQL statement with the DISTINCT keyword to eliminate duplicates. If the data source is from a worksheet then post an example of your data.

As for your second question the answer is yes. If your data source is a worksheet then you could use VLOOKUP. If not then some sort of SQL statement. Once you've replied to this, it should be easier to see how to do this.

 

[k209310]

Sorry i should have been more clear. The datatbase is an access datatbase.

I have the line of code with the query is

Set rst = Db.OpenRecordset("SELECT * FROM tblESTIMATE WHERE tblEstimate.IWO")

Adding distinct to it (after selcect) does narrow the data down a little but still doesnt display unique values. I have tried 'DISTINCT IWO' but this gives an eror message.

 

[dk]

Hi,

If you want unique values then you need to use DISTINCT and select only one field e.g.

SELECT DISTINCT EstimateDate FROM tblEstimates blah blah

Does that work for you or do you need more than one field from the database?

 

[k209310]

For ther combobox I need the unique values
when the correct number is selected in the combox i would like other information relating to that number to be displayed in the lables.

So far i have a combox that displays all of the values in the IWO collum of the database.

I have tried using "SELECT IWO * FROM tblESTIMATE WHERE tblEstimate.IWO" this doesnt seem to work correctly. I think its only giving disitnct values for rows not for the collum IWO.

 

[Andrew Poulsom]

See:

http://j-walk.com/ss/excel/tips/tip47.htm

for how to fill a combobox with unique values.

 

[k209310]

Thanks for the reply but the code is designed to work with data taken from excel not from a seperate access database.

I have all the code in place to get the data i need it just needs a little tweaking to get it right.

 

[Andrew Poulsom]

It would work - just loop around your Recordset to create the Collection.

 

[k209310]

I stand corrected. I hadnt looked properly.
Sorry for wasting your time.

Thanks very m,uch

Chris

 

[dk]

Hi,

Like I said, you need to select a single field. If you use the DISTINCT keyword along with the * character it will only return unique rows. Depending on your data this would probably return all or most of the rows from the tblEstimate table. Use something like this:-

SELECT DISTINCT IWO FROM tblEstimate

Not sure what your WHERE is supposed to be doing....normally you'd have something like:

SELECT Field1, Field2 FROM YourTable WHERE Field3="Daniel"