Recordset not updatable due to Linked Table (1 Viewer)

NSAMSA

Registered User.
Local time
Today, 08:02
Joined
Mar 23, 2014
Messages
66
Hi:

I have a query that references a linked table's dataset to filter the datasets down. The issue I'm running into is that I cannot update any of the items that are not in the linked table, because the dataset is filtered by the linked table. Is there any way around this?

Thank you
 

Ranman256

Well-known member
Local time
Today, 11:02
Joined
Apr 9, 2015
Messages
4,339
If you are not updating the link table then it should work,but SOME joins do not allow updates.

as a workaround, try writing the affected records to a temp table,(make table qry)
THEN update the main data from the temp.
 

MarkK

bit cruncher
Local time
Today, 08:02
Joined
Mar 17, 2004
Messages
8,178
...because the dataset is filtered by the linked table.
I don't understand what this means. Maybe explain exactly how this filtering works.
Cheers,
Mark
 

NSAMSA

Registered User.
Local time
Today, 08:02
Joined
Mar 23, 2014
Messages
66
I don't understand what this means. Maybe explain exactly how this filtering works.
Cheers,
Mark

I will try to explain as best as I can. I have a database for product specs. There are three kinds of specs used for items. spec C is the most specific followed by B and then A. So, I have two tables in the database. One shows what the B spec is in relationship to C specs. The other shows which A spec is in relationship to B specs.

I created another database that has an event where a C spec must be specified. Since the product specs are updated in the first database, I linked to those tables in the new database. In the new one, I choose a C spec for each record. I created a query to filter records, and one of the criteria is by A spec. To do this, I simply created the relationship between the A to the C spec documented. However, by doing this, the query won't let me change records in the table, even though I wouldn't be altering any records in the linked tables. I need to filter by A and I need to be able to change the parts of the records after doing so.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:02
Joined
Feb 19, 2002
Messages
42,970
Why are the tables in two databases? Shouldn't they all be in the same database? Do all the tables have primary keys defined? Does the "c" table have a foreign key that points to the pk of the "a" table?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:02
Joined
May 7, 2009
Messages
19,169
Create a Left Join on each relations on your queries.
 

NSAMSA

Registered User.
Local time
Today, 08:02
Joined
Mar 23, 2014
Messages
66
I believe I've discovered the issue, and its not what I originally thought. The form is based off of a Union Query which is not updatable. I need to figure out a way around this. I have two buttons on the original form. One for "Open" and another for "Closed". In one query I ask that if the open button is true, then closed, (which is a yes/no field) = false or no. The other query is for the other button where if Close =true then closed is true in the record.

I then union these, because you can look at closed records, open records, or both open and closed records. Any suggestions on how I can make this a non-union query?
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:02
Joined
Oct 17, 2012
Messages
3,276
It's possible to set up a query so that it matches either of:
  1. A field matching the value of a control
  2. A control having a certain value.
So for a Boolean field, you can do this:

Set up a combo box with 2 columns. The bound and hidden column has values of 0, -1, and 99. The visible column has values of "Open", "Closed", and "All".

Then use this where clause:

Code:
WHERE (TableName.Closed = FormName!cboControlName OR FormName!cboControlName = 99)
If Open or Closed are selected, the query will match your closed field against 0 (False/Open) or -1 (True/Closed), which are how Access actually stores Boolean values. If All is selected, then it will pull all records.

I'd post an example I just completed Tuesday, but, well, it compares against 3 fields and thus has an 8-grouping WHERE clause.
 

Users who are viewing this thread

Top Bottom