INSERT INTO?

[barit]

I believe I need help in contructing an Insert Into statement. Here is what I am trying to do.

I have a main form and subform. The main form is used to identify a specific event, while the subform is used to develop an event specific budget.

When I link the master/child fields to "EventID", none of the budget items are displayed since records have not been created in the budget table for this event.

What I want is when I open the main/subform that the event specific budget is diplayed or if no budget has been created that all budget items are displayed with 0.00 values so that a budget can be created.

I belive I need an INSERT INTO statement that will insert the expensecategoryID from the expense category table and the eventID identified on the main form into the budget table.In this way new event specific records would be created and data entry could proceed as required.

I cannot get this to work. Using the following I am inserting new records into the table however, they do not show up on the form when a budget has not already been created.I am not sure if I am even on the right track. Can someone please provide some insight as to how I can accomplish this.

Info that might help

Table 1 "ExpenseCategory"
ExpenseCategoryID - PK
ExpenseType

Table 2 "Budget"
BudgetID - PK
ExpenseCategoryID-FK
EventID-FK
BudgetAmount

Table 3 "EventInfo"
EventID=PK
EventName

The query statement I am using is as follows

SELECT [Expense Category].[Expense Type], Budget.[Expense Category ID], Budget.BudgetAmount, Budget.[Event ID], Budget.BudgetID
FROM [Expense Category] LEFT JOIN Budget ON [Expense Category].[Expense Category ID] = Budget.[Expense Category ID];

The INSERT INTO statement I am using is

strSQL = " INSERT INTO Query4([ExpenseCategoryID]) SELECT [ExpenseCategory].[ExpenseCategory ID] FROM [ExpenseCategory];"

Am I even on the right track? Do I need a where clause and if so what would it potentially be? Any and all suggestions would be gratly appreciated since I am now going round and round in circles.

 

[Mute101]

Hi,

I think you need to add something like this into your OnOpen event (or OnChange if you can select the event on the fly)

Dim Db As database, Rs As Recordset

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("SELECT * FROM Budget WHERE BudgetID =" & Me.BudgetID)

If Rs.EOF Then
    DoCmd.RunSQL = "INSERT INTO Budget SELECT ExpenseCategoryID FROM ExpenseCategory"
    Me.Requery
End If

Rs.Close
Set Rs = Nothing
Set Db = Nothing

This code checks for existing records and if not found creates a new record with a bare minimum of information (ie only the expense category id on the line)

This might point you in the right direction?

Sim

----------------------------------------
I was once asked if I was ignorant or just apathetic. I said, "I don't know, and I don't care."
----------------------------------------