Solved

Access 2003 Dirty?? Event?

Posted on 2013-01-16
11
468 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now