Allow editing of a form based on a query

[swisstoni]

[Solved] Allow editing of a form based on a query

I've got a form setup running from a SELECT query, as I need more information than is in one table.

I'd like to be able to edit the items. I understand that Access locks the form fields as it doesn't really know how to update them seeing as it's based on a SELECT query.

This must be do-able, and I'm sure I can't have been the first person to request this - but I am finding it difficult to find a solution.

From working with VB in .net, (a while ago!) I remember setting a different UpdateQuery for a gridview, aswell as different delete and select queries. Is there any way to do this within an access form?

Many Thanks.

 

[pbaldy]

It isn't that the form controls are locked; the query itself is read-only. That isn't automatic, it depends on the nature of the query. Here are some thoughts:

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

 

[swisstoni]

Thanks for the info.

However, it's a pretty simple query and I don't see that it fits any of those criteria.

The one that it could be closest too is probably the one about having the keys setup correctly.

My tables are as follows:

tblItem - with item_id, item_name, etc........ (key - item_id)

tblOrder - with order_id, customers name, phone, etc. (key - order_id)

tblOrder_Item - with order_id, item_id, item_name, item_qty, item_price - to store the items on each order, along with their price and name (at the time they were ordered). Keys are order_id, item_id (maybe the dual keys causing this to mess up??)


My query is:

SELECT tblOrder_Item.item_id, tblOrder_Item.order_id, tblOrder_Item.item_qty, tblItem.item_partno, tblOrder_Item.item_name, tblOrder_Item.item_price
FROM tblOrder_Item, tblItem
WHERE tblOrder_Item.item_id=tblItem.item_id;


Again, any help would be greatly appreciated!

Not sure if it's worth mentioning, but these are all ODBC link tables...

 

[boblarson]

If they are linked tables to, say a SQL Server backend, then you would need to specify to Access when you link them which field(s) are the primary keys. You also need to have them set up as primary keys specifically on the server.

 

[swisstoni]

Hmm, well I was just about to reply saying that they were all setup okay, but thought I'd just check.

tblOrders and tblItem are pretty standard - they're both setup correctly within Access and the source (mySQL) as primary keys on their id's.

In the link table tblOrder_Item, I had setup the foreign keys using MySQL query browser, and this had automatically setup a Primary key on both order_id and item_id fields - I guessed this was okay.

I've now deleted all the keys on this table, if someone could let me know the best way to set those keys up, that would be great.

 

[boblarson]

In the junction table where you have order_id and item_id, you should just have a separate autonumber field as the primary key for that table. No need to use a compound key for the junction table.

 

[swisstoni]

Thanks.

I've done that, created a field orderitem_id and set it as a primary key.

I've put the foreign keys back on item_id and order_id.....

But still - "this recordset is not updateable"

:-(

 

[boblarson]

delete the linked table and then relink them. Then, when you link there's a spot where it should pop up a little pop up which asks what field is the id field. be sure to select the id field.

 

[swisstoni]

Already tried that a couple of times.

It doesn't ask for me to choose a key now I've added the single primary key field orderitem_id.

When I go in and look at the table design, it looks like Access has automatically detected the key as this ID field is already setup as a primary key...

 

[boblarson]

Can you edit the field directly when you open the table in the Access side? If so, then it does indicate something wrong with the query. If not, then I'm afraid we're at the end of what I can do to help as I'm not a MySQL guy, but SQL Server.

 

[swisstoni]

Yes - if I just open up the table, then I can edit that fine, or add new records!

So yes - it would seem it's something to do with the query! If I get rid of the join, it works fine (as in SELECT * FROM tblOrder_Items - going back to basics!).

 

[swisstoni]

Bit of an update..

If I change the query to the following it works fine:

SELECT tblOrder_Item.item_id, tblOrder_Item.order_id, tblOrder_Item.item_qty, tblItem.item_partno, tblOrder_Item.item_name, tblOrder_Item.item_price
FROM tblOrder_Item LEFT JOIN tblItem ON tblItem.item_id = tblOrder_Item.item_id;

I used to use JOIN's when I was more self taught SQL. But at Uni they've taught us to do it as shown above... not really sure which is better, or why.

 

[boblarson]

After looking back at your query, I am guessing that it is that you aren't specifying a Join that might be causing your problem.

 

[boblarson]

I think we posted at just about the same time.

 

[swisstoni]

Great minds eh!

Many thanks for your help sir, there's a click on those scales for you!

 

[boblarson]

Glad you finally got it going. I know how it can be a pain trying to figure some of these issues out. I had one recently with my linked SQL Server that I was lucky enough to stumble upon the answer for. Even someone I knew with much more SQL Server experience than I had said that they never would have thought about that one. So, anyway - good luck on the rest of your project.