Problem with combo control

[richardm55]

I've created simple base with 2 table related each other. Then I've built query with the fields taken from both tables and finally I've made the form with some combo controls.
There are 2 tables:

• Table articles with following fields:

• ID (main key), article
• Article name
• Article type
• First page
• Last page
• Description
• ID magazine

• Table magazines with following fields:
  • ID magazine
  • Magazine name
  • Magazine year
  • Magazine month

Both table are related through key ID magazine
I made combo controls for field:

• Magazine name
• Magazine year
• Magazine month

I've created some records for table magazines for diffirent magazine names and various years and months asigning their main key as number (not autonumber, otherwise table will created the same records for the same value of 3 fields: magazine name, magazine year and magazine month.

I have problem when I start using the form. When I select values in 3 combo controls (taken from coresponding fields from table): magazine name, magazine year and magazine month, magazine ID is not selected at all!!!!! and I dont know why.

 

[jdraw]

Which field exactly is magazine ID?

You have a field in each table named ID magazine.
Is that the issue?

For reference and consideration: You should choose a naming convention that does not allow/have embedded spaces in table and object names. Use only alphanumeric and "_" underscore characters. This will save you many syntax errors and frustration.

Cross posted at http://www.accessforums.net/forms/problem-combo-control-53484.html

A note on cross posting

 

[richardm55]

No, I have just made mistake writing about it. I should write ......ID magazine is not selected at all!!!
You reckon that naming with space is wrong? Maybe I can change it because I doubt if it will fix my problem.

 

[RainLover]

A space is not wrong it is simply not smart.

You will discover why as your experience grows. For now I suggest using CamelHump.

The first letter of each part is a cap with the balance lower case.

Personally I would use AutoNumber for IDMagazine.

JDraw is correct in what he wrote.

 

[richardm55]

Maybe it is a good practice to do that, mate and I am going to change my habits about it but what you said did not help me in any way to solve my problem which still remains unsolved.

 

[richardm55]

Jdraw, I did not know this forum and another one you dropped the link are connected in any way. I thought those forums are completely separate. So I am sorry for dropping the same posts in both forums.

 

[jdraw]

No problem.
Several people scan and participate in several forums.
It isn't that the forums are connected- it's really that you have asked different groups to help solve/work on an issue independently. People can spend a lot of time solving an issue that has already been resolved.
Bottom line is -- just tell readers that you have done so. That way, if you don't check back and indicate it has been solved, readers can go to the related link an see the status.

A lot of readers gain knowledge by reading existing posts and solutions. Posting working solutions can help others.

 

[richardm55]

I think I've found solution and my data base works properly. I did it myself because nobody was able to help me in it. I can tell you what I did wrong. When I built combo box I should tie it with Magazine ID in the table. Before I tied it with other fields like Magazine Name, Magazine Year or Magazine Month. Of course, The combo boxes take also value of those other fields but for displaying them and then selecting and saving actual Magazine ID value which is pass on into Article table.
I have still one issue to sort it out. In combo boxes in dropdown list appear the same values. Combo boxes take data for their records from table fields and I don't know how to write rules in query to list unique values only in combo boxes.

 

[RainLover]

I don't know how to write rules in query to list unique values only in combo boxes.

Create a separate Table to use as a lookup.


EDIT. If the choice is Not on the List

If the choice is No on the List when you use the Drop Box then there is some come to help add a new on. You should be able to find it with a Google Search. If not I can find some code for you.

 

[richardm55]

RainLover, I think you understood me wrongly. I did not want to edit combo box controls and insert something new which is not row sources of this control but probably I will do it later.
Now, I wanted to list unique values in combo box. This combo box has row source taken from query based on 2 fields of table, for example, MagazineID and MagazineName. In this table only MagazineID has uniques values but MagazineName has not so thats why they are listed in this way in combo box, but I would like to do something to improve it to display only unique values.

 

[RainLover]

I do believe I understood correctly.

You need a separate Table to store unique values.

 

[richardm55]

I think I will do it simpler. I just create another 2 tables for MagazineYear and MagazineMonth and tie them with main table with their IDs and each table will have unique values and then create combo box controls based on their tables. And I think it will be the simplest idea without any other complication.

 

[RainLover]

For Clarity.

What you have is a Normalisation problem.

Because MagazineYear and MagazineMonth REPEAT then any table that uses these values should be getting the value from a Table where they are listed once. This would be a One to Many Relationship. I would have an Auto Number as the primary key and use that value in any table that requires0 them.

These Lookup tables could be used by more than one other table.

I hope I have cleared up the situation for you. If not ask for more help. You could create a new Database as a demo with just these tables. Post that and I will have a look and change where required.

 

[richardm55]

RainLover, I think you did not understand it. There is no problem with relationship between 2 tables and all values are passed on properly. I had only problem with displaying those repeated values in combo box controls from table fields where are really some have the same values but I said ID is alway unique

 

[RainLover]

Make the other fields unique by giving them an index with no duplicates.

 

[richardm55]

Make the other fields unique by giving them an index with no duplicates.

Yes, RainLover, I mentioned this before that I am going to split table Magazines into 3 separate tables and associate them with main table Articles. And in this way all those 3 table will have unique values and I will build 3 combo box controls using RowSource as those 3 tables.

 

[RainLover]

What are you using as the ID. Is it Auto Number or what.

Could you please explain your relationships. There are two way of doing this and only one is correct.

What have you used.

Could you post a Pic of the Relationships or if you prefer post a cut down version of your Database.

 

[RainLover]

I've created some records for table magazines for diffirent magazine names and various years and months asigning their main key as number (not autonumber, otherwise table will created the same records for the same value of 3 fields: magazine name, magazine year and magazine month.

I do not understand why you say "otherwise table will created the same records for the same value of 3 fields:"

Can you explain your thinking here.

I have problem when I start using the form. When I select values in 3 combo controls (taken from coresponding fields from table): magazine name, magazine year and magazine month, magazine ID is not selected at all!!!!! and I dont know why.

To have the ID inserted automatically you have a Form with a Sub Form.

The Main Form holds the ID and the Sub holds the Foreign Key.

These two Forms are joined using the Parent and Child Controls. These are found in the Properties box. When you then enter something in the Sub Form the Foreign Key is Automatically populated.

 

[richardm55]

I've changed everything and I made it easier. I moved 2 fields from table Magazines (MagazineYear and MagazineMonth) into main table Articles and I left table Magazine for field MagazineName and Branch. And I redirected 2 combo box controls into right locations and I could use RowSource as list I can edit. Only combo box control remained tied with table Magazines and I left the same setting but no problems with duplicates now.
I had small issue with assigning default value for MagazineMonth combo box controls yesterday when access could accept its syntax and kept coming up with error message while syntax was completely fine. This morning I've tried it on another computer and it worked fine so I saved it and everything is all right now.
Of course, I am going to improve my simple database while I use it and fill in with data. I would like to learn some more about Access but I must find a need for it in the future. Maybe I will encounter something new where I could use Access database.

 

[RainLover]

If it works and you are happy then that is good.

If you don't want to post a copy then that is fine.

Maybe latter if you run into problems.

For now it is good luck with your project.

Bye.