Please Help with Pop up form

[eforce]

OK,
I have a "EmployeeDataForm" that we input various info in regards to our employees that work in our company.

One of these fields is a combo box to which you select the "[DutyPosition]". The "DepartmentSecretaryInfoForm" I've created pops up when the [DutyPosition] field (ComboBox) "Department Secretary" is selected.

I also have a report that features all of these employees and their home phone numbers, addresses, ect...

These reports are grouped by [Department] and each department has a secretary. The secretary is to be automatically listed in the Privacy statement at the begining of each report per Department.


Each department has their own secretary.

Once the "DepartmentSecretaryInfoForm" pops up and is filled out, I would like for the info that is input which is: (Department, SecretaryName, & Phone) To be placed into my report which is grouped by [Department] to have this Example statement with the Department, Secretary Name & Phone number to be automatically entered.

Ex: "This info is strictly confidential and may not be used for any purpose other than official business........blah blah blah...Please contact "[Secretary]" at "[555-555-1212]"."

If I could have the table which is the record source for "EmployeeDataForm" to automatically be populated with this info based on the Department that is selected on this form and the info inputed in the "DepartmentSecretaryInfoForm"
then that would work as well.

Please Help!
I can attach my database if need be.

 

[FilipT]

????

I don't follow you 100 procent in the explination of your problem.
But if you post the database, I can have a look at it if you want to.

 

[eforce]

The DB is at work. I promised my wife "No computer during this Turkey Day stuff".

Basically, I have FormA with many fields amongst the many fields are the [DEPARTMENT] field, and [JOBTITLE] field. [DEPARTMENT] field is a combox used to select your particular [DEPARTMENT]
and the [JOBTITLE] field is also a combobox used to select your job title. (recordsource is "TableA" which also has fields that are not on the form: [SECRETARYNAME] and [SECRETARYPHONE])

I also have FormB (recordsource is "TableB" has 3 fields, [DEPARTMENT], [SECRETARYNAME] and [SECRETARYPHONE]) which pops up but only for the particular people with "Secretary" selected as their [JOBTITLE].

Now, here's what I want: Being that there is only one Secretary per Department; I want for every employee that has a particular Department selected to also have the [SECRETARYNAME] and SECRETARYPHONE] fields in "TableA" automatically populated with the data that their Secretary field out on his/her pop form "FormB".

Ex: Bob works in Technology Department. The Technology Departments' Secretary is Jim. Jim has already inputed his information on Pop-up Form "FormB" now everyone in the Technology Department has this information in their table "TableA".

This is important because of the Report (Contact Roster) that is used which is grouped by Department. This report has all employees in a particular Departments' phone number, address, ect..

There is a Statement at the beginning of each Departments report which lists that Departments Secretary and phone number as the point of contact for this report. I used Concatenation in this statement.

Thanks, for your patience

Eforce

 

[shay]

Hope I've understood the problem correctly - I think you want to end up with each Department secretary's info on the Department report.

What you should not do is copy the info from the Secretary table to the Employee table (bad db design to have the same info stored more than once) - keep the tables separate but linked eg by SecretaryID.

I think I'd then create a query (containing the fields from the Employee table and the Secretary table) and use this as the recordsource for the report.

You could group the report on your SecretaryID field and put the secretary's data fields in the Group Header and the employee fields in the Detail section. This should work.

Please ignore all this if I've misunderstood the question!

shay