Data has been changed

[Guest]

Hello,

I have a form that is bound to a query and all fields on it have their
control source set to a field in the query. More than one person can be
editing a record at the same time and when the first user saves before the
second user saves the same record, when the second user does save they will
get the standard MS Access "Write Conflict" message which allows them to
'copy to clipboard' or 'drop changes'. This works fine.

However, I have a second form that is not bound to a query, but rather the
fields on the screen are dynamically set via sql code. These fields are also
contained in the query in the first form so I need to apply some 'multi user'
logic so data does not get overwritten and I want to use the same "Write
Conflict" interface that is used on the first form.

I've tried many things and this is as close as I come: I set the Record
Source of the form to a select statement containing one field that is updated
on both forms. I then update this field on the second form in the code. If
the first user has edited a field on another form, and the second user tries
to save the same record after, they will get the message 'Data has been
changed; Another user edited the record and saved the changes; Re-edit the
record'. The user only has to option to Debug. There is no option to 'Copy
to Clipboard'.

Does anyone know a way to invoke the "Write Conflict" logic on a form that
is not bound?

Thanks in advance for any advice,
Jackie

 

[Ken Snell [MVP]]

You're looking to spend a lot of time reinventing an existing feature.

Why not put controls on the form that are bound to the query's fields, make
those controls invisible, then use your code to write their values into the
unbound controls. Then you can use your code to copy the edited values back
into the invisible, bound controls, and then use the "built-in" write
conflict process.

 

[Guest]

Ken, thanks so much for your quick response.

I actually did just what you suggested. I did not include the primary key as
one of the invisible fields because that field did not change. However, when
the form was closed, a message displayed saying that my primary key could not
be null. So I added the primary key field as an invisible control and when
the form was closed, a message displayed saying that primary key could not be
duplicated.

Any ideas?

 

[Ken Snell [MVP]]

You're creating a new record with your form, not using the current record.

Create a form that is based on your query. Add controls (bound to the
fields) for all fields from the query to the form. Make them invisible. Be
sure that the form's Data Entry property is set to No, and the
AllowAdditions property is set to No.

Put additional controls, one for each field that the user can edit, on the
form. These controls should be unbound. Your form should copy the values
from the bound, invisible controls into these unbound controls during the
form's Load event. Users edit data in the unbound controls. Put a button on
the form that the user clicks when "done". This button runs code that copies
the values from the unbound controls into the bound controls and then saves
the record.

When you open the form, filter it so that it displays only the desired
record.

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Ken, thank you so much for the good advice.. I will definitely try this.

Jackie

 

[Guest]

Hi Ken, I know its been a while but I did bound the form to a query and I'm
doing as you suggested by displaying the data in unbound controls then
passing the data back to the invisible bound controls in the
Form.BeforeUpdate sub. However, I have some unbound fields on the screen
that I am applying a spell checker to.

Here is the code I am using:

Private Sub txtIntNotesAdd_LostFocus()

If Len(txtIntNotesAdd.Text) > 0 Then
txtIntNotesAdd.SelStart = 0
txtIntNotesAdd.SelLength = Len(txtIntNotesAdd.Text)
DoCmd.RunCommand acCmdSpelling
End If

End Sub

This code worked fine when the form was not bound to a query but now that
the form is bound, I get the following error message:

The Spelling Checker can't proced; Form data is not updateable. Form may be
based on query that isn't updateable or is only partly updateable.

Is there any way I can apply the Spell checker to this unbound field?

I'd appreciate any ideas.

JKro