Query no longer updatable

[Rose B]

I have a form, based upon a query, which used to update OK but somewhere
along the line it has stopped being updatable and I cannot work out why. Can
anyone help? The SQL generated is...

SELECT ExpenseDtl.ExpenseID, ExpenseDtl.ActivityDate, ExpenseDtl.Client,
ExpenseDtl.Activity, ExpenseDtl.ActivityTime, ExpenseDtl.TravelTime,
ExpenseDtl.JobID, ExpenseDtl.Description, ExpenseDtl.Miles,
ExpenseDtl.Expense, ExpenseDtl.OverrideInv, Trip.MileageOut,
Trip.OtherExpenses, VolServices.ServiceInvMiles, VolServices.ServiceInvExps,
Trip.BFGroupTripID, ExpenseDtl.DetailNo
FROM (ExpenseDtl LEFT JOIN Trip ON ExpenseDtl.JobID = Trip.TripID) LEFT JOIN
VolServices ON ExpenseDtl.Activity = VolServices.ServiceType
WHERE (((Trip.BFGroupTripID)=0 Or (Trip.BFGroupTripID) Is Null))
ORDER BY ExpenseDtl.DetailNo;

 

[Jerry Whittle]

This is a very complicated subject. I suggest that you open up Access Help,
go to the Find tab, type in "updatable" (lower case u) or "About updating
data", and then scroll down to "When can I update data from a query?" or "Why
can't I edit data in my form?". There you will find a lot of, possibly too
much, information on the subject. In a nutshell, if the query is based on one
table or tables with a one-to-one relationship, you will be able to edit or
delete records. If it is based on two or more tables with a one-to-many
relationship, you 'should' be able to edit or delete records. If you have
three or more tables based on many-to-one-to-many relationships, you will not
be able to edit or delete records. This is just the highlights. Help has much
more information.

Looking at your SQL is see a Left Join that could stop a query from being
updateable. Also go to the Relationships Window and see if there is a
relationship defined between those two tables. If so see if you can get
Referential Integrity to work on the relationship. If not it's doubtful if
you can make it an updateable query.

One way to fix the problem is a form based on the main table and subforms
based on the child tables.

 

[Roger Carlson]

It's not always possible to tell why a query is non-updateable just from the
SQL. For one thing, the join field on the "one-side" of the Join has to
either be the primary key or have a unique index. In your code, that would
be ExpenseDtl.JobID and ExpenseDtl.Activity. If I were to guess, I'd say
that ExpenseDtl.Activity is your culprit. But that's just a guess. Other
things can influence the updateablility of query. You can find a list in
your Help System. Search on "When can I update data from a query?"

But I rarely base forms on queries with Joins. Usually, I use forms and
subforms. The form is based on the table on the "one-side" of the
relationship and the subform is based on the table on the "many-side".

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Rose B]

Thanks for your response....I have indeed been over-whelmed by text in the
Help!!! At the moment there are no relationships defined between the 3 tables
in question. The main table I am getting information from (ExpenseDtl) I have
linked this table only via the Query to Trip and VolServices. In each case I
have said to show all records in ExpenseDtl and only where fields match in
the other two. The field that I have chosen in each case should be a 1:1
relationship (e.g. Job no in ExpnseDtl will only match with one TripID (which
is in fact the key of that table).

The query is being used to populate a subform already, so I didn't really
want a subform within a subform. At some point yetserday I had the thing
working almost as I wanted but then the updating bit must have stopped
working at some point and I am striggling to re-trace my steps.

Not sure if the additional info gives you any more clues, but appreciate
your help.

 

[Rose B]

OK - I have taken your advice and re-thought my whole approach! Some of the
fields (from VolServices) that I just wanted to display I created another
subform. For those fields I wanted to update (in Trip) I have use SQL in VB
via an event. All seems to be OK - I think! Thanks again