trying to update a record.

[gcrutch70]

I am simply trying to update a record. I have 2 forms. One form "Open Forms" and another form "Close Forms". When the user clicks the close form button they are prompted to enter the FileNo that they want to close. Once they enter the FileNo the "Close Forms" form opens up with prefilled text boxes called FileNo, Status, Attorney, Dept. and Description. Then the 3 fields DateClosed, BoxNo and Room does not come in prefilled. the user enters data in the 3 that's not prefilled. Once the user enters the data in the 3 fields they click a button that says 'Update Record". The "Status" field changes from "Active" to "Closed". Now this all works but the problem is.....every now and then (randomly) when a record is closed, instead of changing the status from "active" to "closed" it just writes a new recored with the status "closed". Now I have 2 records with duplicate fields except status. It doesn't do it everytime, it just does it randomly and I can't figure out why.

here is my code

Me!Status = "Closed" ' Change the value
Me.Dirty = False ' Commit the change

MsgBox "File Successfully Closed."

Msg = "Do you want to close another file?"

Response = MsgBox(Msg, vbYesNo)
If Response = vbYes Then

DoCmd.Save
DoCmd.Close
DoCmd.OpenForm "Close File"
Else
DoCmd.Close
DoCmd.OpenForm "Legal Files Main"
End If

End Sub

 

[dhookom]

I would start by:

  [green]'DoCmd.Save this doesn't do anything[/green]
  DoCmd.Close [red]acForm, Me.Name[/red]
  DoCmd.OpenForm "Close File"
 Else
  DoCmd.Close [red]acForm, Me.Name[/red]
  DoCmd.OpenForm "Legal Files Main"
End If

Also, you really confuse use when you state "Close Forms" but your code states "Close File". Most of us that have been around for a while use strict naming conventions.

Duane
Hook'D on Access
MS Access MVP