Second subform changes to first

[sbell]

I put 2 subforms on an Access 2000 employee form. Both are linked by EmployeeID.

The first is payroll data and the second is phone numbers. When I go in and out of design mode twice the phone number subform becomes a payroll subform and then I have 2 payroll subforms and no phone numbers.

How do I overcome this?

 

[TheAceMan1]

How are ya sbell . . . . .

Need to piece this together a little. Things are a bit blurry . . .

For the [blue]MainForm & 2 subForms[/blue], what are the [purple]relationships[/purple] of the underlying respective tables?

Post the pertinent [blue]table & fieldnames![/blue]

Post The [blue]recordsource[/blue] of the forms!

See Ya! . . . . . .

 

[sbell]

Recordsources are:
Main form: tblEmployees, pk EmployeeID
Subform1: tblPayroll, pk EmployeeID-WeekEnding-Paytype
Subform2: tblPhones, pk EmployeeID-Phone

Both subforms are linked to the main form by EmployeeID.

 

[misscrf]

pk for tblpayroll should be an autonumber for payrollid
same for phones. Each table should have an fk to employeeid.
then forms and subforms should link on employeeid

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

[blue]Both subforms are linked to the main form by EmployeeID.[/blue]

Are the tables linked in the same way?

See Ya! . . . . . .

 

[misscrf]

exactly.

take a moment to look up table normalization. It will not take too long, and you won't regret it. No database will go far if it is not normalized. If you have trouble finding resources ( which you shouldn't) let me know and i will dig up some that I have used in the past.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

misscrf . . . . .
[ol][li][purple]Forms do not have to be linked according to table relationships[/purple]. Same is true for tables in a query!. Remember to link, [blue]the two fields simply require the same data type[/blue] . . . . they can even have different names. So to be sure in this case is premature, particluarly in relation to the problem.[/li]
[li]The driving force for asking if table relationships are the same, stem from the fact that [blue]shell[/blue] has posted [purple]compound primarykeys![/purple]. These types of keys always raise the complexity of the database, and you'll have more problems with them verses single.At your leisure . . . have a look at these types of keys . . .[/li][/ol]

See Ya! . . . . . .

 

[misscrf]

either way, the discussed nature of the tables is not normalized. That was my point. Sorry I tried to help.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

misscrf . . . . .

I [purple]was not trying to be/sound demeaning in any way[/purple]. [blue]Your help is always welcome[/blueee]. I was just trying to point out that table relationships are not known at this point (espcially with the compound keys), let alone if the tables are normalized!

Lets see if [blue]shell[/blue]comes back. For the moment, we do seem to be talking to ourselves . . .

See Ya! . . . . . .

 

[misscrf]

lol

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[sbell]

I recently switched to Access after working with Paradox for 12 years and I used compound primary keys a great deal. Nevertheless I will use autonumbers to get the form to behave properly.

My database is normalized. The relationship of employees to subforms/tables is 1-M.

I appreciate your input very much. Thank you!