One To Many Lost My Way? 1

[Elvis72]

I have a subform in a main form that is link together in the following way:

Mainform - TblConstructionServices
Job/ProposalNo - primary key

Subform - TblProjectInformation
ProjectID - primary key
WorkerID - links to tblresume
Title ID - links to tblstaffrequirements
Job/ProposaNo - links to tblconstructionservices

So, the subform is this:

SELECT TblProjectInformation.ProjectID, TblProjectInformation.[Position Title], TblProjectInformation.[Hourly Wage], TblProjectInformation.[Title ID], TblProjectInformation.WorkerID, TblProjectInformation.[Job/ProposalNo], TblProjectInformation.[Requisition No], TblProjectInformation.[Mobilization Date], TblProjectInformation.[Demobilization Date], TblProjectInformation.[HR Classification], TblResumes.[First Name], TblResumes.[Last Name], TblResumes.[Cell Phone], TblResumes.Email, TblResumes.[Resume Link] FROM TblStaffRequirements INNER JOIN (TblResumes INNER JOIN TblProjectInformation ON TblResumes.WorkerID=TblProjectInformation.WorkerID) ON TblStaffRequirements.[Title ID]=TblProjectInformation.[Title ID];

What my problem is I need to be able to create a record in the subform when someone selects a Position Title from the Title ID combobox.

Right now it only creates a record when you select a Worker from the WorkerID combobox.

But I need to create the position before it is actually staffed.

So, I'm sure I have done something to point it to WorkerID but I cannot figure it out?

Your time and help is GREATLY appreciated!~

 

[Elvis72]

OK....I have figure this much thus far.

It is creating the record for the Position Title in TblProjectInformation but it is not bringing it back up when I re-open the main form.

Looking at TblProjectInformation the only difference in data is the WorkerID field is not populated?

 

[ZOR]

I presume you have set all your table relationships up in the relationships page.

Why not put a list of what you have in there so someone can see whats happening. ie

All relevant tables/names involved

All the tables related fields, ie show what is a Primary or Foreign key ss you just say "related to"

It might help, hope so.

Regards

 

[MajP]

If I understand correctly, the problem is that you have an inner join on workerID

INNER JOIN (TblResumes INNER JOIN TblProjectInformation ON TblResumes.WorkerID=TblProjectInformation.WorkerID)

So in sense it says return all records where workerID is equal. Therefore if you do not add a workerID then no join.

Can you use a left outer join?