How to auto fill subform fields? 1

[twice300]

To all,

Remou helped me on another thread in which I needed to condition a subform.

Now that I have the subform available depending on the value of a field on the main form, I'm working on something else and I'm stuck.

I had the subform working, but I had the subform select the employee using a lookup to the Clerks table.

Since this is a subform, I'd like to have the EmployeeName in the subform set automatically to the Clerks.Name field from the main form. I've searched and read many articles but can't get it to work.

I read an article that mentioned that if I set the link master/child to the corresponding fields, this would work. I have set the fields Clerk.Name = Children.EmployeeName, but when I go into the subform, the Children.EmployeeName is not set automatically with the contents of the Clerk.Name field, actually it reads as "frmClerks" (this is the name of the main form).

Some background info for you.

Employee table fields:

Clerks Table has:
Name as text, here is where I store the employee name
Children as Yes/No

Children Table has:
ChildId as AutoNumber
EmployeeName as text
ChildName
ChildBirthDate

I have a one to many relationship for these two tables, tied by the Name - EmployeeName fields.


If I'm confusing, sorry, I know this is long, but I'm stuck.

TIA
Twice300

 

[Pampers]

Your form / subform (child form) setup reflects the way the tables are setup through primary key (PK) and foreign key (FK). So, to have your form and subform working, your tables have to be set right. Lets start with that. Please give table setup and their relationship.


Pampers
Keeping it simple can be complicated

 

[twice300]

Thank you Pampers for you quick response.

Table1 is Clerks (Main)
Table 2 is Children (Sub)

Table 1 has the following fields:
EmployeeId as Number (Indexed)
Name as Text (Indexed and Primary Key)
etc.

Table 2 has the following fields:
ChildrenId as AutoNumber (Indexed and Primary Key)
EmployeeName as Text
etc.

Table 1 has a relationship with Table 2 (1 to many) as:
Name - EmployeeName

Let me know if you need anything else.

Thanks,
Twice300 (gpadams)

 

[Pampers]

Is looking, good although one could prefer setting the primary and foreign key on the EmployeeID-field instead of the Name.

Next thing is that the link child/master field porperty of the subform must be set to the EmployeeName (or ID). Now the forms (and tables) will be synchronized.


Pampers
Keeping it simple can be complicated

 

[twice300]

Thanks.

I've looked at the setting you mentioned, they are set like this:

Link Child Fields = EmployeeName
Link Master Fields = Name

If I add a record manualy to the children table, they will be filtered correctly when showing in the form/subform. However if I try to add a new record, the EmployeeName field will not default to the Name field from the main form.

Actually it reads "frmClerks", which is the name of the main form.

Thanks,
gpadams

 

[Pampers]

And you also set the referential integrity when you joint the tables.

Pampers
Keeping it simple can be complicated

 

[twice300]

I did not have Referential integrity selected.

Which one do I select?
"Enforce Referential Integrity"
"Cascade Update Relateds Fields"
"Cascade Delete Related Fields"

Also under Join Type, which option should be selected? (1, 2 or 3).

If I have to alter these settings, do I then need to take the subform out of the form and then reinsert it, or will changes automaticaly apply?

Again, thank you very much.

gpadams

 

[Pampers]

You would enforce Referential Integrity and Cascade Updating. The join type can stay no 1. You dont have the reinsert the subform....

Pampers
Keeping it simple can be complicated

 

[twice300]

Thank again Pampers,

I set the enforce referential integrity, but the cascade option is greyed out, I can't select it.

One thing I haven't mentioned and perhaps this is causing the problem, is that the mainform has tabs, and the subform for the tblChildren is on the second tab.

I did try creating a new form with no tabs and adding a new subform for the tblChildren, but still no luck.

One question. Does the subform have to include the EmployeeName field for input, or is this set via code?

Thanks again,
gpadams

 

[twice300]

Pampers,

I tried creating a new employee table where the primary key in on the employeeid and created a new children table and added a childEmployeeId field, I linked these and was able to do the relationship and do the enforce referential integrity and do the cascade. I created 2 new forms for these tables and one as main and the other as subform.

this worked great, so I think the problem is that on my existing employee table the primary key is set to the EmployeeName field.

I tried to delete the relationship between the 2 tables (Clerks and Children), but I get an error, that states I can't delete the relationship inhereited from linked tables.

I inhereited the database design and was asked to add more functionality, if I can avoid making changes to the primary keys in the tables, I'd prefer that, if I have to, where I'll do it. My question is, is there a way to fix my problem without making changes to the existing primary keys in the tables?

Thanks again,
gpadams

 

[Pampers]

Hi twice,
Looks like you are working in a front end of a database with linked tables to a back end. If you want to alter relationships, you must do that in the back end.

Pampers
Keeping it simple can be complicated

 

[twice300]

Sorry my bad.

Yes I was working on the FE as you mentioned. I wanted to thank you for all the time you took to help me. I've decided to correct all the bad designing in the database now before this system grows any bigger.

So I have some chages to make and recoding to do.

thanks a million,
gpadams

 

[Pampers]

Sounds like a good plan. Good luck

Pampers
Keeping it simple can be complicated

 

[twice300]

I finished making changes to the database structure and code. Everything is working perfect now.

Thank you very much for all your help.

gpadams