datasheet form and not able to add new records.

[rum23]

I have a datasheet form and the recordsource is set to

"SELECT tblProjectBudgets.ProjectID, tblProjectBudgets.CIP_ID,
tblProjectBudgets.CIPBudgetItemID, tblProjectBudgets.ProjectBudget,
tblProjectBudgets.ProjectBudgetComments FROM (tblProjects INNER JOIN
tjunCIPProjects ON tblProjects.ProjectID = tjunCIPProjects.ProjectID) INNER
JOIN tblProjectBudgets ON (tjunCIPProjects.CIP_ID = tblProjectBudgets.CIP_ID)
WHERE (tblProjects.ROW_ID = 5)"

Allow Additions and Data Entry properties is set to true. However, when I
open the form, I'm not able to add a new records. Why is that? What am I
missing?

Thanks for your help.

 

[rum23]

If I run this query from the query window, it doesn't let me add records
which is probably the culprit. How do I fix it though?

 

[John W. Vinson]

I have a datasheet form and the recordsource is set to

"SELECT tblProjectBudgets.ProjectID, tblProjectBudgets.CIP_ID,
tblProjectBudgets.CIPBudgetItemID, tblProjectBudgets.ProjectBudget,
tblProjectBudgets.ProjectBudgetComments FROM (tblProjects INNER JOIN
tjunCIPProjects ON tblProjects.ProjectID = tjunCIPProjects.ProjectID) INNER
JOIN tblProjectBudgets ON (tjunCIPProjects.CIP_ID = tblProjectBudgets.CIP_ID)
WHERE (tblProjects.ROW_ID = 5)"

Allow Additions and Data Entry properties is set to true. However, when I
open the form, I'm not able to add a new records. Why is that? What am I
missing?

Thanks for your help.

Three tables joined together may or may not be updateable, and will in any
case be complicated and difficult to edit since you will have multiple copies
of the "one" side table records. Consider instead using a Form with Subforms.
I'm not clear on your relationships - it looks like you're filtering the
records in tblProjectBudgets by a value in tblProjects via an intermediate
table of some sort; it's very unlikely that this will give you an editable
unique record! Could you explain the logic?

 

[Linq Adams via AccessMonster.com]

You might also want to glance at Allen Browne's short article "Why is my
query Read-Only?"

http://allenbrowne.com/ser-61.html

 

[rum23]

Yeah, the query is little complex. Basically in the query below, you can
think of

tblProjects --> tblEmployee
tjunCIPProjects --> tblEmplyeeOrders
tblProjectBudgets --> tblOrderDetails

SELECT tblProjectBudgets.ProjectID, tblProjectBudgets.CIP_ID,
tblProjectBudgets.CIPBudgetItemID, tblProjectBudgets.ProjectBudget,
tblProjectBudgets.ProjectBudgetComments FROM tblProjectBudgets WHERE CIP_ID
IN (SELECT tjunCIPProjects.CIP_ID FROM tjunCIPProjects WHERE
tjunCIPProjects.ProjectID = (SELECT ProjectID FROM tblProjects WHERE
ROW_ID=5));

and it executes fine and let's me add a new record as well. However, when I
set this to the recordsource of the form, it doesn't work. Any ideas why this
is happening?

 

[John W. Vinson]

Yeah, the query is little complex. Basically in the query below, you can
think of

tblProjects --> tblEmployee
tjunCIPProjects --> tblEmplyeeOrders
tblProjectBudgets --> tblOrderDetails

SELECT tblProjectBudgets.ProjectID, tblProjectBudgets.CIP_ID,
tblProjectBudgets.CIPBudgetItemID, tblProjectBudgets.ProjectBudget,
tblProjectBudgets.ProjectBudgetComments FROM tblProjectBudgets WHERE CIP_ID
IN (SELECT tjunCIPProjects.CIP_ID FROM tjunCIPProjects WHERE
tjunCIPProjects.ProjectID = (SELECT ProjectID FROM tblProjects WHERE
ROW_ID=5));

and it executes fine and let's me add a new record as well. However, when I
set this to the recordsource of the form, it doesn't work. Any ideas why this
is happening?

Because a multitable query will - generally - let you update only the
"manyest" table, in this case tjunCIPProjects. You won't be able to update
tblProjectBudgets because (based on the relationships) there's no way for
Access to ensure that the record you're updating is unique.

 

[rum23]

OK. I will find another way to accomplish my tasks just like you suggested
by using a form and subform. Thanks much