LOCK violation error when attempting to run UPDATE query

[FuzzyBear9]

Hi,

I am relatively new to Access 97. I have created a button on a query-based form that updates specific values for ALL records stored in the query to the underyling table.

I have used the database splitter and have placed the tables on a shared network, with users running the front-end applicationon on their terminals.

PROBLEM:
When I press the button on the query-based form, Access returns an error message "Microsoft Access can't update all the records in the update query....due to lock violations".

If I run the query from the 'query' window, Access displays the same message, but then updates the records on the second go.

I have tried placing the tables back with the front-end application, but Access still produces the same error.

Any ideas?

 

[tlbroadbent]

It is difficult to give a definitive answer without more info. Is the form bound to the query? What is the VB code in the click event?

I will venture a guess that the form is bound to the query and the user updates fields on the form. The program then attempts to update the underlying table with an Update query.

If this is the case, then what is happening is that the records are locked because they are updated on the form. When the button is clicked, the query attempts to update the locked records. Issuing an update query in this scenario is redundant and unnecessary. The updates on the form are automatically performed on the underlying table.

If I've guessed wrong, please help me to understand your process a little better. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[FuzzyBear9]

Thanks for responding so quickly. What you say makes sense. The need to include a button to update records only came about because the query failed to update one particular field in the underlying table.

PROBLEM:
The query-based form is based on an auto-lookup query where information about a client is stored. When a new record is created the PROJECT CONSULTANT and END DATE fields are automatically filled in when a user selects a SURVEY.

However, the corresponding END DATE field in the underlying table is left blank. The only thing I could think of was to place a button on the form to run an update to the table.

If I could fix the issue with the END DATE field then I could remove the need for any button.

Any ideas?

 

[tlbroadbent]

Could you set a default value for the End Date in the table design mode? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[FuzzyBear9]

The End Date varies according to the Survey selected:

Example:

Survey End Date
TCM 12/08/02
PIP 11/09/02
ACU 06/05/02
MME 23/11/02

As far as I am aware I can only specify one default value, unless there is a way to write conditions. (But then I will have to write a new condition for each new survey).

Thanks for your help again.

 

[hayo]

I still do not understand, where the problem occurs.

You write "When a new record is created the PROJECT CONSULTANT and END DATE fields are automatically filled in when a user selects a SURVEY."

How do you fill these two fields ? You may use a click event for the survey-selection? But then you must be able to set the ENDDATE value ?

Maybe you could explain this step more precisely.

 

[FuzzyBear9]

Both the CONSULTANT and ENDDATE fields are filled automatically by using a query-based form (where the query is an AUTOLOOKUP query).

Any ideas?