Solved Query is uneditable (1 Viewer)

Antti

New member
Local time
Today, 09:34
Joined
Jul 1, 2021
Messages
7
Hi.

(All my tables are linked SharePoint lists)

I have an InvoiceDetails Query that gets data from InvoiceDetails Table. That data is edited via an Invoice subform.

However, I would also like to get each item's name and category from Stock Table, which is linked to InvoiceDetails Table by barcode, and just showcase it in the subform for every item in an invoice.

Now if I link also Stock Table to the query, it makes the query uneditable. With local tables this didn't happen. Is there a way to get around doing it this way, or is there a better way? Indeed I don't need to edit the Stock table's data in this form.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:34
Joined
May 7, 2009
Messages
19,169
Edit your Query.
in SQL View, change Inner Join to Left Join.
If not all Invoice is showing, change to Right Join.
 

Antti

New member
Local time
Today, 09:34
Joined
Jul 1, 2021
Messages
7
Edit your Query.
in SQL View, change Inner Join to Left Join.
If not all Invoice is showing, change to Right Join.
Thanks, this worked!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:34
Joined
May 7, 2009
Messages
19,169
also make sure to Edit the subform in design view.
make those Item Name and Category Name (if you have) Textboxes Locked property set to Yes
to prevent accidental alteration of these fields.
 

Antti

New member
Local time
Today, 09:34
Joined
Jul 1, 2021
Messages
7
Edit your Query.
in SQL View, change Inner Join to Left Join.
If not all Invoice is showing, change to Right Join.
I'm having problems with this one. This worked for a while but then the Access file probably got corrupted since I can't open it anymore. Now in my backup I can't get the query editable this way. Here are also my SQL and screenshot of my datasheet:

SELECT a.ActionID, a.ItmBarcode, a.ActionQty, a.InOutQty, a.Notes, a.Status,
s.ItmName, s.Category
FROM Stock_T AS s RIGHT JOIN ActionDetails_T AS a ON s.Barcode = a.ItmBarcode;
 

Attachments

  • queryimg.PNG
    queryimg.PNG
    22 KB · Views: 452

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:34
Joined
May 7, 2009
Messages
19,169
can you reverse the order of the tables:

SELECT a.ActionID, a.ItmBarcode, a.ActionQty, a.InOutQty, a.Notes, a.Status,
s.ItmName, s.Category
FROM ActionDetails_T AS a Left Join Stock_T AS s ON a.ItmBarcode = s.Barcode;
 

Antti

New member
Local time
Today, 09:34
Joined
Jul 1, 2021
Messages
7
can you reverse the order of the tables:

SELECT a.ActionID, a.ItmBarcode, a.ActionQty, a.InOutQty, a.Notes, a.Status,
s.ItmName, s.Category
FROM ActionDetails_T AS a Left Join Stock_T AS s ON a.ItmBarcode = s.Barcode;
Made no difference.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:34
Joined
May 7, 2009
Messages
19,169
try to compact and repair your db.
if still does not allow editing, try using DLookup instead:

SELECT a.ActionID, a.ItmBarcode, a.ActionQty, a.InOutQty, a.Notes, a.Status,
Dlookup("ItmName","Stock_T", "BarCode = '" & [ItmBarCode] & "'") As ItemName,
Dlookip("Category", "Stock_T", "BarCode = '" & [ItmBarCode] & "'") As Category
FROM ActionDetails_T AS a;
 

Antti

New member
Local time
Today, 09:34
Joined
Jul 1, 2021
Messages
7
try to compact and repair your db.
if still does not allow editing, try using DLookup instead:

SELECT a.ActionID, a.ItmBarcode, a.ActionQty, a.InOutQty, a.Notes, a.Status,
Dlookup("ItmName","Stock_T", "BarCode = '" & [ItmBarCode] & "'") As ItemName,
Dlookip("Category", "Stock_T", "BarCode = '" & [ItmBarCode] & "'") As Category
FROM ActionDetails_T AS a;
Okay, got it working with the Dlookup. Thanks a lot for the help!
 

Users who are viewing this thread

Top Bottom