Update Form Problem

[Bill Phillips]

I am using a form to update inventory records. On the form I indicate the
inventory ticket # and the batch. The Ticket# uses a combo box for selection
and the batch uses another combo box. I then use the following code to go to
the appropriate record:

Set rs = Me.Recordset.Clone
lintRecountID = Me.cboBatch.Column(2)

rs.FindFirst "[RecountID] = " & lintRecountID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I enter an inventory count and update a timestamp field, click a command
button which performs a SQL update statement as follows:

strSQL = "Update tblRecountDetail" & _
" SET tblRecountDetail.InvReCount = " & lintTotCt & ",
tblRecountDetail.EntTimeStamp = #" & Now & "#, tblRecountDetail.EnterDt = #"
& Now & "#, tblRecountDetail.EnterTm = #" & Now & "# " & _
" WHERE (tblRecountDetail.TicketNo = " & rs1!TicketNo & "
AND tblRecountDetail.RectBatch = " & rs1!RectBatch & ")"
CurrentDb.Execute strSQL, dbFailOnError


The problem is that when I go back to the Ticket number combo box I recieve
the following error message:

The Data has been changed. Another user edited this record and saved the
changes before you attempted to save your changes. Re-edit the record.

I amusing Access 2007, but the db is in Access 2003 format. There are no
other users on this db, I am the only person working on the development side.
If I click OK on the error message, I can go back into the table that has
been updated and the information i added through the form has been updated.

Thanks,

 

[Jeanette Cunningham]

Hi Bill,
a couple of suggestions.
When using FindFirst, use NoMatch instead of EOF

rs.FindFirst "[RecountID] = " & lintRecountID
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

With the sql statement, avoid using the rs and just use the values from the
form.

" WHERE (tblRecountDetail.TicketNo = " & Me.TicketNo & "
AND tblRecountDetail.RectBatch = " & Me.RectBatch & ")"
CurrentDb.Execute strSQL, dbFailOnError

Change Me.TicketNo and Me.RectBatch to the correct names for your form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Bill Phillips]

Jeanette,

Thanks for the tips. However they did not prevent the "Data has been
changed" dialog box from appearing. It seems like the somewhere in the
process from Access's point of view, the record is not being fully written. I
have used other find & updates similar to this so I am at a loss as to what
the probelm is.

Hi Bill,
a couple of suggestions.
When using FindFirst, use NoMatch instead of EOF

rs.FindFirst "[RecountID] = " & lintRecountID
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

With the sql statement, avoid using the rs and just use the values from the
form.

" WHERE (tblRecountDetail.TicketNo = " & Me.TicketNo & "
AND tblRecountDetail.RectBatch = " & Me.RectBatch & ")"
CurrentDb.Execute strSQL, dbFailOnError

Change Me.TicketNo and Me.RectBatch to the correct names for your form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I am using a form to update inventory records. On the form I indicate the
inventory ticket # and the batch. The Ticket# uses a combo box for
selection
and the batch uses another combo box. I then use the following code to go
to
the appropriate record:

Set rs = Me.Recordset.Clone
lintRecountID = Me.cboBatch.Column(2)

rs.FindFirst "[RecountID] = " & lintRecountID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I enter an inventory count and update a timestamp field, click a command
button which performs a SQL update statement as follows:

strSQL = "Update tblRecountDetail" & _
" SET tblRecountDetail.InvReCount = " & lintTotCt & ",
tblRecountDetail.EntTimeStamp = #" & Now & "#, tblRecountDetail.EnterDt =
#"
& Now & "#, tblRecountDetail.EnterTm = #" & Now & "# " & _
" WHERE (tblRecountDetail.TicketNo = " & rs1!TicketNo & "
AND tblRecountDetail.RectBatch = " & rs1!RectBatch & ")"
CurrentDb.Execute strSQL, dbFailOnError


The problem is that when I go back to the Ticket number combo box I
recieve
the following error message:

The Data has been changed. Another user edited this record and saved the
changes before you attempted to save your changes. Re-edit the record.

I amusing Access 2007, but the db is in Access 2003 format. There are no
other users on this db, I am the only person working on the development
side.
If I click OK on the error message, I can go back into the table that has
been updated and the information i added through the form has been
updated.

