Insert Into problem?

[Deb Smith]

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.

 

[Wayne Morgan]

You have the form and subform linked, but are the tables also linked in the
Relationships window on the EventID field? If so, try setting the Default
Value of the desired fields in the budget table to 0.

If that doesn't work, then in the Current event of the subform, set the
value for EventID.

If Me.NewRecord Then
Me.txtEventID = Me.Parent.txtEventID
End If

 

[Deb Smith]

Thanks for the suggestion Wayne;

The tables are linked in the relationship window as well as on the
form/subform.
I tried setting the Event ID value in the current event of the subform as
suggested. This did input the Event ID into a record, however, the Expense
Category ID did not display and only one record not the 19 expense category
id numbers were shown. It appears my bigger problem is how to get all the
Expense Category ID numbers (ie budget categories) from the budget table to
display .
I really would appreciate any suggestions you might have or I have not been
clear as to what I am trying to do please lket me know.

Deb

You have the form and subform linked, but are the tables also linked in the
Relationships window on the EventID field? If so, try setting the Default
Value of the desired fields in the budget table to 0.

If that doesn't work, then in the Current event of the subform, set the
value for EventID.

If Me.NewRecord Then
Me.txtEventID = Me.Parent.txtEventID
End If

--
Wayne Morgan
Microsoft Access MVP

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.

 

[Wayne Morgan]

Ok, I'm lost. Are you trying to enter a single new record in the subform
when there aren't any there or do you want one line for each Category ID to
be prepopulated in the subform, even if there is not data for a particular
Category ID? In other words, every time you create a new record on the main
form, do you want 19 records automatically created in the subform?