Unique values in combobox list

[richardm55]

I have duplicate items in combobox list. There are 2 tables: Articles and Magazines. Articles table has fields: ID, ArticleName, ArticleType, Description, FirstPage, LastPage and foreign key MagazineID


Magazine table has fields: MagazineID, MagazineName, MagazineYear, MagazineMonth.
I created the form based on Article table with some combo box controls associated with some fields in Magazine table. The combo box for example MagazineName displays list of duplicate values(because MagazineID has unique values only) and I am looking for solution how to display only unique values by skipping those repeated ones.

[June7]

Note that I split your post to its own thread. Thread hijacking is discouraged. Start your own thread and include link to any other thread you think is relevant.

Why are there duplicate magazine names in combobox RowSource? You have a record for every copy of a magazine? If you pull only unique names from Magazines, the MagazineID field will not be available for saving into Articles table.

[richardm55]

Because every records of Magazine table has such fields, MagazineID, MagazineName, MagazineYear and MagazineMonth so MagazineName can have many copies with different year and month.
I dont want change records in Magazine table, only display them in different way in combo box control.

[richardm55]

MagazineID	MagazineName	MagazineYear	MagazineMonth
1	Electronics	2015	January
2	Electronics	2015	February
3	Electronics	2015	March

So you can see that fields MagazineName and MagazineYear have the same values although ID is unique for each record and those records together are also unique but I display all those 3 fields in different combo controls so some of them display repeated values.

[June7]

Year combobox:
SELECT DISTINCT MagazineYear FROM Magazines;

Month combobox:
SELECT DISTINCT MagazineMonth FROM Magazines;

Magazine combobox:
SELECT DISTINCT MagazineName FROM Magazines;

So what are you really trying to do - cascading comboboxes?

[richardm55]

Year combobox:
SELECT DISTINCT MagazineYear FROM Magazines;

Month combobox:
SELECT DISTINCT MagazineMonth FROM Magazines;

Magazine combobox:
SELECT DISTINCT MagazineName FROM Magazines;

So what are you really trying to do - cascading comboboxes?

I've tried it already and those did not work.

[June7]

What does 'did not work' mean - error message, wrong results, nothing happens?

The suggested syntax is correct and I use DISTINCT in my combobox RowSource SQL statements.

Again, what are you really trying to accomplish - cascading comboboxes?

[richardm55]

I added DISTINCT after SELECT and nothing happened after using it. Still combo box listed the same values including duplicated ones.
You keep asking me what I want to achieve but I explained everything clearly in my earlier posts. I would like to display unique values in combo box controls which has RowSource from 2 fields of Magazine table, when one containing ID is hidden by combo formatting. And this field contains repeating values so it also shown the same in combo box control.
So I wanted to use special trick to take only unique values to list them in combo box.
But I see this is to difficult to realize that in easy way so I think I am going to do this in another way. I just split Magazines table into 3 separate tables into associate them with main table Article and in this way all 3 table will surely have unique values.

[June7]

Post your attempted SQL statements. If you include the ID field, then the descriptive values cannot be unique in list.

The DISTINCT keyword works for me as shown in my post.

If you want to provide your db for analysis, follow instructions at bottom of my post.

3 separate lookup tables was suggested in your other threads https://www.accessforums.net/forms/p...rol-53484.html.

What you need to clarify is if the value you want to save is the ID for each individual copy of a magazine, then the combobox RowSource MUST list all records which means the magazine name will be duplicated because it is the ID that makes each item unique.

[richardm55]

I drop all of those I said before and I combine all fields into just one table but I have another question; I would like to set default value in combo box control and I wanted to use such expression:

= Choose(Month(Now()),"Styczeń","Luty","Marzec","Kwiecień","Maj","Czerwiec","Lipiec","Sierpień","Wrzesień","Październik","Listopad","Grudzień")

and I get error message that there is error somewhere and I cannot find where.

[June7]

What is the exact error message?

I don't see anything wrong with syntax.

You are in UK but developing db in non-English version?

Will Format(Date(), "mmmm") return month name in that language?

[richardm55]

I got typical error message: Syntax is incorrect.........

[orange]

I just ran this with no error

? Choose(Month(Now()),"Styczen","Luty","Marzec","Kwiecien","Maj","Czerwiec","Lipiec","Sierpien","Wrzesien","Pazdziernik","Listopad","Grudzien")
Lipiec   was returned

Perhaps the error is somewhere else????

[June7]

Syntax works perfect for me.

Maybe something about the language alphabet??? Those letters with the accent mark (or whatever it's called) might be a problem?

[richardm55]

I checked with different strings elements and I always get the same error. When I run just Month(Now()) I get month as number but when I want to use function Choose I get error message. I don't think it is about alphabet because you tested it and it worked fine despite you used English alphabet.
I remember I tested it some time ago and it worked fine.