Probably a simple combobox question..

[thatguy]

Hey there folks--

I'm just starting out in Access (after spending several years in FoxPro) and I'm having trouble with a (theoretically) simple combobox problem.

I have two tables (main and category), the relations go like this:

main category
pkey |--> pkey
ncategory --| ccatdesc

(main.ncategory relates to ncategory.pkey). I would like a combobox that displays category.ccatdesc (category description text) corresponding to the category in main.ncategory, but has a list of choices containing all records in category.

I Know this is simple (at least it should be), but the way I have it set up now, Access beeps at me when I try to select a different category (says I can't edit the control; it's is bound to [ncategory].) Any help would be appreciated!

Thanks
-- michael~

 

[bsupport]

Thatguy,
could you be a little more specific? if I read you right, you have:

Table 1: Main Table 2: Category
Field 1: Main.Primary Key Field 1: Category.Primary Key
Field 2: Main.Category Field 2: Category.Description

You want:

If Category.Primary Key matches Main.Primary Key, then
List all Category.Descriptions where a match is found.

Your data looks something like this:
Main.Primary Key = 0246, 0300, 0450
Main.Category = Cats, Dogs, Primates

Category.Primary Key = 0246, 0300, 0450
Category.Description = Siamese, Beagle, Chimpanzee
Category.Description = Cheshire, Dashund, Gorilla
Category.Description = Tabby, Terrier, Orangatang

So if you choose: Category.Primary Key = 0450
You get: Category.Description = Chimpanzee, Gorilla and Orangatang.

Is this what you want? If so, then Category.Primary Key is not really a primary key, it is a foreign key and is set-up as a Look-up Text field that points to Main.Category in the Main table. This way when you add records to the Category table, all you have to do is select a Category.Category from the drop-down list (created by the Look-up) and then add a Category.Description. You can create and run a query to show all Category.Descriptions for the particular Main.Category.

I hope I didn't confuse you further. Who takes 7 seconds to develop,
7 mins to document,
7 hours to test,
7 months to fix will always blame the clock. s-)

 

[thatguy]

My apologies, I should've been more specific.

For example, in table Main I have the following records:

pkey ncategory
1 3
2 1
3 1
4 2

and in Category, I have the following records:

pkey ccatdesc
1 "Shirt"
2 "Pants"
3 "Sweater"

When I bring up the main form (Main.pkey = 1), the combobox should display "Sweater". When I click the arrow on the combobox, I want a list of all records in Category (Shirt, Pants, Sweater). When I select "Shirt" from the combobox, Main.ncategory should change to 1.

As I mentioned in the first post, I have it set up to where it makes sense to me - the combobox is bound to Main.ncategory (the field I want to update with the value of the combobox) and the rowsource is a select statement pulling ccatdesc and pkey from Category (bound column is 2), but Access won't let me change the value in the combobox and I really don't understand why!

Thanks
-- michael~