Solved

Access 2003 Dirty?? Event?

Posted on 2013-01-16
11
477 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

739 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