Editing in a query linked to a linked table

[NSAMSA]

Hi:

I am having an issue with a query not being able to be edited. I created a database with an ODBC connection to my company's Warehouse Management System. However, for purposes of auditing product from specific sections, and the ODBC table being unable to be edited, I created a new table called tblTargets created through an append query which adds the column "Target Pallet" as a Yes/No check box and filters by quarantined product. This also has the following columns which can be found in the ODBC linked table as well - SAP#, PalletID (key), Pallet#, Notes. In order to only see product that we have currently as opposed to seeing all product ever appended I created a table which exclusively has columns from the editable table as its source, but creates a relationship between the editable table's Pallet ID and the Pallet IDs on the ODBC table. This ensures that only product present in the plant is shown and product shipped is omitted.

I believe that since the ODBC table cannot be edited and the relationship exists, that this makes it so that my query cannot be used to edit the tblTargets table. Is there a work around here so that I can use the ODBC table to filter out non-existent products, but can also edit the tblTargets table through the query?

Thank you and Cheers

 

[Minty]

If you create a single left / outer join between your table and the other one you should be able to edit the data in your table. Experiment with the join type.

 

[NSAMSA]

the outer joins do not work. I still cannot edit. When I outer join to the ODBC, no data comes up and when I outer join to the tblTargets all the appended data comes up, when all I really want is the data present in the ODBC table. In all scenarios, the system will not allow me to check the Target box.

I'm trying to think of a work around. The only item in the tblTargets that I want to be able to edit is the Target (Yes/No) check box. The category is not present in the linked table and if I could block the user from updating the other data-pieces, then that would be preferable anyway.

I will keep trying different things and let you know if any of them work out. Thank you for the suggestion regardless.

 

[NSAMSA]

I was able to find a work around for my particular purpose. I created an update for my appended table that finds records not in the ODBC table. Then I created a delete query that removes those records from my appended table. Then I just inserted a button that does the three functions of appending, updating and deleting.