How to change form to allow control of saving record changes?

[ncsthbell]

I have inherited an access database that has the forms designed so when a user makes a change in any column(text box), there is a bar on the left of the form and a pencil appears. If the user does not click on this pencil, the changes are not saved and there are no warning messages. We have found that lots of changes have been lost due to user's not clicking on the pencil. I have been asked to change the forms to put a 'Save record' button on the form. I also need to detect if they have made a change on the form and as soon as a change is made to set this new button to 'enabled'. They would like it to stay grayed out unless a change is made. If the user clicks on the 'x' button in the upper right corner to close the form or a existing button to take them back to the main menu, I want to check at the point if any data has been changed and display a message box prompting them if they want to save the changes.

I have done the following, changed the properties on the form for 'Record Selector' and 'Navigation Buttons' to 'No'.
I tried using the form event of 'Close' and put a test in for

If me.Dirty Then
   intAnswer = MsgBox("The Asset info has been modified.  Do you want to save your changes?", vbYesNoCancel)
End If
 

When it hits this code, the "me.Dirty" is "false". I was expecting it to be "true" since I made a data change.

I may be going about this all the wrong way. The columns on my form are 'bound'. Any suggestions on the best way to handle this?

Thanks for any ideas!

[TheSmileyCoder]

Your approach seems sound enough. What other code is currently on the form? Especially look for the FORMs BeforeUpdate event, as well as the forms Dirty event.

[ncsthbell]

Nothing is working correctly. I am not familiar with the form having a bar on the left side with a pencil showing up when a change it made. I need to figure out how this is done and how to get rid of it so I can control the save. I wonder if it is because the forms columns are 'bound'?

[NeoPa]

It may be because they're unbound. So many newbies seem to want to handle data saving on forms without using the way Access provides (Bound forms etc). Maybe posting the form's module code would be helpful in this case. There may be some clues in there for us. Can you also include whether the form and the record controls are actually all bound.

PS. Using the CODE tags should be easier than typing in the tags. There is a hash (#) type button in the post window that will do it for you for any selected text.

[ncsthbell]

All of the columns on the form are 'bound'. There is no code behind the form except for code that pertains to some combo boxes to handle 'not in list'. I want to detect when a data column value has been changed and capture the original value to store as history. I put code behind the 'before update' event of the data column and a 'stop' so I could look at the column value. The column value (txtDataCol1) contains the new value I just typed in. Since the columns are bound, does this mean it updates the table as soon as data is changed in a column? This seems like this should be an easy task, maybe I am over complicating it. Bottomline is all I want to to is detect when a column (bound) on the form has had a change made, and when it has, I want to write a history record to another table that contains the previous and the new column value.

[NeoPa]

Please post the code rather than describing it.

[TheSmileyCoder]

Your post 5 description of your problem doesn't really match your post 1 description. So I am a bit unsure what you want.

If you need to access the old value of a textbox, you can use the

txtDataCol1.OldValue

to get to that value.

Personally for history purposes I use a second table with the same columns as the first table. Then in the forms before update event, if it is not a NewRecord, I run code to append the current record to the history table. Thus each time the forms before Update event fires, a copy of all the old values are appended to a seperate table.

[ncsthbell]

This sounds like exactly what I need to do. I like the suggestion to store the record in a table and then append it to the history table. In the 'Forms before update' event code, how do I test to see if it is a new record or an update to the current record?

[TheSmileyCoder]

For bound forms there is a simple, Me.NewRecord which will be true for records that have not yet been inserted into the table.

Something like this for table tbl_Test with primary KEY KEY_Test, in a form where the key field is bound to textbox tb_KEY

Private Sub Form_BeforeUpdate(Cancel as Integer)
  If not Me.NewRecord Then
    Dim strSQL as string
    strSQL="INSERT INTO histTbl_Test " & _
           " SELECT * " & _
           "FROM tbl_Test" & _
           "WHERE tbl_Test.KEY_Test=" & Me.tb_KEY
 
    'Inline error handling
    On Error Resume Next
 
    'Runquery
    CurrentDB.Execute strSQL,dbfailonerror
 
    'Was query succesfull
    If Err.nr<>0 then
      'Update failed 
      MsgBox "Could not save record"
      Cancel=True
      Exit Sub
    End If
 
    'Resume Normal Error Handling
    On Error goto 0
    End If
End Sub

[ncsthbell]

Sorry, I apologize. I just re-read my post and I realize that I did kind of get off of the 1st post I made and made it a bit confusing. It all revolves around the same issue I was having, but should have been separate posts.
I am trying to figure out what the bar on the left of the form is that shows a 'pencil' once any data on the form has been changed. I don't want to display this bar with the pencil but instead put a button on the form for the user to select to 'save' the changes. Thus, was the reason I was trying to detect when a column was changed on the form. If they try to close the form after making a change, I wanted to detect it and prompt them to ask if they want to save the changes or cancel. However, I am confused about when the physical record is updated on the table since the columns are 'bound'. Is it updated when the data is entered in the column? Currently, the users click on the 'pencil' in the bar on the left to save the changes. I am not sure how to remove this bar and pencil.

[TheSmileyCoder]

The bar on the left is the Record Selector. If you look at the forms properties you can set the "Record Selectors" property to False/No, to turn of the bar on the left. You confused me a bit because you made it sound like you were talking about a custom control in the database you inherited. In fact its standard built in Access.

The pencil is indicating that changes have been made to the record, but the record has not yet been saved. However, Access will always save your record when you: Move away from the form, browse to a new record, close the form, close the database. Exceptions to this could be if the record entered does not full-fill any validation rules or some required fields are not filed out. (Or if you press Esc, which triggers the undo action and discards the changes)

However, you can modify the save behavior by looking at the Forms Before_Update event.

Individual columns are not updated seperate from the "whole". A record consists of several fields (or columns) and are updated "together".