Help! View data from another table...

[Sugada]

I linked a web site below so someone might beable to download an Access 2000 Budget database and view my major problem.

I'm trying to view records from an exsisting table (ITBudgetHistory) on a form (Budgetdata) but keep the record unbound providing information about the records already selected from the form so far. It's must easier if you just look at it.

But I'll press on...

I have a combo box that pulls data from the ITBudgetHistory table called Category. The next combo box (ExspenseType) can only be selected if the Category combo box had a record in it. This done by requiering the form after the first combo box (Category) was selected. This works great!!

Now, here is an example of what I would like to have happen next.

I select "General Expenses" from the (Category)combo box then that provides me with a list that falls under "General Expenses" from the Historical table. I then select "Salaries - Internal" from my new list now available in the (ExpenseType) combo box.

So now I would like my form do what ever in order to see "2001 Amount" for "Salaries - Internal". I don't want this information bound to the records being inserted into the table (ITBudgetdata), I just want to view some Historical information about what was select from Category and ExpenseType. Can anyone help me???

Maybe fixing my code that looks like:
____________________________________________
Private Sub ExpenseType_AfterUpdate()
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("BudgetHistory&quot
rst.FindFirst &quot;<criteria based on combo box value>&quot;
If Not rst.NoMatch Then
Me![??????] = rst![???????]
Me![??????] = rst![???????]
End If
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
____________________________________________
To download this mdb, go to:

http://www.sugada.com/tek-tipsproblem.htm

 

[Nordyck]

Sugada,
I have rewrote your form, it's not clean or pretty looking but it you should get the idea. I'm leaving the cleanup work for you.

Based upon the category, year, expense type that is selected, a subform is used to display the values along with the monthly totals and grand total. If you desire, you could show a graph based upon these values.

The reason why I changed it, because, you want to have the flexibility to select what you want and have the totals appear for a particular expense type, category or year. The way that it was being shown on your db, is that you could only see 1 expense type for a given year and you couldn't select which year you wanted. This is very restrictive for any type of management review/planning.

Link below is where the db is located.

http://www20.brinkster.com/freedom100/help/budget.mdb

In designing your database, I would change the tables so that the columns are
ExpenseID: Auto Number
Category: Text
Expense Type: Text
Expense Date: Date
Expense Amt: Currency
etc...

By formating this way, it makes use of graphs easier, and makes totals, subtotals simplier.

Nordyck
nordycki@hotmail.com