ugly dropdown boxes with linked information 1

[chaft]

I created a field that linked to another table (to save the user from having to type in the information everytime they could just select the correct data that appeared in the drop down box)

Problem was that where there blanks in the orginal they appeared in the drop down box...for example

this is the original table

index Animal Colour
1 fox brown
2 bear
3 panda Black and white
4 dog blonde

i'd have a drop down box created on the user form to select which Colour the animal was and this would be the result



dropdown box results :
brown

Black and White
blonde

See the gap? Is there any way that the dropdown can still gather the same information but arrange it so that I don't get these gaps? Looks really ugly.


I accept any other suggestions on how to do this by the way. I just want to be able to select from an existing list to save time typing it out.

 

[SkipVought]

Well, is [BLANK] not a valid choice?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[PHV]

Use a criteria (SQL snippet):
WHERE Trim(Colour & "") <> ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nim180]

Hi Chaft,

Try this, create a Duplication query. Create a query in design view, add your table "Animal", you'l have three columns in the query as follows: (Replace writing in blue with your database info)

column1 column2 column3
field: Colour_Field: Colour NumberOfDups:Colour Colour
Table: Your Table Your Table YourTable
Total: First Count GroupBy
Show: (Checked) (Checked) (Unchecked)
Criteria: 1 OR >1

Save your Query with any name. Now when you open the query column1 should not have a space anymore. So you can now call this field in your combobox. In the rowsource of the combobox you will have a statement like this:

SELECT qrycolor.color_field FROM qrycolor

That should do the trick.

Nim

 

[nim180]

Hi Chaft,

Try this, create a Duplication query. Create a query in design view, add your table "Animal", you'l have three columns in the query as follows:

column1 column2
field: Colour_Field: Colour NumberOfDups:Colour
Table: Your Table Your Table
Total: First Count
Show: (Checked) (Checked)
Criteria: 1 OR >1


column3
Color
Your Table
Group By
(Unchecked)

If the field total is missing right click on the mouse and make sure its checked. Save your Query with any name. Now when you open the query column1 should not have a space anymore. So you can now call this field in your combobox. In the rowsource of the combobox you will have a statement like this:

SELECT qrycolor.color_field FROM qrycolor

That should do the trick.

Nim

 

[Pampers]

what bear doesn't have a color?

Pampers
Keeping it simple can be complicated

 

[nim180]

i know my heart nearly broke when i found out that the bear was colourless

 

[chaft]

bear didn't want to be judged for his colour but for the content of his character...


thanks for your help everyone I Shall try both methods today and report success or not

 

[chaft]

The best method was the duplication method. Thanks to all that helped that supply that answer.


But how DO you guys learn this stuff? Even with google and MSDN..I still struggle.

 

[nim180]

Trust me mate i had quite an uphill battle when i first started but after reading through many threads and completing lots and lots of Access projects i got the hang of it. Alot of helpfull people on this site