Autonumbers and forms

[Dragnog]

I have a database that has a relationship between three tables called Staff StaffEscalation and Escalation.
The relationship is:
Staff StaffEscalation
StaffID(1)--------(many)StaffID

StaffEscalation Escalation
EscalID(many)---------(1)EscalID

In the Staff table I have the Staff Name and in the StaffEscalation table I have some yes/no fields.

The query for the form is: SELECT Escalation.EscalID, StaffEscalation.EscalID, StaffEscalation.StaffID FROM Escalation INNER JOIN StaffEscalation ON Escalation.EscalID = StaffEscalation.EscalID;

On a form (which I going to turn into a continuous subform) I want to be able to have the staff name and these other yes/no fields. I have made an unbound combo box which selects specific staff from staff table in column 1 of the combo box and the staff ID corresponding to that person in column 2. I have bound column 2. Both Staff ID and Escal ID are autonumbers.

On the form I have the combo box a text box bound to staff escaltaion.staffID a text box bound to staffescalation.escalID and escalation.escalID (I was using the last two text boxes for checking) When I create a new record and select someone from the list, the staff ID updates but a new escaltion ID is not created.

Why would this be happening and how could I fix this?

 

[Pat Hartman]

If you want a form to add rows to two tables at the same time, you need to base the form on a query and data fields in BOTH tables must be updated. You have not included any data fields from Escalation so that is why no record was created.

 

[Dragnog]

Thank you for the information.
On a slightly different tack, but basically to do with the same thing, as I have this combo box unbound how would I get it to show the value of the the name in the drop down box?
For example once you have selected someone from the list all of the names change to the currently selected entry on the continuous form.
I am assuming that I would need to put in some code to get this to check for the name associated with the staffID and then put it as the value for each entry in the form.
The other option (and I do not know if you can do this) would be to put an if statement into a query.

Any feedback on this would be greatly appreciated.