Access form design

[theolddog]

Hello. I have an Access 2000 problem.

I have successfully imported a database from SideKick into Access and all 750 records appear to have been imported correctly. I have set up a form using the wizard and it works fine as well. Now I would like to refine it by making one of the fields a combo box with a list of defined values to choose from. So...I created a separate table with the values I wanted, matching the values that already exist in the main table for that field. However, now when I open the form, every record gives me a prompt that 'the value you entered isn't valid for this field' even though the word that appears is one of the choices listed. Clearly I have done something wrong but I can't get my mind around what it is.

Any help would be appreciated.

The field in the main table is called Category and there are three categories to choose from: Classical, Popular or Contemporary.

Each existing record has one of those choices already.

I want to do this so that when future records are added, the choice is limited to the options in the new table.

Thanks for your help.

theolddog

 

[VicRauch]

How did you set up the new table with the three choices? Does this new table have one or two fields in it? If it only has one field, then my guess is that all three of your categories are not spelled EXACTLY the same way the three categories are spelled in your main table. The easiest way to help here is if you could post the database so I could look at the data in the table and just how you have configured this combo box.

 

[theolddog]

The new table has two fields. ID and Category. There are actually eight categories, I just choose three as an example. I believe each category in the table is spelled the same as in the main table but I will check that.

I'm not sure what you mean by posting the database but I would be happy to do that if you are willing to look at it.

thanks for the help

theolddog

 

[theolddog]

Okay, I think i just realized a problem. In my table each category has an ID set to it 1 - 8. Of course in the main table while the word may be the same as the description in the category table, it doesn't have the ID number with it. I may be wrong, but could that be the problem and if so how do I correct it?

theolddog

 

[VicRauch]

The two fields in the new Category table explain the problem. The main table has the actual words (Classical, Popular or Contemporary), but with the new Category table having an ID field, which is more than likely an autonumber field, then you have asked the combo box to match this ID number to the existing actual words in the main table. All that said to now say you have two choices. Either don't use the ID field in the Category table, or change all you existing data in the Category field of the main table to be the ID number that matches the Category name.

 

[VicRauch]

Yes, I was just about done when I got the little (You've got mail) beep. If you don't understand what I said about fixing the problem, write back and we will go through the fix in better detail.

 

[theolddog]

I created the Category table again, this time without a primary key, just the Category description. Now when I create the combo box, everything shows up okay and the choices are all there, except that when you enter a new record, the field will accept anthing you add to it, the choice is not restricted to the descriptive words in the combo box. So this kind of defeats the purpose of what I'm trying to accomplish. I don't want someone to be able to type 'clasical' instead of 'classical'. So....any suggestions?

thanks

theolddog

 

[VicRauch]

Yes, in the properties for the combo box, you need to say "Yes" on the line, under the "Data" tab that is titled: "Limit to list".

 

[theolddog]

Okay! It works and the problem is solved. Thanks so much for your help.


theolddog