This record has been changed by another user since you started edi

[Design by Sue]

I am getting a message that states "This record has been changed by another
user since you started editing it..." I am the only user and believe I am
only making one change. To try to set the stage:

I am trying to create a form where the user can select a PartNumber-Suffix
combintation from the table, see where the Part currently is (Line) and
reassign it to another line.

I have a table, PartSuffixTbl in which there are fields PartNumber, Suffix,
and Line. I have created a query (Part-SuffixQuery) which has all of these
fields in it plus one to combine the PartNumber and Suffix with a dash
between them so they read in one field. In the Line field I have created a
Criteria to filter by a number in a field on the form (LineNumber - more
about this to follow). Now on my form I have a combo box showing the
combined PartNumber-Suffix field plus the PartNumber and Suffix fields as
hidden. I then have a text box to display the current Line which is linked
to the combo box just mentioned. I then have an unbound combo box
(LineNumber) linked to the table that lists all available line numbers
(LineTbl). I have another text box IDisplay) that uses the query
(Part-SuffixQuery) and the criteria in the line field to display all parts
that are currently assigned to a given line.

The way it works, the user selects a PartNumber-Suffix and the linked test
box shows where the part currently is. The use then enters a number in the
LineNumber box and clicks a button to change the line number to the one
entered in the LineNumber box. This also refreshes the from and displays all
parts assigned to the line shown in the LineNumber box in the Display text
box.

The code in the OnClick of the button is:
Private Sub UpdateRecordBtn_Click()
On Error GoTo Err_UpdateRecordBtn_Click

Dim SQL As String

SQL = "UPDATE [PartSuffixTbl] " & _
"SET [Line] = Forms![LocationFrm3]![LineNumber] " & _
"WHERE PartNumber = Forms![LocationFrm3]![PartNumber] and " & _
"Suffix = Forms![LocationFrm3]![Suffix]"


DoCmd.RunSQL SQL

Me.refresh

Exit_UpdateRecordBtn_Click:
Exit Sub

Err_UpdateRecordBtn_Click:
MsgBox Err.Description
Resume Exit_UpdateRecordBtn_Click

End Sub

Now for the problem. All works but I get an error message stating This
record has been changed by another user since you started editing it..." CAn
anyone tell from my lengthy (sorry) explination what is causing this error.
I am thinking it is because I am using the same query for the PartNumber/Line
fields and for the Display text box. Any thoughts greatly appreciated.

Sue

 

[Design by Sue]

Any while you might be looking can you tell me why the code I posted works
only randomly?

 

[Tony Toews [MVP]]

I am getting a message that states "This record has been changed by another
user since you started editing it..." I am the only user and believe I am
only making one change.

I was having trouble following your explanation but the typical
situation in which this error occurs when only one person is in the
database is that you are updating a record in one form and you open
another form in which you then attempt to update that same record.

You can see this in action by watching the record selector bar which
is on the left hand side of the form. Note that you must have the
Record Selectors option on the form properties set to Yes.

When you are viewing a record you will see a triangle pointing to the
right. When you have made some changes to the record on the form but
haven't written those to the database it changes to a pencil. When
you are prohibited from updating the record you will see a stop sign
there.

SQL = "UPDATE [PartSuffixTbl] " & _
"SET [Line] = Forms![LocationFrm3]![LineNumber] " & _
"WHERE PartNumber = Forms![LocationFrm3]![PartNumber] and " & _
"Suffix = Forms![LocationFrm3]![Suffix]"

I'd change that to

SQL = "UPDATE [PartSuffixTbl] " & _
"SET [Line] = " & Forms![LocationFrm3]![LineNumber] & _
"WHERE PartNumber = " & Forms![LocationFrm3]![PartNumber] &
" and " & _
"Suffix = " Forms![LocationFrm3]![Suffix]

DoCmd.RunSQL SQL

I'll offer a suggestion here in response to your next posting.

Me.refresh

Try me.requery instead of me.refresh.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Any while you might be looking can you tell me why the code I posted works
only randomly?

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Design by Sue]

Tony - thanks for you help but this didn't solve the problem. My first
question, in the other post you stated

"I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText "

ADO is good. Not being too good at this stuff, I tried to past the last
line in my code and the debugger stopped it. Were you giving me the code or
what did you mean by your statement?

Also after more work and diagnosis on this the reason this "doesn't work" is
because every time I click on the button the only record to be updated is the
first record in my PartSuffixTbl. It doesn't matter what I select in the
combo box, only the very first number gets changed to what every I have put
in the Line text box. Therefore I must have the code wrong to indicate the
selected item in the Combo box. So now my question is what is the correct
coding to indicate a selection made in a combo box that is based on the query
(Part-SuffixQuery) that is based on the PartSuffixTbl (reminded this query
joins two fields into one so the PartNumber-Suffix appears rather than 2
separate fields.)

I only work on Access about once a year and most times forget at least half
of what I learned the previous year, so please be understanding if I have not
expressed this correctly. All help truly appreciated.

I am getting a message that states "This record has been changed by another
user since you started editing it..." I am the only user and believe I am
only making one change.

I was having trouble following your explanation but the typical
situation in which this error occurs when only one person is in the
database is that you are updating a record in one form and you open
another form in which you then attempt to update that same record.

You can see this in action by watching the record selector bar which
is on the left hand side of the form. Note that you must have the
Record Selectors option on the form properties set to Yes.

When you are viewing a record you will see a triangle pointing to the
right. When you have made some changes to the record on the form but
haven't written those to the database it changes to a pencil. When
you are prohibited from updating the record you will see a stop sign
there.

SQL = "UPDATE [PartSuffixTbl] " & _
"SET [Line] = Forms![LocationFrm3]![LineNumber] " & _
"WHERE PartNumber = Forms![LocationFrm3]![PartNumber] and " & _
"Suffix = Forms![LocationFrm3]![Suffix]"

I'd change that to

SQL = "UPDATE [PartSuffixTbl] " & _
"SET [Line] = " & Forms![LocationFrm3]![LineNumber] & _
"WHERE PartNumber = " & Forms![LocationFrm3]![PartNumber] &
" and " & _
"Suffix = " Forms![LocationFrm3]![Suffix]

DoCmd.RunSQL SQL

I'll offer a suggestion here in response to your next posting.

Me.refresh

Try me.requery instead of me.refresh.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/