Creating a form that loads data from the tables

[misterweber]

I've received some excellent help around here, and I hope that someone can help me out again.

In this discussion, I described my tables and relationships. (btw - thanks!) To bring it back again, my table structure is like this ( italicized fields are linked fields bolded fields are primary keys ):

Task
TaskID
SubProject
Description
Job Code
etc...


Allocations
AllocationID
TaskID
ResourceID
Hours
etc...

Resources
ResourceID
FirstName
LastName
Role
Department
etc...

The idea is to create a single form (or series of linked forms) so that when one enters in a new Task, at the same time one can also choose the resource and hours. I was able to get the queries to link properly. However, when I get to the part where I enter the resource information, it doesn't select from the list... it instead creates a new resource every time! The only way to select a resource from the list is to pick the exact Resource ID from the list. I would like to have it look up the name (e.g. when looking up the resource "John Smith", one could type in "Smith" in the last name field, and it would bring up the record for John Smith.) I read a suggestion once to do a duplicate primary key, but it wasn't allowing any of that. Any suggestions?

 

[MustBNuts]

I didn't realize you had posted your question in with someone else's, but answered yours in his/her post. Are you saying that did not work? If so, let's try something else.

As I understand it, your resources will never change (at least not from this form). So just dealing with the Resource issue, your Defect table should have a field for the resource name and it should be a lookup to the resource table ( you may want to choose to display both ID and Name but only save ID in the table, this will make it more user friendly).

Now create a query that has all the pertinent info for the defect (from the defect table) and any fields you need from the resource table (EXCEPT the resource ID, it should be coming from the defect table).

Just make sure that you have linked the two tables by ResourceID in the top portion of the query (if it isn't already done for you).

Last step, create a new form. You should find that when you choose an ID, the rest of the Resource-based fields will autopopulate and the defect table will save only the resource ID its own field.

Hope this helps,

MBN

 

[mdmilner]

I have a 3 table, 3 form demonstration mdb that I'd be willing to forward to you.

Specifically, it's setup with one-to-many relationships for task->allocations->resources allowing 1 task, unlimited allocations, unlimited resources to each allocation.

The three forms are setup as Form->Subform1->Subform2 with Cascade Updates on the relationships.

I'm suspicous that you haven't setup your relationships properly.

Mike

 

[990405]

I have a 3 table, 3 form demonstration mdb that I'd be willing to forward to you.

Specifically, it's setup with one-to-many relationships for task->allocations->resources allowing 1 task, unlimited allocations, unlimited resources to each allocation.

The three forms are setup as Form->Subform1->Subform2 with Cascade Updates on the relationships.

I'm suspicous that you haven't setup your relationships properly.

Mike

********************************
I have a similar problem and would appreciate if you could send this file

thanks Vic