Solved Query is uneditable

[Antti]

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]

Edit your Query.
in SQL View, change Inner Join to Left Join.
If not all Invoice is showing, change to Right Join.

 

[Antti]

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]

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]

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;

 

[arnelgp]

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]

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]

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]

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!