Solved

Access 2003 Dirty?? Event?

Posted on 2013-01-16
11
472 Views
Last Modified: 2013-01-17
I have a form with a textbox, a save button, and a ListBox.

What I want to do is on "After Update" event of the text box...
If I click on the listBox before saving, get a message "Your answer changed! Save?"

I'm unfamiliar with how to do it but was told the Dirty method would do the trick.
0
Comment
Question by:lrbrister
  • 6
  • 4
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38783029
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

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
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38783097
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.
0
 

Author Comment

by:lrbrister
ID: 38783206
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
Screenprint

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

Open in new window

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 61

Expert Comment

by:mbizup
ID: 38785216
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.
0
 

Author Comment

by:lrbrister
ID: 38785400
Mbizup
I had it in a popup form but management want what I'm showing now in the screen print
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38785410
>>  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.
0
 

Author Comment

by:lrbrister
ID: 38785419
Will try and put something together at work tomorrow
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38785430
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38785440
Also, what version(s) of Access are you working with?

EDIT:

Nevermind - your question title says 2003.
0
 

Author Comment

by:lrbrister
ID: 38785505
The controls are unbound
Save us through VBA to stored procedure on SQL Server
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38785550
Ok --

Scrap previous ideas.  :)

This is the general idea...

- Create a hidden textbox. (Visible = False)
- Populate that textbox exactly the same way you populate the textbox in your screenshot.

The hidden textbox cannot be edited and will retain your 'previous value'

- In your list box's click event compare the value in the hidden textbox with the value in the editable textbox:

If Me.MyEditableTextbox & "" <> Me.MyHiddenTextbox & "" then
         ' Your data was changed
End if

Open in new window


Heading to bed, but I'll check in the morning.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question