VBA Search function

k209310

Active Member
Joined
Aug 12, 2002
Messages
382
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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

Active Member
Joined
Aug 12, 2002
Messages
382
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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

Active Member
Joined
Aug 12, 2002
Messages
382

ADVERTISEMENT

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.
 

k209310

Active Member
Joined
Aug 12, 2002
Messages
382

ADVERTISEMENT

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.
 

k209310

Active Member
Joined
Aug 12, 2002
Messages
382
I stand corrected. I hadnt looked properly.
Sorry for wasting your time.

Thanks very m,uch

Chris
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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"
 

Forum statistics

Threads
1,144,291
Messages
5,723,532
Members
422,502
Latest member
barakgahtan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top