Locking on Update Query

[Tom Brown]

I have a database that keeps track of credit applications. I am trying to
setup a 2 forms to pull up existing apps and edit them. The application
data
table is linked to an SQL Server 2000 table. I have 2 forms, one tied to
a macro which allows me to find the app I want to edit using several
criteria and sends the
details of the app I select to a temp table, then opens the edit form, which
is showing
only the app I want to edit. This works fine. However, when I make the
edits on
the edit form and press the save button, the query runs, tells me I am
updating 1 row,
then gives me the locking message asking me if I want to continue. When I
say Yes,
the query finishes but the edit does not go thru. I think the issue is with
the SQL
table. How can I get around this locking issue? Sample code below.

UPDATE tbl_ID INNER JOIN tblCreditApplicationInput ON (tbl_ID.Outlet =
tblCreditApplicationInput.[Outlet #]) AND (tbl_ID.[Date Rcvd] =
tblCreditApplicationInput.[Date Rcvd]) SET tblCreditApplicationInput.[Date
Rcvd] = tbl_Id.[Date Rcvd], tblCreditApplicationInput.[Date Completed] =
tbl_Id.[Date Completed], tblCreditApplicationInput.Status = tbl_ID.Status,
tblCreditApplicationInput.[Outlet #] = tbl_Id.Outlet,
tblCreditApplicationInput.[Existing Outlet #] = tbl_Id.[Existing Outlet],
tblCreditApplicationInput.[Type of Request] = tbl_ID.[Type Of Request],
tblCreditApplicationInput.[Corporate Name] = tbl_ID.[Corporate Name]

Thanks in advance for your help.

Tom

 

[John Spencer]

Does tbl_ID have a field that is type TimeStamp?
Does tbl_ID have a primary key?
Has the tblCreditApplicationInput record on the form been saved? Until the
record has been saved, the displayed values of the controls on the form are
different than the values stored in the table.

UPDATE tbl_ID INNER JOIN tblCreditApplicationInput
ON (tbl_ID.Outlet = tblCreditApplicationInput.[Outlet #])
AND (tbl_ID.[Date Rcvd] = tblCreditApplicationInput.[Date Rcvd])
SET tblCreditApplicationInput.[Date Rcvd] = tbl_Id.[Date Rcvd]
, tblCreditApplicationInput.[Date Completed] = tbl_Id.[Date Completed]
, tblCreditApplicationInput.Status = tbl_ID.Status
, tblCreditApplicationInput.[Outlet #] = tbl_Id.Outlet
, tblCreditApplicationInput.[Existing Outlet #] = tbl_Id.[Existing Outlet]
, tblCreditApplicationInput.[Type of Request] = tbl_ID.[Type Of Request]
, tblCreditApplicationInput.[Corporate Name] = tbl_ID.[Corporate Name]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[thor177]

Thanks, John

I do have a Primary Key on tbl_ID, however, I do not have a field that
is type
TimeStamp. Tbl_CreditApplicationInput is the table that is linked to
SQL
Server. That is the table that I am trying to update using the update
query.
Tbl_ID and Tbl_CreditApplicationInput have identical columns and data
types.
The form for choosing the app to edit uses tbl_ID as its record
source.
When the app to edit is chosen, the query puts the data from that app
in the tbl_ID table.
I then open the edit form which pulls the data in tbl_ID into the edit
form
and allows the user to edit any field on the form, which when the
command button is pushed
activates the update query to update the tbl_CreditApplicationInput
table. tbl_Credit
ApplicationInput is not taking the edit, it is locked somehow. I
don't know what you
mean by "is it saved." The table has over 19000 applications in it,
and in testing I am
the only user. Hope this helps.

Does tbl_ID have a field that is type TimeStamp?
Does tbl_ID have a primary key?
Has the tblCreditApplicationInput record on the form been saved? Until the
record has been saved, the displayed values of the controls on the form are
different than the values stored in the table.

UPDATE tbl_ID INNER JOIN tblCreditApplicationInput
ON (tbl_ID.Outlet = tblCreditApplicationInput.[Outlet #])
AND (tbl_ID.[Date Rcvd] = tblCreditApplicationInput.[Date Rcvd])
SET tblCreditApplicationInput.[Date Rcvd] = tbl_Id.[Date Rcvd]
, tblCreditApplicationInput.[Date Completed] = tbl_Id.[Date Completed]
, tblCreditApplicationInput.Status = tbl_ID.Status
, tblCreditApplicationInput.[Outlet #] = tbl_Id.Outlet
, tblCreditApplicationInput.[Existing Outlet #] = tbl_Id.[Existing Outlet]
, tblCreditApplicationInput.[Type of Request] = tbl_ID.[Type Of Request]
, tblCreditApplicationInput.[Corporate Name] = tbl_ID.[Corporate Name]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have a database that keeps track of credit applications. I am trying to
setup a 2 forms to pull up existing apps and edit them. The application
data
table is linked to an SQL Server 2000 table. I have 2 forms, one tied to
a macro which allows me to find the app I want to edit using several
criteria and sends the
details of the app I select to a temp table, then opens the edit form,
which is showing
only the app I want to edit. This works fine. However, when I make the
edits on
the edit form and press the save button, the query runs, tells me I am
updating 1 row,
then gives me the locking message asking me if I want to continue. When I
say Yes,
the query finishes but the edit does not go thru. I think the issue is
with the SQL
table. How can I get around this locking issue? Sample code below.

UPDATE tbl_ID INNER JOIN tblCreditApplicationInput ON (tbl_ID.Outlet =
tblCreditApplicationInput.[Outlet #]) AND (tbl_ID.[Date Rcvd] =
tblCreditApplicationInput.[Date Rcvd]) SET tblCreditApplicationInput.[Date
Rcvd] = tbl_Id.[Date Rcvd], tblCreditApplicationInput.[Date Completed] =
tbl_Id.[Date Completed], tblCreditApplicationInput.Status = tbl_ID.Status,
tblCreditApplicationInput.[Outlet #] = tbl_Id.Outlet,
tblCreditApplicationInput.[Existing Outlet #] = tbl_Id.[Existing Outlet],
tblCreditApplicationInput.[Type of Request] = tbl_ID.[Type Of Request],
tblCreditApplicationInput.[Corporate Name] = tbl_ID.[Corporate Name]

Thanks in advance for your help.

Tom- Hide quoted text -

- Show quoted text -

 

[John Spencer]

First my error.
I should have said does tbl_CreditApplicationInput have a timestamp field
and a primary key. To update a linked table on an SQL Server using ODBC,
the SQL server table needs a timestamp field. If you don't have the
timestamp field then I don't think you can update the record.

When the button is pushed on the form, you need to save the changes to the
record in tbl_ID. The easiest way I know to do this is to use the line of
code

If Me.Dirty = True Then Me.Dirty = False

That line checks to see if there are any unsaved changes to the record the
form is bound to. If so, it forces the changes to be written to the table.

That said, I am guessing that you have a specific reason for not working
directly with the data on the SQL server using the linked table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks, John

I do have a Primary Key on tbl_ID, however, I do not have a field that
is type
TimeStamp. Tbl_CreditApplicationInput is the table that is linked to
SQL
Server. That is the table that I am trying to update using the update
query.
Tbl_ID and Tbl_CreditApplicationInput have identical columns and data
types.
The form for choosing the app to edit uses tbl_ID as its record
source.
When the app to edit is chosen, the query puts the data from that app
in the tbl_ID table.
I then open the edit form which pulls the data in tbl_ID into the edit
form
and allows the user to edit any field on the form, which when the
command button is pushed
activates the update query to update the tbl_CreditApplicationInput
table. tbl_Credit
ApplicationInput is not taking the edit, it is locked somehow. I
don't know what you
mean by "is it saved." The table has over 19000 applications in it,
and in testing I am
the only user. Hope this helps.

Does tbl_ID have a field that is type TimeStamp?
Does tbl_ID have a primary key?
Has the tblCreditApplicationInput record on the form been saved? Until
the
record has been saved, the displayed values of the controls on the form
are
different than the values stored in the table.

UPDATE tbl_ID INNER JOIN tblCreditApplicationInput
ON (tbl_ID.Outlet = tblCreditApplicationInput.[Outlet #])
AND (tbl_ID.[Date Rcvd] = tblCreditApplicationInput.[Date Rcvd])
SET tblCreditApplicationInput.[Date Rcvd] = tbl_Id.[Date Rcvd]
, tblCreditApplicationInput.[Date Completed] = tbl_Id.[Date Completed]
, tblCreditApplicationInput.Status = tbl_ID.Status
, tblCreditApplicationInput.[Outlet #] = tbl_Id.Outlet
, tblCreditApplicationInput.[Existing Outlet #] = tbl_Id.[Existing
Outlet]
, tblCreditApplicationInput.[Type of Request] = tbl_ID.[Type Of Request]
, tblCreditApplicationInput.[Corporate Name] = tbl_ID.[Corporate Name]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have a database that keeps track of credit applications. I am trying
to
setup a 2 forms to pull up existing apps and edit them. The
application
data
table is linked to an SQL Server 2000 table. I have 2 forms, one tied
to
a macro which allows me to find the app I want to edit using several
criteria and sends the
details of the app I select to a temp table, then opens the edit form,
which is showing
only the app I want to edit. This works fine. However, when I make
the
edits on
the edit form and press the save button, the query runs, tells me I am
updating 1 row,
then gives me the locking message asking me if I want to continue.
When I
say Yes,
the query finishes but the edit does not go thru. I think the issue is
with the SQL
table. How can I get around this locking issue? Sample code below.

UPDATE tbl_ID INNER JOIN tblCreditApplicationInput ON (tbl_ID.Outlet =
tblCreditApplicationInput.[Outlet #]) AND (tbl_ID.[Date Rcvd] =
tblCreditApplicationInput.[Date Rcvd]) SET
tblCreditApplicationInput.[Date
Rcvd] = tbl_Id.[Date Rcvd], tblCreditApplicationInput.[Date Completed]
=
tbl_Id.[Date Completed], tblCreditApplicationInput.Status =
tbl_ID.Status,
tblCreditApplicationInput.[Outlet #] = tbl_Id.Outlet,
tblCreditApplicationInput.[Existing Outlet #] = tbl_Id.[Existing
Outlet],
tblCreditApplicationInput.[Type of Request] = tbl_ID.[Type Of Request],
tblCreditApplicationInput.[Corporate Name] = tbl_ID.[Corporate Name]

Thanks in advance for your help.

Tom- Hide quoted text -

- Show quoted text -