Thanks,

 

[Jeanette Cunningham]

The error that you describe comes about when the same table is open in 2
different forms at the same time and in both places it can be edited at the
same time. For example if one of the forms was opened acDialog, or if one of
the forms was read only, you wouldn't see the error.

If the table you are updating via the query is part of the record source of
your form, this could be the cause of the error.

Does this help?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

Thanks for the tips. However they did not prevent the "Data has been
changed" dialog box from appearing. It seems like the somewhere in the
process from Access's point of view, the record is not being fully
written. I
have used other find & updates similar to this so I am at a loss as to
what
the probelm is.

Hi Bill,
a couple of suggestions.
When using FindFirst, use NoMatch instead of EOF

rs.FindFirst "[RecountID] = " & lintRecountID
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

With the sql statement, avoid using the rs and just use the values from
the
form.

" WHERE (tblRecountDetail.TicketNo = " & Me.TicketNo & "
AND tblRecountDetail.RectBatch = " & Me.RectBatch & ")"
CurrentDb.Execute strSQL, dbFailOnError

Change Me.TicketNo and Me.RectBatch to the correct names for your form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I am using a form to update inventory records. On the form I indicate
the
inventory ticket # and the batch. The Ticket# uses a combo box for
selection
and the batch uses another combo box. I then use the following code to
go
to
the appropriate record:

Set rs = Me.Recordset.Clone
lintRecountID = Me.cboBatch.Column(2)

rs.FindFirst "[RecountID] = " & lintRecountID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I enter an inventory count and update a timestamp field, click a
command
button which performs a SQL update statement as follows:

strSQL = "Update tblRecountDetail" & _
" SET tblRecountDetail.InvReCount = " & lintTotCt & ",
tblRecountDetail.EntTimeStamp = #" & Now & "#, tblRecountDetail.EnterDt
=
#"
& Now & "#, tblRecountDetail.EnterTm = #" & Now & "# " & _
" WHERE (tblRecountDetail.TicketNo = " & rs1!TicketNo &
"
AND tblRecountDetail.RectBatch = " & rs1!RectBatch & ")"
CurrentDb.Execute strSQL, dbFailOnError


The problem is that when I go back to the Ticket number combo box I
recieve
the following error message:

The Data has been changed. Another user edited this record and saved
the
changes before you attempted to save your changes. Re-edit the record.

I amusing Access 2007, but the db is in Access 2003 format. There are
no
other users on this db, I am the only person working on the development
side.
If I click OK on the error message, I can go back into the table that
has
been updated and the information i added through the form has been
updated.

Thanks,

 

[Bill Phillips]

Jeanette,

The form definitely does not have more than one instance open. However, I
think the record source point you raised may be part of the issue. I have a
qrery populating this form that incorporates 2 tables. I would like to use
the findfirst functionality but I will write a looping procedure if that is
the only way to update the table.

Any thoughts?

Thanks,

Bill

The error that you describe comes about when the same table is open in 2
different forms at the same time and in both places it can be edited at the
same time. For example if one of the forms was opened acDialog, or if one of
the forms was read only, you wouldn't see the error.

If the table you are updating via the query is part of the record source of
your form, this could be the cause of the error.

Does this help?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

Thanks for the tips. However they did not prevent the "Data has been
changed" dialog box from appearing. It seems like the somewhere in the
process from Access's point of view, the record is not being fully
written. I
have used other find & updates similar to this so I am at a loss as to
what
the probelm is.

Hi Bill,
a couple of suggestions.
When using FindFirst, use NoMatch instead of EOF

rs.FindFirst "[RecountID] = " & lintRecountID
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

With the sql statement, avoid using the rs and just use the values from
the
form.

" WHERE (tblRecountDetail.TicketNo = " & Me.TicketNo & "
AND tblRecountDetail.RectBatch = " & Me.RectBatch & ")"
CurrentDb.Execute strSQL, dbFailOnError

Change Me.TicketNo and Me.RectBatch to the correct names for your form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I am using a form to update inventory records. On the form I indicate
the
inventory ticket # and the batch. The Ticket# uses a combo box for
selection
and the batch uses another combo box. I then use the following code to
go
to
the appropriate record:

Set rs = Me.Recordset.Clone
lintRecountID = Me.cboBatch.Column(2)

