Larry Brister
asked on
Access 2003 Dirty?? Event?
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.
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.
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.
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.
ASKER
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
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(
The textbox is set to the value in Me.lbvQuestionList.Column(
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
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.
- 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.
ASKER
Mbizup
I had it in a popup form but management want what I'm showing now in the screen print
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.
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.
ASKER
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.
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.
EDIT:
Nevermind - your question title says 2003.
ASKER
The controls are unbound
Save us through VBA to stored procedure on SQL Server
Save us through VBA to stored procedure on SQL Server
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
I've described this a little in my article here:
https://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