problem updating full record

[Delano29]

I have a form based on the Employee table listed below. The Employee table and Office table are linked as a one-to-many relationship using the Officecode field in both tables. The Employee table uses the Office table (Officename field) as a lookup. My problem is when I created the Employee form based on the Employee table and when I change the Officename field using the dropdown combo box it doesn’t change the Officecode field in the table to reflect both the change in OfficeCode and Officename. Could someone tell me what I am doing wrong or how I need to link the fields so when I change the Officename using the dropdown field Officename in the form it will change the corresponding OfficeCode? Thanks.


Employee Table:
EmId (primary key)
Officecode
Officename (lookup field in Office table)
Lastname
Fistname

Office Table:
Officecode (primary key)
Officename

Employee Form:
EmId
Officecode
Officename (dropdown combobox)
Lastname
Fisrtname

 

[SkipVought]

Don't know unless we see the UPDATE SQL code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TheAceMan1]

How are ya Delano29 . . .

From what you've shown:
[ol][li]Are the tables linked on [blue]Officecode?[/blue][/li]
[li]Is the Office table the parent and Employee Table the child?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Delano29]

Skip,

I created the form using the form wizard without using code. I thought that since the Officecode field are in both tables in a one-to-many link that if I changed the Officename of the employee it would change it to the right Officecode automatically. For instance:

Office Table:
Officecode Officename
1246 Denver
1345 Buffalo
1456 Chicago

If the employee current record had the Officecode: '1246' that corresponds to the Officename: 'Denver' and I change the Officename using the dropdown field to 'Buffalo' I want it to change it to the right code for that office which in this case would be '1345'. Would I need code to do this or could I it by linking the fields somehow?

 

[Delano29]

Hi Aceman,

The Officecode is the primary key in the Officename table and its the foreign key in the Employee table and they are linked as a one-to-many. Should it be setup that is a parent and the other child? If so, how do I go about doing that?

Thanks.

 

[TheAceMan1]

Delano29 . . .

And my prior post? . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886