Access 2003 Dirty?? Event?

You can use this anytime up until a record is saved (ie: before you move to another record, etc):

If me.txtMyTextbox <> me.txtMyTextbox.OldValue then
      if( msgbox "Your answer changed! Save?", vbYesNo) = vbYes then
               etc...
       else
               etc...
       end if
end if

Select all Open in new window

I've described this a little in my article here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_10162-What-is-in-a-field-Value-vs-Text-and-the-differences-between-form-data-and-table-data.html

I think we need a clearer definition of what you are trying to do.

If you change the listbox selection at ANY time then you will automatically save the result.

I don't see how the afterupdate event of the textbox comes into the picture.

Ok folks...
In the screen print below
My listbox caries a list of the questionID, sort, question, Answer,Done
Only sort, question and Done are shown

In VBA when I click on the ListBox...
There is a label and textbox below the listbox
The label is set to the value in Me.lbvQuestionList.Column(6)
The textbox is set to the value in Me.lbvQuestionList.Column(7)

A person can type in new information to the textbox and update the answer with the "Save" button.

What I'm running into is people type in the new Answer...and then forget tosave...clicking on the next question in the listbox...thus resetting everything and losing what they typed.

So I want to promt them to "Save"
I'm struggling through mbzip's answer...can't seem to get it to work without prompts everywhere.

Screen Print


This is all my (development current) VBA

Option Compare Database

Private Sub lbvQuestionList_Click()
'    If Me.txtAnswer <> Me.lblOld.Caption Then
'        If MsgBox("Your answer changed! Save?", vbYesNo) = vbYes Then
'            saveAnswer
'        Else
'            refreshAnswer
'        End If
'    Else
        refreshAnswer
'    End If
End Sub
Private Sub cmdSave_Click()
    saveAnswer
End Sub

Private Sub refreshAnswer()
        Me.txtAnswer = Me.lbvQuestionList.Column(7)
        Me.lblQuestion.Caption = Me.lbvQuestionList.Column(6)
        Me.lblOld.Caption = Me.lbvQuestionList.Column(7)
        Me.lblID.Caption = Me.lbvQuestionList.Column(0)
End Sub

Private Sub saveAnswer()
    Dim cmd1 As New ADODB.Command
    Dim rst1 As ADODB.Recordset
    With cmd1
        .ActiveConnection = CurrentProject.Connection
        .CommandType = adCmdStoredProc
        .CommandText = "usp_MarketingVisitationQuestionsEdit"
        .Parameters("@id") = Me.lblID.Caption
        .Parameters("@answer") = Me.txtAnswer
        .Execute
        
        'Display save information
        If Len(Me.txtAnswer) > 0 Then
            Me.lblQuestion.Caption = "Answer Saved"
        Else
            Me.lblQuestion.Caption = "This answer has been cleared!"
        End If
        
        'Refresh ListBox data without reselecting top record
        loadQuestions Left(Me.parent.cmbVisits.Column(1), 1), True
    End With
End Sub


Private Sub getQuestion(ByVal ID As Integer)
    Dim cmd1 As New ADODB.Command
    Dim rst1 As ADODB.Recordset
    'Connect to db and run stored proc based on Client ID
    cmd1.ActiveConnection = CurrentProject.Connection
    cmd1.CommandType = adCmdStoredProc
    cmd1.CommandText = "usp_MarketingVisitationQuestionsGet"
    cmd1.Parameters("@id") = ID
    
    'Set the recordset
    Set rst1 = cmd1.Execute()
    
    'Set the values
    Me.lblQuestion.Caption = rst1![Question]
    Me.txtAnswer = rst1![Answer]
End Sub


Public Sub loadQuestions(ByVal pType As String, Optional ByVal reload As Boolean = False)
    If Nz(Me.parent.cmbVisits, 0) <> 0 Then
        Dim cmd1 As New ADODB.Command
        Dim rst1 As ADODB.Recordset
    
        'Connect to db and run stored proc based on Client ID
        cmd1.ActiveConnection = CurrentProject.Connection
        cmd1.CommandType = adCmdStoredProc
        cmd1.CommandText = "usp_MarketingVisitationQuestionsGet"
        cmd1.Parameters("@VisitID") = Nz(Me.parent.cmbVisits, 0)
        cmd1.Parameters("@ClientID") = Me.parent.txtClientID
        cmd1.Parameters("@parent") = pType
    
        'Set the recordset
        Set rst1 = cmd1.Execute()
        Set Me.lbvQuestionList.Recordset = rst1
        
        
        'Set focus on first record
        If reload = False Then
            Me.lbvQuestionList.SetFocus
            Me.lbvQuestionList.Selected(1) = True
        End If

    End If
End Sub

Select all Open in new window

Suggesting a slightly different approach if you don't like all the message boxes...

- Add Edit and Cancel buttons

- Start with the textbox read-only so that the user cannot edit the data when the textbos is initially populated.

- Add code to the Edit Button to unlock the textbox for editing, and also lock the listbox so that the user cannot select a new item while in 'Edit' mode.

- Add code to your 'Save' button to unlock the listbox and lock the textbox, ready for the user to make another selection,

- Add code to your Cancel button to unlock the listbox and lock the textbox, ready for the user to make another selection,

Thoughts?

Another possible solution is to use a popup form to handle the edits.

Mbizup
I had it in a popup form but management want what I'm showing now in the screen print

>>  but management want

Gotcha.

Are you able to upload a small sample database?  Exact data is not necessary if there is anything sensitive.  Just the form/tables (structure and some junk data)/ queries involved.

Will try and put something together at work tomorrow

Okay I'll put some more thought into it tonight too...

A couple of questions -

- Is your textbox bound or unbound?
- How is the data getting saved?  (general description if you dont have access to the database at the moment)


And a quick note... If the textbox and form are UNBOUND, we'll have to come up with a solution other than 'dirty' or 'old value' which only apply to bound controls and forms.

Also, what version(s) of Access are you working with?

EDIT:

Nevermind - your question title says 2003.

The controls are unbound
Save us through VBA to stored procedure on SQL Server