[Solved] Modifying Form data w/ SQL content type

[lnyork6]

I feel like this might be a stupid question but I cant seem to find a clear answer(probably because its a stupid question) but here goes...
There is a lot of explanation because I would like help with a solution sense my current method is probably not possible.

I have a form that contains a grid control with fields from 2 tables
tblItem
ItemID (Primary Key)
ItemDescription
ItemLocation (this is actually several fields, simplified to one for this example)
etc.
AND
tblInventoryItem
InvnItemID (auto number Primary key, needed a unique field for key, not used for anything)
InventoryID (used to filter items for the desired inventory)
InvnItemID (w/ relation to ItemTable.ItemID)
ItemCount
etc.

Edit: The first "InvnItemID" I meant "InvnItemAutoID"

The grid is sorted by the "ItemLocation" field from "ItemTable" and displays the ID and description from that table. It also contains the "ItemCount" field from "InventoryItemTable" that needs to be modified within this form.
The SQL command is as follows (tblFilter is self explanatory... i hope)

Code: Select all

SELECT "tblInventoryItem".*, "tblItem".* FROM "tblInventoryItem", "tblItem", "tblFilter" WHERE "tblInventoryItem"."InvnItmID" = "tblItem"."ItemID" AND "tblInventoryItem"."InventoryID" = "tblFilter"."fltrInt" AND "tblFilter"."fltrID" = 'filter' ORDER BY "tblItem"."ItemLocation" ASC

And now my stupid question...
Can this grid control be directly modified?
my guess is no because all my attempts have failed thus far
if anyone has a suggestion on how to make this work or an alternate method of at least filtering and sorting my "tblInventoryItem" by criteria from "tblItem" and be able to modify the count field

I feel i may have left out some essential information for readers. let me know and I'll do my best to clarify my needs

[Villeroy]

[Tutorial] Read-Only in Base
Add some subforms to your grid form. Each subform should be bound to one table, including the primary key, showing the editable record set of the selected main form record.

[lnyork6]

In a previous attempt to solve my issue I created a sub form for "tblInventoryItem" that allowed me to modify the count but the changes I make in the sub form don't transfer to the parent form where the grid is, so the grid shows the old data. I worked around this with macros that would reload the grid form and restore the row position with bookmarks but code is Inefficient and the further down the list i get, it takes longer and longer to reload and movetobookmark. Maybe there is a more efficient method for refreshing a grid control?

Edit: I used getrow and relative methods as well as bookmark

To clarify:
The user of this form will have a printed copy (with specific sort criteria) of exactly what the grid displays, with the count column filled in by hand. The Idea is to be able to go down the printed list and fill the hand written count into the form. This is why the sort order is important, otherwise I would just use "tblInventoryItem" as my grid form.
Efficiency and speed of data entry are my key concerns. The main grid form being for 'at a glance' data confirmation providing up to date record information from multiple tables.

[MTP]

Queries from multiple tables can be writable. The trick is including the primary keys from all the tables. Your query isn't writable because it doesn't include the primary key of the filter table. Just add that column to the query (no need to display it in the grid) and I think it will work as you want.

[lnyork6]

Awesome! Thank you MTP. Such a simple solution and it worked perfect. I didn't think to include the key as a column because I wasn't using it outside the initial SQL statement.

my solution

Code: Select all

SELECT "tblInventoryItem".*, "tblItem".*, "tblFilter"."fltrID" FROM "tblInventoryItem", "tblItem", "tblFilter" WHERE "tblInventoryItem"."InvnItmID" = "tblItem"."ItemID" AND "tblInventoryItem"."InventoryID" = "tblFilter"."fltrInt" AND "tblFilter"."fltrID" = 'filter' ORDER BY "tblItem"."ItemLocation" ASC