Form has data locked - All settings set to "not locked"

[BlueWolverine]

Hello,
MS ACCESS 2003 on XP PRo.

I have a form fed by a query fed by a query fed by a query... basically it's
a bunch of nested queries. I thought that form based on this query chain
would back fill data backwards to the tables eventually.

So my question is:

Is there a way so that edits to the form data will propagate back to the
tables through multiple queries, or do i have to try and nest a million
queries in 1 SQL statement?

If I have to nest, could someone show me a simple example? I haven't nested
yet...

Thank you,

 

[Golfinray]

I think it will get back to your tables. Somethings I have seen information
become slightly modified when it has to go back through several queries,
especially yes/no fields.

 

[Clif McIrvin]

Hello,
MS ACCESS 2003 on XP PRo.

I have a form fed by a query fed by a query fed by a query...
basically it's
a bunch of nested queries. I thought that form based on this query
chain
would back fill data backwards to the tables eventually.

So my question is:

Is there a way so that edits to the form data will propagate back to
the
tables through multiple queries, or do i have to try and nest a
million
queries in 1 SQL statement?

Are *ALL* of your queries updateable? If any one (or more) of them are
not updateable then your form will be non-updateable.

I've seen references on this list to one of the MVP websites that has a
discussion on what makes a query non-updateable; I'm sorry I don't have
that link close at had to share.

From the database window open your final query in datasheet view and
watch your status bar -- if it says "non updatable" then that's what you
need to track down.

My personal feeling is that it's far easier to maintain, debug and
document nested saved queries than nest them into one gargantual SQL
statement.

 

[BlueWolverine]

My UNION Query is non-updateable. Is there any way to change this? My boss
is 99% sure that Union queries are just not-updateable.

Here's the SQL in case it matters.
SELECT q_FuelCardInfo_Assigned.FuelCardSource,
q_FuelCardInfo_Assigned.FuelCardProvider, q_FuelCardInfo_Assigned.FuelCardNo,
q_FuelCardInfo_Assigned.AccountNo,
q_FuelCardInfo_Assigned.t_FCHistory.Assigned,
q_FuelCardInfo_Assigned.Unassigned, q_FuelCardInfo_Assigned.VIN,
q_FuelCardInfo_Assigned.Location, q_FuelCardInfo_Assigned.Comments,
q_FuelCardInfo_Assigned.Active,
q_FuelCardInfo_Assigned.t_FuelCardInventory.Assigned,
q_FuelCardInfo_Assigned.DateAddedToINV, q_FuelCardInfo_Assigned.DateDeac,
q_FuelCardInfo_Assigned.DeacReason
FROM q_FuelCardInfo_Assigned
UNION ALL SELECT q_FuelCardInfo_LastAssignment.FuelCardSource,
q_FuelCardInfo_LastAssignment.FuelCardProvider,
q_FuelCardInfo_LastAssignment.FuelCardNo,
q_FuelCardInfo_LastAssignment.AccountNo,
q_FuelCardInfo_LastAssignment.t_FCHistory.Assigned,
q_FuelCardInfo_LastAssignment.Unassigned, q_FuelCardInfo_LastAssignment.VIN,
q_FuelCardInfo_LastAssignment.Location,
q_FuelCardInfo_LastAssignment.Comments, q_FuelCardInfo_LastAssignment.Active,
q_FuelCardInfo_LastAssignment.t_FuelCardInventory.Assigned,
q_FuelCardInfo_LastAssignment.DateAddedToINV,
q_FuelCardInfo_LastAssignment.DateDeac,
q_FuelCardInfo_LastAssignment.DeacReason
FROM q_FuelCardInfo_LastAssignment;

 

[Clif McIrvin]

My UNION Query is non-updateable. Is there any way to change this?
My boss
is 99% sure that Union queries are just not-updateable.

I believe your boss is correct.

Can you rework your two queries using "or" criteria and eliminate the
need for the union? I'm guessing that the two queries in your union are
coming from the same table...

Make a copy of one of your underlying queries and see if you can tweak
it using the "or" criteria in the design grid to add the results of your
second query.

One thing that I eventually learned is that if you are combining both
"and" criteria and "or" criteria all the "and" criteria need to be
repeated on each "or" line.

- Or - you can create a query with the "and" criteria" and use it as the
recordsource for a query defining the "or" conditions.

hth!
--
Clif

<snip>

 

[BlueWolverine]

Getting fancy with the OR statements worked pretty well. I like that, thank
you.

 

[Linq Adams via AccessMonster.com]

For once the boss is right! ;0)>

Here's the link to Allen Browne's list of thnigs that make a query Read-Only:

http://allenbrowne.com/ser-61.html

 

[Clif McIrvin]

Getting fancy with the OR statements worked pretty well. I like that,
thank
you.
Great!