Use UNION query as record source for UPDATE query

[kepston]

I have a situation where I need to record scrap quantities for parts that we sell and parts that are supplied, in a single table. I have a UNION query that combines the part numbers successfully, allowing the user to select from a combo box without knowing whether the part is supplied or manufactured. I need to provide a DEFAULT value for cost (which, if changed later would not be wrong). To do this, I would like to pull the price from the results of the combined UNION recordset
.
The AfterUpdate Event of the [part number] combo box contains an action query.

sql = "UPDATE [Internal Rejections Table] INNER JOIN [qryComponent_price] ON " & _
          "([Internal Rejections Table].[Part No] = [qryComponent_price].part_no) " & _
          "SET [Internal Rejections Table].Price = [qryComponent_price].price " & _
          "WHERE (([Internal Rejections Table].ID)=" & Me.ID.Value & ");"
 

[qryComponent_price] is the UNION query
[Internal Rejections Table] is the underlying table my form is based on
Me.ID.Value is the Autonumber of the current record
The current record is saved before running the action query.

The VBA code produces the error: Too few parameters. Expected 1.
When run directly in a query window: Operation must use an updateable query.

These errors point me to believe that my UNION query is not updateable, and is rendering my table read only, in this instance.

Is there another way to achieve my goal in a single step?

(I have considered creating a temporary table, but that feels excessive, alternatively I could run a separate query from each table (supply and manufacture) and handle an empty record set, again overkill).

[Rabbit]

Try DLookup instead of an INNER JOIN.

[kepston]

Try DLookup instead of an INNER JOIN.

Thanks Rabbit, that worked a treat.
Dlookup is a new one on me! I will remember that for future, in fact I may be able to improve some previous projects too.

Cheers!

[Rabbit]

Not a problem.

[NeoPa]

Be very careful of using DLookup() (or any other Domain Aggregate functions) from within SQL. The performance hit can be extreme (Forget IIf() etc - DLookup executes a query within each record of the query). In some cases it can be the only way to achieve a result. In others, performance is so not an issue that it won't matter.
Without seeing the SQL of the UNION query it's hard to produce a version that would work without the DLookup (and sometimes Access can be VERY restrictive about what it allows the SQL to do), so if performance is no issue, use the DLookup for this one.
I would strongly recommend against considering it for general purpose usage though.

[kepston]

Be very careful of using DLookup() (or any other Domain Aggregate functions) from within SQL. The performance hit can be extreme (Forget IIf() etc - DLookup executes a query within each record of the query). In some cases it can be the only way to achieve a result. In others, performance is so not an issue that it won't matter.
Without seeing the SQL of the UNION query it's hard to produce a version that would work without the DLookup (and sometimes Access can be VERY restrictive about what it allows the SQL to do), so if performance is no issue, use the DLookup for this one.
I would strongly recommend against considering it for general purpose usage though.

I'll bear that in mind.
In this instance I required a single value, which I extracted using DLookup into a variable, and was then able to add the variable to the SQL code. This produced the correct result in less than a second, barely noticeable to the user.

The complex part of the UNION has three tables, two INNER JOINs and a GROUP BY, with a reference to the current record on the form.
I am assuming it is the aggregate that caused my nonupdateable recordset.

Thanks for your wisdom.

[NeoPa]

I'll bear that in mind.
In this instance I required a single value, which I extracted using DLookup into a variable, and was then able to add the variable to the SQL code. This produced the correct result in less than a second, barely noticeable to the user.

Certainly a good reason for not worrying about performance.

The complex part of the UNION has three tables, two INNER JOINs and a GROUP BY, with a reference to the current record on the form.
I am assuming it is the aggregate that caused my nonupdateable recordset.

I'm pretty sure that any UNION query would be treated as non-updatable in Access, regardless of complexity.

Thanks for your wisdom.

Nice. I like that. Thank you for the compliment, and I'm glad you got an answer that works for you.

[hiral265]

I have a problem with Access Forms. The record source of my Form is a UNION query on 2 tables.

Now, when I try to update any of the fileds on the Forms I cannot. I could do updates when I had record source query as just a SELECT on 1 table.

There might be something very stupid that I am missing. Please let me know if anybody has any suggestions.

Thanks,
Heeral

[NeoPa]

If you read through the post just before your one (#7), you will see :

I'm pretty sure that any UNION query would be treated as non-updatable in Access, regardless of complexity.

Obviously (I think), any form built on a non-updatable query would not be able to update the data.
Does this make sense?