Query not updateable

M

Molasses26

I have a form that uses a query linking 4 tables as it's data source. We are
trying to convert all of our data tables to an SQL server and now my form
won't allow me to edit the records any more. If I remove the reference to
the ISBillingData.Status and ISStatusOptions.StatusDescr fields and their
associated tables then the query becomes updatable again. Unfortunately I
NEED those two fields in the form.

The fields that I actually need to edit are Assignment.Comment and
Assignment.Complete.
I have tried various different Join methods and nothing seems to work.
The Redordset type is set to "Dynaset (Inconsistent Updates)"

I appreciate any help you might be able to give me!
----------------------------------------------------------
SELECT dbo_bs_billing_excp.qitm_num, dbo_bs_billing_excp.excp_cret_dt,
dbo_bs_billing_excp.excp_num, dbo_bs_billing_excp.ca_id,
dbo_bs_billing_excp.saa_id, dbo_bs_billing_excp.site_id,
dbo_bs_billing_excp.bill_cycle, dbo_bs_billing_excp.order_dt,
dbo_bs_billing_excp.order_type, dbo_bs_billing_excp.order_status,
dbo_bs_billing_excp.redirect_dt,
IIf([redirect_type]="I","IS",IIf([redirect_type]="c","Sr","")) AS RedirTo,
Assignment.Comment, Assignment.Complete, Assignment.user_id,
Assignment.RepName, dbo_bs_billing_excp.ebill, dbo_bs_billing_excp.que_desc,
Assignment.PullBill, Assignment.RedirOpt, Assignment.kwRefund,
Assignment.RedirID, ISBillingData.Status, ISStatusOptions.StatusDescr,
dbo_bs_billing_excp.resolved_dt, Trim([rate]) AS SvcPln,
dbo_bs_billing_excp.pullbill_dt, dbo_bs_billing_excp.pullbill_id,
dbo_bs_billing_excp.pullbill_rsn
FROM ((Assignment LEFT JOIN dbo_bs_billing_excp ON (Assignment.cret_dt =
dbo_bs_billing_excp.excp_cret_dt) AND (Assignment.qitm_num =
dbo_bs_billing_excp.qitm_num)) LEFT JOIN ISBillingData ON
(Assignment.qitm_num = ISBillingData.qitm_num) AND (Assignment.cret_dt =
ISBillingData.cret_dt)) LEFT JOIN ISStatusOptions ON ISBillingData.Status =
ISStatusOptions.StatusID
WHERE (((dbo_bs_billing_excp.resolved_dt) Is Null)) OR
(((ISBillingData.Status)<7) AND ((dbo_bs_billing_excp.resolved_dt) Is Not
Null))
ORDER BY dbo_bs_billing_excp.excp_cret_dt, dbo_bs_billing_excp.excp_num,
dbo_bs_billing_excp.site_id;
 
G

Golfinray

The underlying problem is that you have table data that is not editable.
Dynaset (inconsistent updates) will, in my experience, cause queries and
forms to not be editable. I have had to manipulate tables, such as using
union queries then taking that data and do a make-table query to get an
editable table. Sounds like the problem.
 
T

Todd

You may also wish to go to the design view of a query and right click in the
table pane choose properties, then with your cursor in the "Recordset Type"
field press F1. I found some information in there that may be the problem.
It does mention something about a project having an updatable snapshot
property among other info. I hope it helps, may be a long shot though.

Golfinray said:
The underlying problem is that you have table data that is not editable.
Dynaset (inconsistent updates) will, in my experience, cause queries and
forms to not be editable. I have had to manipulate tables, such as using
union queries then taking that data and do a make-table query to get an
editable table. Sounds like the problem.

Molasses26 said:
I have a form that uses a query linking 4 tables as it's data source. We are
trying to convert all of our data tables to an SQL server and now my form
won't allow me to edit the records any more. If I remove the reference to
the ISBillingData.Status and ISStatusOptions.StatusDescr fields and their
associated tables then the query becomes updatable again. Unfortunately I
NEED those two fields in the form.

The fields that I actually need to edit are Assignment.Comment and
Assignment.Complete.
I have tried various different Join methods and nothing seems to work.
The Redordset type is set to "Dynaset (Inconsistent Updates)"

I appreciate any help you might be able to give me!
----------------------------------------------------------
SELECT dbo_bs_billing_excp.qitm_num, dbo_bs_billing_excp.excp_cret_dt,
dbo_bs_billing_excp.excp_num, dbo_bs_billing_excp.ca_id,
dbo_bs_billing_excp.saa_id, dbo_bs_billing_excp.site_id,
dbo_bs_billing_excp.bill_cycle, dbo_bs_billing_excp.order_dt,
dbo_bs_billing_excp.order_type, dbo_bs_billing_excp.order_status,
dbo_bs_billing_excp.redirect_dt,
IIf([redirect_type]="I","IS",IIf([redirect_type]="c","Sr","")) AS RedirTo,
Assignment.Comment, Assignment.Complete, Assignment.user_id,
Assignment.RepName, dbo_bs_billing_excp.ebill, dbo_bs_billing_excp.que_desc,
Assignment.PullBill, Assignment.RedirOpt, Assignment.kwRefund,
Assignment.RedirID, ISBillingData.Status, ISStatusOptions.StatusDescr,
dbo_bs_billing_excp.resolved_dt, Trim([rate]) AS SvcPln,
dbo_bs_billing_excp.pullbill_dt, dbo_bs_billing_excp.pullbill_id,
dbo_bs_billing_excp.pullbill_rsn
FROM ((Assignment LEFT JOIN dbo_bs_billing_excp ON (Assignment.cret_dt =
dbo_bs_billing_excp.excp_cret_dt) AND (Assignment.qitm_num =
dbo_bs_billing_excp.qitm_num)) LEFT JOIN ISBillingData ON
(Assignment.qitm_num = ISBillingData.qitm_num) AND (Assignment.cret_dt =
ISBillingData.cret_dt)) LEFT JOIN ISStatusOptions ON ISBillingData.Status =
ISStatusOptions.StatusID
WHERE (((dbo_bs_billing_excp.resolved_dt) Is Null)) OR
(((ISBillingData.Status)<7) AND ((dbo_bs_billing_excp.resolved_dt) Is Not
Null))
ORDER BY dbo_bs_billing_excp.excp_cret_dt, dbo_bs_billing_excp.excp_num,
dbo_bs_billing_excp.site_id;
Click to expand...
Click to expand...
 
J

John W. Vinson

I have a form that uses a query linking 4 tables as it's data source.
Click to expand...

Well, that's a problem right there. SQL/Server multitable queries are not
editable - you'll need to write Triggers to let this happen.
We are
trying to convert all of our data tables to an SQL server and now my form
won't allow me to edit the records any more. If I remove the reference to
the ISBillingData.Status and ISStatusOptions.StatusDescr fields and their
associated tables then the query becomes updatable again. Unfortunately I
NEED those two fields in the form.
Click to expand...

Do you need to EDIT those two fields? or just display them? Could you use a
Combo Box to *display* the StatusDesrc field based on the StatusID, while
having only the StatusID as a bound field?
The fields that I actually need to edit are Assignment.Comment and
Assignment.Complete.
I have tried various different Join methods and nothing seems to work.
The Redordset type is set to "Dynaset (Inconsistent Updates)"
Click to expand...

Without knowing anything about the relationship of the tables, all I can
suggest is use of combo boxes and/or subforms to display the data from the
other tables while basing the form on the Assignment table (only).
 
Top