Auto population of foreign key

[kattatonic1]

Once again I think that I'm looking for something so simple that Google can't help me find a similar thread. Help would be so appreciated.

I have a Position table whose primary key is the Position Number.
I have an Employee table whose primary key is the Employee Number.
The Employee table also has foreign key Active Position Number.
That is, tblPositions[pkPositionNumber] is linked to tblEmployees[fkActivePositionNumber] in a one-to-many relationship.

I have a working query that uses their relationship.
I have a working form that uses their relationship.
I can look up both employees and positions in this lovely form.

HOWEVER


When I try to use the form to create a new record, it doesn't automatically populate tblEmployees[fkActivePositionNumber].
The new record is correctly created in tblPositions.
The new record is created in tblEmployees, except for population of the foreign key.
Sooo, when I close and come back in, they are unrelated to each other of course.

What is an easy way to make this happen?

Many thanks from The Great White North!

Kay

[kattatonic1]

Additional thought: I could have both tblPositions[pkPositionNumber] and tblEmployees[fkActivePositionNumber] on the form for the user to input the number redundantly, but not only would it be redundant, but it would invite errors.

[kattatonic1]

Clue but not solved: If I do the parent / child form scenario using datasources tblPositions / tblEmployees, the foreign key is correctly added if I add a record. Is this a form type issue?

The CHALLENGE: The form I have created and wish to use mirrors a paper form the users use extensively and circulate. It would be so much easier for them if it can remain this single simple form based on the query datasource instead.

Thoughts, anyone?

Kay

[ssanfu]

Are you using a main form/sub form arrangement?

Maybe post a picture of the form??

[kattatonic1]

Hi Steve,

No, it's not a form / subform deal; just a simple form based on a query. The reason for this is that the fields from the two tables that the query is based on are interspersed on the paper form I am emulating. Roughly they're in an order similar to this:

EmpID#, Name fields (from the Emp table)
Position status fields (from the Pos table)
Location info (Pos table)
Salary info (Emp table)
Probationary info (Emp table)
Organizational info (e.g. Manager, Branch, etc. Pos table)
Home Position info in the case of secondments (Emp table)

I would like to name it "MishMash".

It works great for looking up data.

Thoughts?

Kay

[Dal Jeanis]

This is worth a shot. Make sure that both positions fields, PK and FK, are on the query. Only one of them should need to be bound on the form, since they should be bound to each other by the query.

[ssanfu]

OK, I have two possibilities.

Not knowing what the relationships look like, what the field names/types are, what the form looks like or the form record source is:

1) Use the form before update event to read the "pkPositionNumber" value and put it in "fkActivePositionNumber".

2) If the "Position" control is a combo box, use the combo box after update event to put the "pkPositionNumber" value and put it in "fkActivePositionNumber".

[kattatonic1]

Steve, your suggestions were much appreciated!

Dal Jeanis, that did the trick. Do not know why, but the fk was not on the query. Now all is well!

Do I mark the thread as SOLVED or does an administrator?

Many thanks to all the contributors here. I cannot tell you how many searches I've done and all the valuable information I've found in this forum. I am having fun getting back into database creation, and although the concepts haven't changed, the tools sure have come a long way in the last decade, and with their increased functionality, so many more details about how to make things happen!

Kay

[Dal Jeanis]

You can mark as solved - top of page, under thread tools.

The reason it wasn't in the query is that, to humans, it's redundant to have two different fields that always have the same value. On the other hand, to the Jet engine, one of those is a detail field on one table, and the other is a key field on the other table. The database needs both in order to set both.