Form Recordset is not updateable

[ChrisTheIntern]

Hey guys,

I need help on a problem where I want to create a form where I can write different recommendations on each textbox while displaying some information from a crosstab query (aka Query1) next to it.

I have included snapshots in the attachments for better visual.

My Ideal Outcome on the Form:
Vendor__Current Recs__ Current Month__Last Month_____2Months ago
Apple___[User Input]___[From crosstab] [From crosstab]_[From crosstab]
Microsoft [User Input]__ [From crosstab]_[From crosstab] [From crosstab]
Amazon_[User Input]___[From crosstab]_[From crosstab] [From crosstab]

The "Current Recs" have no value other than for printing out a report so that info does not need to be stored anywhere.

The "Current Recs" will be different for each Vendor. When I tried an unbound textbox, if I typed something in one, all of the textboxes copied the same thing.

My previous attempt:

1) Created a dummy table called [Current Recommendations] with all of the Vendors/EORs. (Table picture in attachment)

2) Created a tabular form based on this table.

3) Changed the record source for this table to:

SELECT [Current Recommendations].EOR, [Current Recommendations].Recommendation, Query1.[07/15], Query1.[06/15] FROM [Current Recommendations] LEFT JOIN Query1 ON [Current Recommendations].EOR = Query1.EOR;

(Query Builder Diagram in attachment)

4) Added Existing Fields to the detail section of the form
(A pic of this form is in attachments)

This form displays the report the way I want it but when I try to type in the textboxes under "Current Recs", the bottom banner says Form Recordset is not updateable. I don't understand why because I am only trying to edit the empty cells in my dummy table and I am not touching the crosstab query at all.

Current Efforts:
I am currently attempting to make a subform next to the crosstab data. I will update you guys on my success on that. In the meantime, can anyone give me suggestions on what I should do? I've already spent a day and a half on this issue.:banghead:

 

[jdraw]

Review this by Allen Browne

Good luck

 

[ChrisTheIntern]

Thanks jdraw,

What I got from the checklist is that the problem is: the query is based on another query that is read-only.

I feel like that justifies my current attempt: splitting the form such that [Query1] has its own subform and the table is in the main form so that the form query doesn't involve [Query1]. However, I don't know how to show multiple records on a form besides making a continuous type form. So a problem occurs when I try to put combine the [Query1] subform into the main form with the table in continuous form mode.

The error goes like:
A form with a subform object can't have its DefaultView property set to Continuous Forms.
You tried to add a subform to a form in Design view.

Is there a correct way to do this? By this I mean combine a table form with a query subform showing all records side by side. Or am I completely off the right track?

 

[ChrisTheIntern]

I just had an epiphany.

I created three new fields in my dummy table which are CurrentMonth, LastMonth, and 2MonthsPrior. Then I used VBA to populate those fields from the crosstab query when the user clicks to generate the report form. Now the report form is based off of this single table and everything works great! I can't believe I missed this simple solution! Thanks for the help guys =]

 

[jdraw]

Glad you have it resolved.