Problem with Form/Subform - adding a field in the subform

[djmurphy58]

I'm having a problem with my form subform. My form is for employees, and the subform is for their wages as well as the dates those wages are valid. For example, Employee Joe Smith might have 2 entries in his subform: The first entry says he earns $25/hour between 6/1/02 and 5/31/03. The next entry says he earns $26/hr between 6/1/03 and 5/31/04. Now, when I add a 3rd entry, I just now noticed that I have 3 entries for Joe Smith in my forms. In other words, when paging through my employee forms, I notice I have three identical entries for Joe Smith. I only want one Joe Smith, but with 3 entries for his 3 different wage rates and when they are valid. So in addition to adding the entries into the subform, for some reason the database has also created identical entries in the form also.

What did I do wrong?

Thanks in advance,
Dennis

 

[LittleSmudge]

What table is the Empoyee form bound to

What is the Primary key in this table

What table is the wages sub form bound to

What is the primary key in this table

What is the name of the foreign key in this table that links to the first table above

What does it say in the Link Parent Fields property of the subform control on you Employees form

What does it say in the Link Child Fields property of the subform control on you Employees form


The answers to these question WILL find the problem for you. If you don't see the problem yourself then post the answers back here.







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[djmurphy58]

OK, I'm a rookie, so bear with me.

The bound table is the one that the information gets posted to after you type it in the form, correct?? If so, then here are the answers:

Emplyee form is bound to tblLabor
PK in tblLabor is EmployeeID
Wages Subform is bound to tblEmployeeCosts
PK in tblEMployeeCosts is - No primary key
The PK in tblLabor (EmployeeID) is linked to the foreign key of EmploeeID is tblEmployeeCosts

Both Link Parent/Child Fields say EmployeeID

Thanks,
Dennis

 

[LittleSmudge]

Okay

If the table is tblLabour then WHY isn't the Prime Key LabourID ? - So that when you come back to this project in 12 months time you can tell easily which table the field is the PK for.

tblEmployeeCost needs a primary key - call it EmployeeCostId

I'd advise changing the foreign Key in tblEmployee to EmployeeRef because it REFers to the record in tblEmployee.

Other than that - all of what you answered sounds correct.

But lets probe a bit deeper

Open the main form in design view and open the properties dialog box
What is the text that appears in the form's RecordSource property ?

Do the same for the subForm


When you looked at the data and saw three entries for Joe Smith where you expected one - were you looking
a) at the Form
b) directly at the tblLabour
c) at a query
If c) then what is the SQL string for that query ?







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[djmurphy58]

AH HA!!!!!

I think I found my problem:

The RecordSource for the form is qryAddEmployee
The RecordSource for the subform is tblEmployeeCosts

I don't know why I would have made the recordsource a query instead of a table.

D

 

[LittleSmudge]

It's quite reasonable to have a query as the Recordsource of a Form IN CERTAIN Cases.

This is NOT one of them


Glad I've helped -

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.