Change Data based on User Input 1

[WebGodiva]

I have two tables:

TbleInspector (general employee info)

TbleProject (project related info store by EmpID)

I have the table relationship set up with the EmpID as the primary key related in both tables. I have set up the EmpID as a lookup field in the Project table as all it will have is the employee id and the project info. the Inspector table has all general employee info.

What i've been trying to do is this:

Generate project information and have it stored by employee number. My problem is that when I have to change an employee ID (EmpID) in the Inspector table (when going from part-time to full-time, Employee number changes), it doesn't carry (cascade) over to the Project table - i then loose all project information related to that specific number unless i go in an manually change the number in the project table.

I have tried an update query but it just adds new records if there are any, doesn't change the current.

My tables are set up as:

TbleInspector

PID - Primary Key
EmpID - will always be unique - diff prefix for diff regions
Lname
Fname
JobTitle

TbleProject

PID
EmpID (look up from TbleInspector)
ProjName
ProjLocal
ProjClient
ProjDate
ProjDesc

I have created a dataentry form which uses the TbleProject as a subform - it will show records as long as the numbers (empid) have not changed, the minute you change the EmpID in the TbleInspector, the projects are hidden as the numbers don't match. I know this is probably a simple question for most out there but i'm really stuck and have been pounding my head against the wall. Any help would be appreciated.





"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn

 

[JoeAtWork]

In terms of database design, you should relate your foreign keys to the real primary key (which never changes).

Instead of an EmpID in TbleProject, you could have InspectorID which relates to TbleInspector.PID.

So relationship is:
TbleInspector.PID <------> TbleProject.InspectorID

Since PID never changes, you don't need to worry about this breaking anymore. And it is a superior database design.

(If you wanted, you could still call it EmpID in TbleProject, but that might be confusing).

 

[WebGodiva]

Thanks so much...i knew there was something that just wasn't right but couldn't for the life of me figure it out. i appreciate your help....not much for access programming and am learning by fire....i'm more of a webdesigner but hey...multi-tasking seems to be the in thing anymore.

again, many thanks...i'm gonna give it a try today.





"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn