Queries, Comboboxes and Tables

Hi all -

I have a many-to-many relationship that is resoved into three tables (two parent tables and a junction table between them)

1. Project: ProjID, ProjName
2. Project-Employee: peID, projID, empID
3. Employee: empID, empLastName, empFirstName, etc...

I have a form on which I want to display all Employees who are working on a project. However, the names of the employees are stored as empFirstName and empLastName in the EMPLOYEE table. I wrote a query which combines the first and last name into an expression called empFullName.

When the user selects a project, the form will display a continuous form list of all the employees who work on that project, using the empFullName value from a query. What I would like, is for the user to be able to add or modify the employee(s) working on a particular project from this form by selecting their names from a meaningful list (which would mean both first and last name would appear in the list).

I imagine that this has something to do with setting the ControlSource and RowSource in some correct combination. I can get the contol on the subform to display the empFullName for all employees working on a project. However, the rest (i.e. reassinging the project to a new employee, or adding additional employees to a project) doesnt seem to be working correctly.


Ideally the end result would be:

1. A project is selected and displays the employees (if any) that are assigned to that project by empFullName (the result of the expression in the EmployeeQuery).
2. The person using the database can either reassign the project to a new employee or add another employee to the project, but they would be limited to a list consisting of empFullName for everyone in the company

I've attached the database with all the forms and tables as they are. Any help would be appreciated. I also need to do essentially the same thing for Applicants. I'd like to first display all applicants for a particular project and then be able to change or add applicants based on a list of all possible applicants derived in some fashion from the APPLICANT table.

help!

eric

eric, I do not see the new query or Form in your latest attachment, should it have been included?

hmmm... i'll have to check it. but i've already left work for the day, so I'll re-post it first thing in the morning.

*sigh*

thanks again for looking

Here is the "real" version of the file. Sorry for the confusion.

The main form is called Project Dashboard, and would eventually be a single entry point for most of the basic information relating to a project. I would also eventually build a series of forms for each of the linked tables (i.e. APPLICANT, EMPLOYEE, etc...) that contain more specific information about each one of those.

So the problem just to re-iterate is that, for example, I'd like to be able to select or edit project managers from a combo box which has a row source resulting from a query, but I cant seem to get it to work. I feel like its close, but then again I've been wrong before.

Also, how can i get all subforms to default to blank until a project is selected ?

thanks!

eric

eric, I have your Forms doing what you basically wanted, I have used "Tabbed" Subforms, it is just one option. If you don't like it I can use ordinary subforms.
Basically I just added the project and employee/applicant Ids to the subforms.

I have also taken the liberty of adding an Autoexec macro, a "Splash Screen" during start-up and a Main Menu which you are going to need for Table maintenance etc. I didn't create all the Queries and Forms to do that part though.
Only the first button is actually working at the moment.
Let me know what you think.

OBP -

This is amazing. There is one thing that is confusing me through. Im not quite sure what you changed to make it so that values could be selected from the combo boxes to dispaly the information in the tabs/subforms.

Also, it's not telling me that I cant change the "Project" from the combobox on the project dashboard form because it is bound to ProjID (which, i assume is a problem becasue the drop down list is displaying the values from projName), which is essentially the problem I was having before. What exactly did you do to make it work ?

best!

eric

eric, Combo (drop down) boxes are normally for Selecting pre-saved data. To add a new record you would usually use the Form's "Record Navigation" buttons at the bottom of the form.
The main change that I made was to add the Employee ID and Project ID fields to the Employee subform and the Applicant ID and Project ID fields to the Application Subform. I also made the Project Combo "Bound" to the Project ID field, which I added to the Query and form.

You can't change the Mainform Project once it has been saved with Sub form entries because that would create an error in the relationships.

eric, I have done a bit more work on the database today, I have fixed the problem with the Combo, added a "New Project name" field to the form and also some extra Tabbed Subforms.
The Main Menu buttons that do not have "blank" on them are now doing as it says on the button.
I have also started a "search Form" that allows you to "find" Employees, Contacts and Applicants, we need a bit more data in the Tables to really check it out.
I don't think that you need a Project/Comments link table unless you will be cross referencing Project within one comment, just having the ProjID in the Comments table should suffice.
I have not really "Formatted" the various forms that I have created as I don't know if you want to use any particular colours/schemes etc.
Have a look and a "play" and let me know what you think.

This is great. I think you've used a lot of different concepts here that I can definately learn from. I think I'm going to try and recreate some of the things you've done with some small changes to form design.

But for the time being, I just need to "play" (fun!). Im sure I'll be in touch soon.

Thanks again!

eric, just drop me a Private Mail when you are ready to continue.