form, subforms and more subforms!

[nmodhi]

Hello everyone,

I'm having a very rough time linking and connecting two subforms and both connecting to one main form. Sounds simple but I believe it really isn't.

My database is fairly straightforward. There's a unique employee ID on the main form that links the main form to the subform. That subform links to the other subform.

The information of my database is as follows..

For each employee there's a weekly Timesheet. And For each Timesheet there's a daily entry.
I hope you can see what I'm working with, it'd be great if anyone can help

 

[vbaInet]

Look at the following properties of your subform on the Data tab: Source Object, Link Master Fields, Link Child Fields. The link fields is the field that connects your subform and main form. The source object is the main form.

 

[nmodhi]

Thanks for the quick reply vbaInet

I believe I set the master fields and child fields correctly for both subforms. I'm still having trouble. I got to a point where I created a subform inside the subform that's on the main form.

The main form has a unique Employee ID that links it to the subform which has a unique Weekly Timesheet number (1 through 52). So the field link here is the employee ID. Now, using another subsubform, this time as a continuous form, I have a daily entry(1 through 7). This links to the subform by the weekly timesheet number.

I can link the weekly timesheet form to the daily form with no problem and it works perfectly. I just have no way of tracking whom the timesheet is for.. The master field usually always links to the main form..I can't seem to make that work..

 

[vbaInet]

Thanks for the quick reply vbaInet

I believe I set the master fields and child fields correctly for both subforms. I'm still having trouble. I got to a point where I created a subform inside the subform that's on the main form.

The main form has a unique Employee ID that links it to the subform which has a unique Weekly Timesheet number (1 through 52). So the field link here is the employee ID. Now, using another subsubform, this time as a continuous form, I have a daily entry(1 through 7). This links to the subform by the weekly timesheet number.

I can link the weekly timesheet form to the daily form with no problem and it works perfectly. I just have no way of tracking whom the timesheet is for.. The master field usually always links to the main form..I can't seem to make that work..

No problem. Zip and attach your db and I'll have a quick look.

 

[nmodhi]

sorry for the delay, I had to convert the file to 97 format since the zip file way just a bit over the limit.

So the main form is Timesheet and as a weekly timesheet number is entered near the top, daily entries can be filled in the continuous form at the bottom. The problem I have is to link the employee subform (employee table) to both forms via badge number(employee ID) I changed the information to false data. Hope you can help!

 

[vbaInet]

You cannot have an explicit link from two tables to one table. And you can only link subform to it's parent form. Why do you want to do that?

 

[nmodhi]

I figured since each employee would be entering daily information, their timesheets would be stored according to their name..am I on the wrong track? what would be a better alternative

 

[vbaInet]

In that case you've got things mixed up. Your employees form should be the Main form, subforms would be Timesheet and Timesheet entries. I'm guessing an employee is linked to both the timesheet via badge number? There shouldn't be a direct link from Employee to Timesheet entries. It goes like this: Employee links Timesheet via BadgeID, Timesheet links Timesheet Entries via Timesheet Number. Is this your setup?

 

[nmodhi]

thanks once again! Yes that's the setup.

I thought of setting the employee form as the main form before but couldn't seem to work. I whipped up something quickly and I'm wondering if this is what you mean?

 

[vbaInet]

Excellent. Just having a look. Had to go get some dinner

 

[vbaInet]

There you go. Sorted. When you want to create a relationship, don't do it via the Relationships window, perform your links using the Lookup Wizard on the design view of the table (where you select data types) of fields.

 

[nmodhi]

You're the best thank you!!

 

[vbaInet]

You're welcome. You seem to be using a pretty old version of Access though, 97? Had to convert the first db you sent but the other was fine.

 

[nmodhi]

Oh the first attachment was to large to send so I converted it down to save time. I have 2000 though

 

[vbaInet]

2000 is good. At some point try 2007. It's pretty cool.

 

[nmodhi]

I guess when the workplace decides to upgrade to 2007

 

[vbaInet]

Hehe!! I guess so. Good luck with the rest of your database. Cheerios.

 

[nmodhi]

hey vbaInet I have a particular problem with the database..

Each employee has a weekly timesheet. So for example timesheet number 1 can be used and will probably be used by every employee. Currently, Timesheet number on the subform is unique so Timesheetnumber 1 is the same for all employees.. thus I tried removing the primary key but it really didn't make much of a difference.
Maybe I'm missing something..any ideas??

 

[vbaInet]

Shouldn't the timesheet number be unique to each employee? It's a one-to-many relationship between employee and timesheet. Just like you said, each employee has a weekly timesheet.

Unless you mean each timesheet (type) can have many employees. In orde to achieve a many-to-many relationship you would need to create a number table, maybe call it "TimesheetAllocations" and it would be the intermediary between Employee and Timesheet. It should have the following structure:

Timesheet_Allocations
----------------------
AllocationID (PK)
TimesheetID_FK (Foreign Key linked to TimesheetID in Timesheet table)
BadgeNumber_FK (FK linked to BadgeNumber in Employee table)

Also since your BadgeNumber is unique to each employee, I don't think you would need a seperate EmployeeID? It's up to you to have it or not.

 

[nmodhi]

thanks for sticking around vbaInet!

I believe you're right, timesheet (type) can have many employees. Can you further explain what you mean by an intermediary table between Employee and Timesheet..
Do I display the AllocationID onto the form ?