user-based datasheet

[wshs]

im trying to create a data sheet users can use to manage their budget. This is what I have so far.
3 text fields: ProjectID / Project Name / Bud Amount /
here's the problem i can't solve. User has to be able to divide/catergorize their own budget line.
ie. ProjectID:test1 / Project Name: LOB / Bud Amount:10,000 /
User can then create his/her 'sub' categories.(datasheet)
ie. ProjBudgetLine: sub1, sub2, sub3
budget description: marketing1, promo2, tv3
budget amount: 2000, 3000, 5000
committed:2000,0,0
Spent: 0,0,1000
Balance: 0,3000, 4000
another tricky part.. balance of the columns needs to be caculated.. like budgeted amount: 10,000 (budget amounts can not exceed Bud Amount) and balance will be 7,000.

i've tried doing creating 2 forms.
first form where user can type in their projectID, name and amount and then user click OK and new form opens carrying over the amount.
on the second form, i've created a subform where user can type in their categories.
I can't get the logics/events to work tho...
i know this is very confusing and all but if you have any suggestions or solutions.. i'll be very grateful.

 

[sozzer]

So you have a primary key between the two underlying tables? Can you explain what's not working?

You can do this either through code or using access' built in functionality. I gather you're coding anyway though.

Never use datasheet forms myself and not sure that you can total within them - always just looked like a table to me!

Know this might not sound too helpful but i really dont understand exactly what you are trying to do that doesnt work.

LMK
A

 

[CautionMP]

Take your original form (w/ three fields) and create a subform on it with the Child/Parent fields based on ProjectID.

This assumes the following table structure:[tt]
Table: ProjectMain
pkojectID (primary key)
ProjectName
BudAmount
Table: ProjectBudjet
fkProjectID (foreign key)
pkProjBudjetLine (primary key)
LineBudget
LineCommited
LineSpent[/tt]

Make the subform a continous form, that way all line items are shown and the user can change them. When a new line is added the ProjectID will automatically populate from the Parent form.

There will be a couple of calculated fields on the subform:[tt]
Detail Section:
Balance ([LineBudget]-[LineCommited]-[LineSpent])
Header/Footer:
Total of line items (Sum([Balance])[/tt]

You can then validate the Main forms [tt]BudAmount[/tt] field agianst the [tt]subform.[Total of line items][/tt].

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[wshs]

well, how would i do that using datasheet?

 

[CautionMP]

Why datasheet?

You loose a lot of format control when you use a datasheet, calculations get goofy, you loose the form header/footer...

If you want your subform to look like a datasheet make sure all the text boxes in the detail section touch each other then change the following properties:[ul][li]Special Effect: Flat[/li][li]Border Style: Solid[/li][li]Border Color: 12632256[/li][li]Border Width: 1pt[/li][/ul]

(GMT-07:00) Mountain Time (US & Canada)