Errors 3020 and 3197

[weightinwildcat]

am working on code to open one form using DoCmd.OpenForm from another form, using parameters as follows:

FormName - the actual name of the form.
View - in this case no value is passed, so the default acNormal is used, opening the form in Form view.
FilterName - the name of the other form's record source, in this case a multitable query.
WhereCondition - in this case, the filter is having a sales order number that matches a value in a text box on the calling form.
DataMode - in this case acFormEdit.
WindowMode - in this case acHidden.
OpenArgs - in this case no value is passed.

The calling then triggers a public subroutine in the form being called. The code is something like this:

Dim rs As Recordset
Set rs = Me.Recordset

If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
rs!ReqDate = ReqDate
rs.Update

rs.MoveNext
Loop
End If

There may also be calls to other subroutines on the second form to take advantage of logic that is already built in.

The problem is that some of the tables in the query are connected through a LEFT JOIN. When I try to do updates, I get error messages as follows:

3197 - The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time.
3020 - Update or CancelUpdate without AddNew or Edit.

If I try to run the same code a second time it works without any problems. However, the first time will cause an error unless there is already a matching record in the table connected by the LEFT JOIN.

Any thoughts on how to negate this issue?

Thank you in advance.

 

[Defactio]

I don't understand why you are delaying data updates until another form is opened.
Why not update records immediately as they are required, THEN allow a form that is being opened to simply display those changes?

Execute a data update using "currentdb.execute (updatequery)" exactly when it needs to be updated, THEN open a form and display the updated data.

It seems that a date field is being auto-populated with "ReqDate". Where does "ReqDate" come from?

ATB,

Def