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

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

Post by Qmx1ZVdvbH » Tue, 07 Oct 2008 23:42:02


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,
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!
 
 
 

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

Post by R29sZmlucm » Tue, 07 Oct 2008 23:56:02

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.

 
 
 

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

Post by Clif McIrv » Wed, 08 Oct 2008 00:06:18

"BlueWolverine" < XXXX@XXXXX.COM > wrote in


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.



--
Clif
Still learning Access 2003
 
 
 

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

Post by Qmx1ZVdvbH » Wed, 08 Oct 2008 01:16:04

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;
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!
 
 
 

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

Post by Clif McIrv » Wed, 08 Oct 2008 01:53:11

"BlueWolverine" < XXXX@XXXXX.COM > wrote in


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
Still learning Access 2003
 
 
 

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

Post by Qmx1ZVdvbH » Wed, 08 Oct 2008 02:18:16

etting fancy with the OR statements worked pretty well. I like that, thank
you.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


"Clif McIrvin" wrote:

 
 
 

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

Post by Linq Adams » Wed, 08 Oct 2008 02:25:24

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://www.yqcomputer.com/

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.yqcomputer.com/
 
 
 

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

Post by Clif McIrv » Wed, 08 Oct 2008 03:12:30

"BlueWolverine" < XXXX@XXXXX.COM > wrote in


Great!


--
Clif
Still learning Access 2003