rs.FindFirst "[RecountID] = " & lintRecountID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I enter an inventory count and update a timestamp field, click a
command
button which performs a SQL update statement as follows:

strSQL = "Update tblRecountDetail" & _
" SET tblRecountDetail.InvReCount = " & lintTotCt & ",
tblRecountDetail.EntTimeStamp = #" & Now & "#, tblRecountDetail.EnterDt
=
#"
& Now & "#, tblRecountDetail.EnterTm = #" & Now & "# " & _
" WHERE (tblRecountDetail.TicketNo = " & rs1!TicketNo &
"
AND tblRecountDetail.RectBatch = " & rs1!RectBatch & ")"
CurrentDb.Execute strSQL, dbFailOnError


The problem is that when I go back to the Ticket number combo box I
recieve
the following error message:

The Data has been changed. Another user edited this record and saved
the
changes before you attempted to save your changes. Re-edit the record.

I amusing Access 2007, but the db is in Access 2003 format. There are
no
other users on this db, I am the only person working on the development
side.
If I click OK on the error message, I can go back into the table that
has
been updated and the information i added through the form has been
updated.

Thanks,

 

[Bill Phillips]

OK, I found a solution or at least a workaround. I simply refreshed the form.
The last step in my VBA prior to using a setfocus was simply Me.Requery.
Apparently that will fianlize the save operation because I am no longer
getting the Data has been changed error.

Jeanette,

The form definitely does not have more than one instance open. However, I
think the record source point you raised may be part of the issue. I have a
qrery populating this form that incorporates 2 tables. I would like to use
the findfirst functionality but I will write a looping procedure if that is
the only way to update the table.

Any thoughts?

Thanks,

Bill

The error that you describe comes about when the same table is open in 2
different forms at the same time and in both places it can be edited at the
same time. For example if one of the forms was opened acDialog, or if one of
the forms was read only, you wouldn't see the error.

If the table you are updating via the query is part of the record source of
your form, this could be the cause of the error.

Does this help?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

Thanks for the tips. However they did not prevent the "Data has been
changed" dialog box from appearing. It seems like the somewhere in the
process from Access's point of view, the record is not being fully
written. I
have used other find & updates similar to this so I am at a loss as to
what
the probelm is.



:

Hi Bill,
a couple of suggestions.
When using FindFirst, use NoMatch instead of EOF

rs.FindFirst "[RecountID] = " & lintRecountID
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

With the sql statement, avoid using the rs and just use the values from
the
form.

" WHERE (tblRecountDetail.TicketNo = " & Me.TicketNo & "
AND tblRecountDetail.RectBatch = " & Me.RectBatch & ")"
CurrentDb.Execute strSQL, dbFailOnError

Change Me.TicketNo and Me.RectBatch to the correct names for your form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I am using a form to update inventory records. On the form I indicate
the
inventory ticket # and the batch. The Ticket# uses a combo box for
selection
and the batch uses another combo box. I then use the following code to
go
to
the appropriate record:

Set rs = Me.Recordset.Clone
lintRecountID = Me.cboBatch.Column(2)

rs.FindFirst "[RecountID] = " & lintRecountID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I enter an inventory count and update a timestamp field, click a
command
button which performs a SQL update statement as follows:

strSQL = "Update tblRecountDetail" & _
" SET tblRecountDetail.InvReCount = " & lintTotCt & ",
tblRecountDetail.EntTimeStamp = #" & Now & "#, tblRecountDetail.EnterDt
=
#"
& Now & "#, tblRecountDetail.EnterTm = #" & Now & "# " & _
" WHERE (tblRecountDetail.TicketNo = " & rs1!TicketNo &
"
AND tblRecountDetail.RectBatch = " & rs1!RectBatch & ")"
CurrentDb.Execute strSQL, dbFailOnError


The problem is that when I go back to the Ticket number combo box I
recieve
the following error message:

The Data has been changed. Another user edited this record and saved
the
changes before you attempted to save your changes. Re-edit the record.

I amusing Access 2007, but the db is in Access 2003 format. There are
no
other users on this db, I am the only person working on the development
side.
If I click OK on the error message, I can go back into the table that
has
been updated and the information i added through the form has been
updated.

Thanks,