Getting message when modifying

[Maracay]

Hi Guys,

I am getting this message when I am modifying the data, there is no way
someone else is using the file because I haven’t split the data base yet.
I will really appreciate if someone may help me with this.

“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â€

Press OK

This is the code

Private Sub CommSaveData_Click()
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb

Set rs = db.OpenRecordset( _
"select MaterialNumber, MaterialData, MaterialDataComments,
MaterialInk, MaterialInkComments, " & _
"MaterialPoly, MaterialPolyComments, MaterialVideo,
MaterialVideoComments, " & _
"MaterialLake, MaterialLakeComments, MaterialTabbing,
MaterialTabbingComments, " & _
"MaterialCollater, MaterialCollaterComments,
MaterialInsMaster, MaterialInsMasterComments, " & _
"MaterialInsReg, MaterialInsRegComments, MaterialLaser,
MaterialLaserComments, " & _
"MaterialFolding, MaterialFoldingComments, MaterialManual,
MaterialManualComments, " & _
"MaterialOther, MaterialOtherComments, MaterialStartDate,
MaterialDropDate, MaterialComments, MaterialQuantity " & _
"from tblDMaterial " & _
"where MaterialNumber = " & Me.TextMaterNumb & " and
MaterialSequence = " & Me.TextSequenceNumber)

If rs.RecordCount = 1 Then
'Nota tengo que grabar en tblMMaterial

' Save Material tblDMaterial
rs.Edit
' rs!MaterialNumber = Me.TextMaterialNumber
rs!MaterialStartDate = Me.TextStartDate
rs!MaterialDropDate = Me.TextDropDate
rs!MaterialComments = Me.TextMaterialNotes
rs!MaterialQuantity = Me.TextQuantity

rs!MaterialData = Me.CheckData
rs!MaterialDataComments = Me.TextDataComments
rs!MaterialInk = Me.CheckInkjet
rs!MaterialInkComments = Me.TextInkjetComments
rs!MaterialPoly = Me.CheckPoly
rs!MaterialPolyComments = Me.TextPolyComments
rs!MaterialVideo = Me.CheckVideo
rs!MaterialVideoComments = Me.TextVideoComments
rs!MaterialLake = Me.CheckLake
rs!MaterialLakeComments = Me.TextLakeComments
rs!MaterialTabbing = Me.CheckTabbing
rs!MaterialTabbingComments = Me.TextTabbingComments
rs!MaterialCollater = Me.CheckCollater
rs!MaterialCollaterComments = Me.TextCollaterComments
rs!MaterialInsMaster = Me.CheckInsMaster
rs!MaterialInsMasterComments = Me.TextInsMasterComments
rs!MaterialInsReg = Me.CheckInsReg
rs!MaterialInsRegComments = Me.TextInsRegComments
rs!MaterialLaser = Me.CheckLaser
rs!MaterialLaserComments = Me.TextLaserComments
rs!MaterialFolding = Me.CheckFolding
rs!MaterialFoldingComments = Me.TextFoldingComments
rs!MaterialManual = Me.CheckManual
rs!MaterialManualComments = Me.TextManualComments
rs!MaterialOther = Me.CheckOther
rs!MaterialOtherComments = Me.TextOtherComments
rs.Update
MsgBox " Record was updated "
Else
MsgBox " Record no found "
End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

 

[Daryl S]

Maracay -

When you use SQL to update the database, the form does not automatically
refresh. You will want to add a Me.Requery to the end of your code so that
what you put into the table is now reflected on the form, and Access won't
give you that message.

Are you trying to prevent new records from being added? Is that why you are
going to all this trouble instead of just letting Access save the record
normally? If you want to save all the changes on the form to the table, you
could just use a Me.Dirty = False statement instead. Or you could use a
DLookup or DCount to see if the record exists, and if so, then do a Me.Dirty
= False, and if not, give your error message.

 

[Maracay]

Thank you, Me.Requery works perfect, the reason I did it like that, creating
the text boxes and updating the record set, is because some times I am using
more than one table in the form and I tried to create a query to have it all
in 1 record source but I couldn’t modify the data because the query contain
more than 1 table. That why I decide to do it like that.

But definitely I would like to try your approach.

Thanks