Access subform does not save to join table 1

[bethae3]

I have several "list" tables and "join" tables in the newest db I'm creating. I want to create a subform for the employee status, date the status was upgraded, etc. that uses a combo box to let the user select the 'position type', and saves all of the input data in a 'join table' (automatically grabbing the employee ID # from the main employee data form). This has been giving me migraines for days, and I know it's not that hard. I've got some kind of a block - can anyone give me a suggestion?

 

[formerTexan]

Hi Bethae3,

First, if the Employee ID # is s social security number or some sort of company assigned ID, then don't use it as a key field for maintaining data relationships between tables. Instead use some unique, non-data related key. There are plenty of postings here and elsewhere discussing this at length.

So your Join table for employee status should have the following 4 fields at the minimum.

1. A primary key field (autonumber or self generated).
2. A foreign key field (for the link to the employee table. THis will be the field that links the subform to the main form (child/master).)
3. A field for the employee status.
4. A field to track the date the record was added (set the default value on this field to =Date() either at the table or the form level)

Then all four fields are placed on your subform. The first two fields can be invisible. Make sure the child/master links have indeed been set on the subform. The date will be added automatically as will the employee link. The only other thing you need is a combo box to lookup the employee status.

Cheers,
Bill

 

[bethae3]

The primary keys for each table are all autonumbers, so that shouldn't be an issue.

The statusType table has two fields - (pk)statusID and statusType.
The memberStatus table has the pk(mStatusID), fk (statusID), fk(memberID), and msYear (status is 'union determined' and changes Jan 1 of whatever year).
I think all relationships are set properly - no many to many's. When I set up the subform, I included the statusType table fields, the memberStatus table fields, and the member table memberID field. What I get is a mess - nothing ends up storing or showing where it should.



Bethae3
The phrase "working mother" is redundant.

 

[formerTexan]

Hi Bethae3,

I made the assumption that you are using "join" tables in a many-to-many relationship. If you don't have any many-to-many relationships, then in what capacity are you using join tables?

Also you might want to post the SQL string from the subform's recordsource.

Cheers,
Bill

 

[bethae3]

Thanks for the help - this was one of those times when I knew the answer, but coudn't remember it. I finally figured out what was wrong - I left the combo boxes unbound.


Bethae3

Bethae3
The phrase "working mother" is redundant.