Trying to create a relationship between 2 tables where 2 fields are look up refrenced

[Ht99vpi]

Hello, I am new to this forum and have tried searching several different DB forums looking for a similarity to what I am attempting to do. I am a firefighter working on a project for my supervisor that will track our Employees shift exchanges. I have some knowledge in both access and SQL. I have created 3 tables 1 is Staff_Details which has all our staff listed. The other 2 tables are Trade_Off and Trade_Worked, they will be used with a form I will create later to allow a supervisor to enter the data for each exchange. This is what I have so far.



Table
Trade_Off
Fields
ID, Staff_Name, Date, Duration, Staff_Exchange
Table
Trade_Worked
Fields
ID, Staff_Exchange, Date, Duration, Staff_Name

Current set up Trade_Off table is filled out with all information, using Staff_Detail table as a look up for both Trade_Off.Staff_Name, and Trade_Off.Staff_Exchange fields. I would like to have the Trade_Worked.Staff_Exchange field automatically filled out based on what is entered in the Trade_Off.Staff_Exchange field. Previous attempts have resulted in displaying the Staff_Detail.PriKey field not the Staff_name, which is what I want.
Other attempts have ended in Key index errors when creating the relationships.

The second thing I am ultimately looking to accomplish is to track and update the Trade_Worked.Duration field as the Trade_Off.Duration is filled out linked to the Trade_Off.Staff_Name. This is to allow us to track the hours of the shift exchanges for each employee and verify remaining hours for each employee for each trade.

The initial Trade_Off will not have a date selected for the Trade_Worked table for repayment of the Exchange this will be added later when it has been identified.

I am open to any other options for accomplishing the same task if anyone has any thoughts. I can also provide any additional details if needed to understand my goal or what I have already done.

Paul

[ssanfu]

If Jim Jones wants 12 hours off, can Susie Smith work 6 hours and Bill Brown work 6 hours? Or is the exchange 1 person to 1 person?

[Ht99vpi]

The pay back of the hours is 1 to 1 but it can be split up among several people. So Jim Jones will have to work for Susie Smith 6 hours some other time and Bill Brown will need to do the same.

[ssanfu]

This is really rough. It is in Acc2000 - I can convert it to Acc2010 tonight if it doesn't work for you.

When you open the dB, it should open to a form to add shift exchanges. There is also a form to add new staff - just names....

How close is this??

[Ht99vpi]

That is almost perfect but the column for remaining hours is not displaying any data. I can fine tune the rough edges. This will save me many frustrating hours. Thank you. Do you think the problem is the difference between 2000 and 2010,as far as the remaining hours? Also how did you set up the 2 forms to be used? Is one for entering the original exchange off and the second for the pay back of the exchange?

[ssanfu]

Yes, the top part (main form - Trade Off) is for entering the original request for time off and the bottom (sub form - Trade Work) is for entering pay back hours.

Hmmm, the remaining hours is updating for me. I'll look at this tonight at home using A2K10...

-----------------
I was thinking about taking out the control (combo box) for the Staff_Exchange in the top (main form), because there could be 1 or more staff paying back.
In the sub form (Trade Work), I was also thinking about taking out the control (combo box) for Staff_Name, but still update the table field with the Staff_Name ID since the records are linked. I'll see what it looks like tonight..

[ssanfu]

I converted the A2K dB to A2K10 accmdb.

That is almost perfect but the column for remaining hours is not displaying any data

It is working for me - both versions. Are the colors being displayed?

Attached is the A2K10 version.

[Ht99vpi]

There is still no data displaying in the Remaining hrs column. When you ask if the colors are visible, the only colors I see are the 2 buttons for close form and quit. The Set BG button does not work at all if that is what you mean. I also got an error "Run-time 2452 The expression you entered has an invalid reference to the parent property" when entering paid back time when I tried debugging it I was taken to this

Private Sub Form_AfterUpdate()
Me.Parent.RemainingHrs.Requery
End Sub

The second line was highlighted.

See attached not sure what colors your talking about.

[Ht99vpi]

@ssanfu

You have been a tremendous help. Are you still available to assist me with trouble shooting the remaining hours calculation? I have not heard back from you in several days. Please let me know so I can work toward finding a solution. Again I am very grateful for the help you have provided but I am unable to trouble shoot your DB myself because I am unfamiliar with VBA.

Paul

[ssanfu]

Sorry. I didn't see your response after my last post.

See my post #8. Have you tried it? I converted it to the A2K10 version. The remaining hours shows the hours - no errors for me.
If you just open my example, are there errors? Are the remaining hours displayed?

[Ht99vpi]

No problem, just didn't know where to go from here. I did receive your last DB converted to 2010, and it did not produce numbers in the remaining hrs column. I received no errors when opening the DB but did receive an error when entering a new record as noted in my #8 post. The attached picture is what the form looks like.

[ssanfu]

Would you send back the "ShiftExchangeDemo2" copy you were testing? I'll look at it tonight.
The remaining hours field is not showing up??

[Ht99vpi]

I have attached a picture of the error i am getting and highlighted the row I added when I received the error. Also I am not seeing anything in the remaining hours column not even a blank space. Again the error is occurring when I try to enter the pay back of some of the hours of an exchange.

ShiftExchangeDemo.zip

[ssanfu]

I'll have to look at it tonight - only have A2K at work.

[Ht99vpi]

Steve,
Just checking to see if you were able to find anything wrong with the DB?