Combo Box Problem in a Subform

[maura]

Here are my tables, and I am trying to create a combo box to look up the
values in tblStatusType... it's not working.

tblCustomer
custId (pk)
cfirst
clast
caddress
ccity
cstate
czip
cphone
ccell
cemail

tblCollection
collectionId (pk)
OpenDate
Notes
custId(fk)

tblCollectionStatus
collectionId (pk)
statusId (pk)
respondDate

tblStatusType
statusId (pk)
statusType

I have created a qryCollections (tblCollection; tblCollectionStatus;
tblStatusType) and I am basing a subform on the qryCollections. But I need a
Combo Box for the status type, so my users are only allowed to choose from
this list.

I don’t know if I have set up my tables wrong, but I was thinking that if I
created a table for StatusTypes then I could just add to this if I need to…
but my combo box will not work on the subform and I know it has to be bound,
but maybe I am not clear on where it needs to be bound… would it be back at
the tblStatusType or right to the qryCollections. Everytime I have tried
something different, it changes the information within the table itself.

What am I doing wrong?

 

[scubadiver]

Should statusID be a FK in the 'collections status' table? You have given it
a PK.

 

[maura]

Sorry about posting 2x, thought I was in one discussion group and posted to
another, then didn't think I did it right. I am at the point of rethinking
these tables. I thought if I used the combination of collectionId and
statusId for the tblCollectionStatus this together create a primary key for
the table.