[RESOLVED] VB6 & Access: Fill Combobox With Fields.item, All Unique

Hi,

I'm trying to make a Search Form for different games.
I have 2 comboboxes, combo1 filled with the Field Names to select a field to search in. This works!

Now i wan't the second combobox to fill with all the items in that field, BUT:
If there are more then 1 equal items in the field, it should only enter it once.

example:
ID_____Type_____Group_____Name__________Extra
1______Computer_Racing____Need For Speed__Most Wanted :D
2______Computer_Sports____Fifa10__________Football
3______Board____Cards_____Patience________Cardgame
4______Computer_Racing____Need For Speed__Undercover
etc.

I allready have the fieldnames in combo1, but i want combo2 to be: (If combo1 = 'Type')
Computer (1x)
Board (1x)
etc.

Thanks in advance!

Assuming you are using a Select statement, simply add the keyword DISTINCT just after the Select.

hi,

Thanks for the reply. combo1 = "Select case", but no select (SQL) statement . I found a way to get around my problem, so don't need an answer anymore.

Should i set thread as resolved (it isn't really solved) or leave it like this, for people who await the answer?

thanks and cheerz!

If you don't want people to attempt to help you with the issue any more, then marking the thread as Resolved is the right thing to do.

If you do want help with it, we'll need more details about how you are loading the data and filling the combo.

Hi,
For Filling Combi1 i use:

Code:

---

Private Sub Form_Load()
Set cn...
Set rs...
With rs
  .Open "Games", cn, adOpenKeyset, adLockPessimistic, adCmdTable
  Dim FC as Integer    'FC = FieldCount
  For FC = 0 To rs.Fields.Count -1
      Combo1.AddItem rs.Fields (FC) . Name
  Next
End With

---

It Fills up with: ID____Type____Group_____Name______Extra (all fieldnames)

Code for Filling Combo2:

Code:

---

Private Sub Combo1_Click()
Select Case Combo1
  Case  "Type"
      Do
      Combo2.AddItem rs.Fields.Item("Type")    'Error 3021 (see End of Code)
      rs.MoveNext
      Loop
  Case else  'For now, just to test
      Combo2.Clear
End Select
End Sub

'Run-Time Error '3021': " BOF or EOF is True, Or the current record had been deleted. There needs to be a current record to apply the prefered handling. "

---

In Run-Time:
When selecting ID: no problem, because ID cells are all unique allready
Selecting Type: It should show all Items in field "Type"
All others: same as Type

I know I should work with .BOF and .EOF somewhere, but i don't know where.
Can you tell me what the problem is?

Thanks!

Ok, well believe it or not you are actually using an SQL statement - you are just getting it written for you (which tends to make it slower), rather than having it directly in the code.

This line:

Code:

---

  .Open "Games", cn, adOpenKeyset, adLockPessimistic, adCmdTable

---

...gives the same result as this:

Code:

---

  .Open "SELECT * FROM Games", cn, adOpenKeyset, adLockPessimistic, adCmdText

---

While you could write code to eliminate the duplicates yourself, it is simpler (and will probably run quicker) to get the database to do the work for you - by using an SQL statement like "SELECT DISTINCT [" & Combo1.Text & "] FROM Games"

In order to do that tho you should use a different recordset, and open/use/close it in Combo1_Click. You can see an example of the code in the article How can I fill a combobox with values in a database? from our Database Development FAQs/Tutorials (at the top of this forum)

Ok, didn't know that :D Here's another 'Clue':

My Comboboxes should act like the WHERE Clause in SQL.
Combo1 is just for the columns of the table (search in "Type")
::Show All Types (all once!)::
Combo2 is to narrow the search down (to 1 Type, say "Computer")
::Show All Type.Items WHERE Type = "Computer"::
Combo3 is to select a Group (narrow search down to Group "Racing")
::Show All Group.Items WHERE Group = "Racing"::
etc.

If i use DISTINCT (in code or in SQL btw?), would ALL my data from field ("Group"), next to field ("Type") still be intact?
Also: If I insert .EOF (after DO 'while not') this message shows up:
Compile Error:
Invalid or Unqualified reference
What to do?

Hope it all makes sense;)

As you are going to do it more than once, I would recommend adding the FillCombo routine from the FAQ to your project.

Your code for Combo1_Click could then just be this:

Code:

---

Private Sub Combo1_Click()
    Call FillCombo(Combo2, cn, "SELECT DISTINCT [" & Combo1.Text & "] FROM Games", Combo1.Text)
End Sub

---

Quote:

---

Originally Posted by JWJWJW

If i use DISTINCT (in code or in SQL btw?), would ALL my data from field ("Group"), next to field ("Type") still be intact?

---

I'm not sure what you mean by that... if you are worried that it is going to delete things from your database, it isn't.

Quote:

---

Also: If I insert .EOF (after DO 'while not') this message shows up:
Compile Error:
Invalid or Unqualified reference
What to do?

---

You can only start something with a . if it is between a "With" and "End With". Otherwise you need to type the object name first.

Great! It works now.

thanks!

Thread Solved!