Best way to represent a relationship in a Form

[adamcm]

Hi all,

I got lots of help with my last question so I figured i'd try again with something else .

Now what I have is two tables: Resource, and Project.



What I want to do is be able to add many resources (with a percent required) to many projects.

My initial thought was to create a table ResourceOnProject with ProjectID, ResourceID, ResourceName, PercentRequired. I then created a form with only Resource Name, and Percent Required displayed as a Multiple Item form. I then thought I should add this form into my Project form so my-coworker could select between one to many resources on the project.

Two questions:
1 - Is this the best approach?
2 - When clicking on the Add Project, or Update Project buttons, how will I update my DB to record all of the proper relationships between projects and resources?

Hope that makes sense, thanks!

[RuralGuy]

Not to me Adam. Want to take another shot at it?

[adamcm]

Not to me Adam. Want to take another shot at it?

Sure, I know i'm terrible with my words .

Essentially each Project can contain many Resources. I want to create a form where the user can select a Project from a drop down list and then select multiple different Resources for that project. The best way that I have found so far is to create a Multiple Item form. The only issue I have is that the user must select the same project over and over.

The other issue I have is that I can't figure out how to record my selections as ID #'s only. I tried adding the Project ID and Resource ID to the form, but that meant the user had to select them which I didn't want.

I know that it is better to represent each table with a primary key such as an auto number but I am thinking of removing that and having a my primary keys being ProjectName and ResourceName.

Anyways, I just wanted to check if I have missed anything in terms of forms. I can't see any other way to be able to add resources to a project without a Multiple Item form.

Thanks!

[RuralGuy]

The best way to view/edit the 1:m relationship is with a MainForm/SubForm arrangement. Is that what you are asking?

[adamcm]

Well I think it's actually a M:M relationship as a resource can have two different roles on a project.

Would a subform still be best especially when I want to assign resources to a project?

[ConneXionLost]

Hi Adam,

By creating the ResourceOnProject table, you successfully dealt with the M:M relationship problem between Resource and Project. The result is a 1:M between Resource and ResourceOnProject, and another 1:M between Project and ResourceOnProject.

Since Project is your focus, the 1:M relationship between Project and ResourceOnProject is what RG is referring to when he recommends a MainForm/SubForm arrangement. You appeared to be nearing the finish line when you made your "resource/percent" form and were going to add it into your Project form (hopefully, as a subform). However, one thing doesn't seem right. You state that:

The only issue I have is that the user must select the same project over and over.

If you have a SubForm working properly, your user shouldn't have to re-select the project like that.

This appears to indicate that the MainForm/SubForm arrangement may not be set up properly. It might be worth trying to insert the subform into the Project form again. The subform wizard for this usually does a good job for this.

Make sure that your Project form is bound to the Project table (via a query) and uses the ProjectID key; and your Resource/Percent subform is bound to the ResourceOnProject table (via a query), and uses ProjectID key. That way the wizard will be able to establish the Master/Child relationship based on ProjectID.

This will allow you to assign multiple resources to a project.

Cheers,

[adamcm]

Thanks for the info! That helps quite a bit and has me motivated to try subforms again. Can I select a proejct in my main form, resources from
my subform, and add them to my database?

I've seen examples for displaying data with forms and subforms
but not with adding to a table. I assume I could create a new table ResourceonProject for this information?

Last thing, how would I instruct my form to display names but record (in the table) as an ID instead of name?

Hope I'm making sense!

Thank you!

[ConneXionLost]

Hi Adam,

Can I select a project in my main form, resources from my subform, and add them to my database?

In most cases, yes. But usually it's best to keep everything simple & straightforward by having a separate form for creating a project, another for creating resources, then the form/subform, for combining them together. You can use command buttons to switch between the forms. You can reduce the number of forms for efficiency after you get everything up and running first.

I've seen examples for displaying data with forms and subforms but not with adding to a table. I assume I could create a new table ResourceonProject for this information?

Are you referring to a "second" ResourceonProject table? There's no need to create any more tables beyond the three we've already discussed to deal with this. The subform will transfer your information to the ResourceonProject table quite nicely.

Last thing, how would I instruct my form to display names but record (in the table) as an ID instead of name?

As long as you're using autonumber for your Primary keys, then everything will be handled by the form/subform & table relationships whenever you create a new record. You don't need to display the Primary keys, as long as they're part of the record source for the form. You are using autonumber, right?

Here's some further reading that will hopefully give you better answers than what I've done here:

Many-to-Many Relationships

Many-to-Many Example

Form / SubForm Example

Cheers,

[adamcm]

Hmmm..well I gave it a try with a Test DB containing the following:

Project table
ProjectID
ProjectName
ProjectDuration

Resource
ResourceID
ResourceName

ResourceOnProject
ProjectID
ResourceID
ResourceRequired

I then created a subform through the wizard. I now have the Project Name displayed above and a resource subfrom with ResourceName and ResourceRequired.

Some problems:
1 - Each time I switch a project, the same resources are displayed.
2 - Whenever I select a Resource (drop down list) it reads "The object doesn't contain the Automation object 'ProjectID'.
3 - I added a Assign button, but only ResourceName is recorded in the ResourceOnProject table.
Any ideas where I am going wrong?

[ConneXionLost]

Difficult to say; it may be the way you've structured your queries.

It's only 2003 format (it's all I have), but take a look at this example.

Hope it helps!

[adamcm]

Difficult to say; it may be the way you've structured your queries.

It's only 2003 format (it's all I have), but take a look at this example.

Hope it helps!

That's awesome! And it helps a ton! Im finally picking this up and this really helped.

Quick question. I was looking at your relationships and noticed that you have the 1 to many displayed on the relationship line. How did you do that? I created a relationship, but it only displayed as a solid line. But in the window where I created the relationship it says one-to-many.

Thanks!

[adamcm]

Sorry figured this one out