473,327 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Populate combo box with only unique values

Hello,

I am trying to populate a combo box with only unique values. Currently I am using an access database and VB6 Enterprise. The program populates the combo box fine however, there are several repeated values in this column. What i would like to do is just show one instance of that particular record.

For example:

if the column has the values: Accounts Payable, Process Control, Shipping, Accounts Payable

i would like the combo box just to list: Accounts Payable, Process Control, Shipping


I have tried several attempts and comparing each record set as they are listed, but that seems to throw me into an infinite loop and the program will crash. Eventually i would like it to work as follows, however if there is another way I am open for suggestions.

While Not rs.EOF
if rs!Column_Name <> "" Then
strData = rs!Column_Name (strData is a string)
Call CheckRepeat (will return a boolean Unique)
if Unique = True Then
Combo1.Additem (strData)
end if
rs.MoveNext
end if
wend

Thanks!!
Nov 2 '06 #1
5 11652
sashi
1,754 Expert 1GB
Hi there,

Use the DISTINCT keyword, all you need is a little SQL knowledge. Take a look at below sample code segment. Good luck & take care.

Expand|Select|Wrap|Line Numbers
  1.   Select DISTINCT fldName from tblName
  2.  
Hello,

I am trying to populate a combo box with only unique values. Currently I am using an access database and VB6 Enterprise. The program populates the combo box fine however, there are several repeated values in this column. What i would like to do is just show one instance of that particular record.

For example:

if the column has the values: Accounts Payable, Process Control, Shipping, Accounts Payable

i would like the combo box just to list: Accounts Payable, Process Control, Shipping


I have tried several attempts and comparing each record set as they are listed, but that seems to throw me into an infinite loop and the program will crash. Eventually i would like it to work as follows, however if there is another way I am open for suggestions.

While Not rs.EOF
if rs!Column_Name <> "" Then
strData = rs!Column_Name (strData is a string)
Call CheckRepeat (will return a boolean Unique)
if Unique = True Then
Combo1.Additem (strData)
end if
rs.MoveNext
end if
wend

Thanks!!
Nov 2 '06 #2
albertw
267 100+
hi
or use 'plain' VB

put

Expand|Select|Wrap|Line Numbers
  1. Public Const CB_FINDSTRING = &H14C
  2. Public Const CB_FINDSTRINGEXACT = &H158
  3.  
  4. Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
  5.  
in your general declaration

and use

Expand|Select|Wrap|Line Numbers
  1. Ret = SendMessage(cboComboBox.hwnd, CB_FINDSTRINGEXACT, -1, strCompare)
  2. if Ret=-1 then cboComboBox.AddItem strCompare
  3.  
where Ret is your ComboBox listindex
and strCompare is the string your are looking for in your ComboBox.

if you get an error-message regarding the called API
change to lParam As String
Nov 2 '06 #3
Killer42
8,435 Expert 8TB
... if you get an error-message regarding the called API change to lParam As String
It's been quite a while, but don't you need to define string parms for API calls as ByVal to make VB pass them as null-terminated format?
Nov 2 '06 #4
willakawill
1,646 1GB
Interesting couple of answers.

Sashi gets it handled before it happens while the other is an extremely involved fix after the fact. I would go with DISTINCT every time :)
Nov 2 '06 #5
Killer42
8,435 Expert 8TB
Interesting couple of answers.
Sashi gets it handled before it happens while the other is an extremely involved fix after the fact. I would go with DISTINCT every time :)
Depends where the info is coming from, of course.
If you can source just the unique values in the first place, that is obviously preferable to filtering them as you populate the control, however the filtering is done.
Nov 2 '06 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Sean | last post by:
Greetings all, I am attempting to make a form that will filter through several tables that (I believe) have refretial integrity. I am pulling data from several tables into the form and i would...
4
by: godber | last post by:
I need to populate text boxes for instance with employee information using their unique employee works number selected from a combo box. Can anyone help, I am told thru visual basic this can be...
1
by: Cindi | last post by:
Hi, Another newbie with a question that I hope someone can point me in the right direction. The goal is to populate a text box with data according to the selection in a combo box while still...
11
by: DSR | last post by:
Help Please... I would like to populate a combo box on a form with a query that compares data from two tables. Any record that is unique in table1 should continue to populate my combobox. The...
1
by: freekedoutfish | last post by:
Hi. New member here Im sat at work, pounding my head off the desk because this tiny bit of simple code refuses to work. The sub is intended to pull data from the "companyname" column in the...
11
by: martin DH | last post by:
I have an Access database of employees. Each employee has a unique identification number called "BANNER_ID". Each employee's last name and first name are in the database but are not unique...
2
by: CrazyAL | last post by:
I am working on a form to add entries into my assets table. I am trying to figure out how to make a purchase price field auto populate after entering model number into a combo box. There maybe a...
4
by: whamo | last post by:
I have the need to populate a field based on the selection in a combo box. Starting out simple. (2) tables tbl_OSE_Info and tbl_Input; tbl_OSE_Info has three fields: Key, OSE_Name and OSE_Wt...
2
by: Ronald | last post by:
I hope somebody can help. I can't get into the specifics of my project, but I'll try to create a simple example: tblVehicle * VIN (text box) * Make (text box) * Model (text box) frmRepair
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.