Okay, if that was your code for one, then this would be your code for each
(this is the optimized version I hinted at earlier):
Public Sub ClearViolation(ByRef LabelControl As Label)
If Nz(LabelControl.Caption) <> "" Then 'Note, this handles both null
and empty strings in your control.
'You should always specifically declare the types of any variables,
both for speed and readability.
Dim Msg As String
Dim Style As VbMsgBoxStyle
Dim Title As String
Dim Response As VbMsgBoxResult
Dim MyString As String 'Is this even necessary?
Msg = "Did you want to change this Violation?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Violation Type"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
MyString = "Yes"
With LabelControl
.Caption = ""
.Locked = False
.BackColor = 13303807
End With
Else
MyString = "No"
End If
End If
End Sub
Then wherever you need to call it from (I'm assuming it's the label's
double-click event), you would do the following:
Private Sub txt1Label_DblClick(Cancel As Integer)
ClearViolation Me!txt1Label
End Sub
Private Sub txt2Label_DblClick(Cancel As Integer)
ClearViolation Me!txt2Label
End Sub
...etc.
Rob
Gina Whipp said:
You know I thought all was well until I answered yes then all the fields
in the record went blank. I think I'm going back to the simplier code
for each control, much easier, so here's what I came up with....
If IsNull([txt1Label]) Then Exit Sub
Dim Msg, Style, Title, Response, MyString
Msg = "Did you want to change this Violation?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Violation Type"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
MyString = "Yes"
Me.txt1Label = ""
Me.txt1Label.Locked = False
Me.txt1Label.BackColor = 13303807
Else
MyString = "No"
DoCmd.CancelEvent
End If
Robert Morley said:
It occurred to me after I posted that you might be trying to do
something other than what I thought, and obviously John Spencer was
thinking the same thing. If you're trying only to disable one control
out of the nine, then you'd want to do something more like the code
below (no looping required). In this case, you would call the code from
the double-click event for each control and then pass the LabelNumber.
Note that there are actually several optimizations that could be made to
the code, if this is what you're trying to accomplish, but I tried to
keep it as similar to your code as possible.
Please let us know if either of the two possible interpretations is what
you're trying to do, or if we've misunderstood entirely. Here's the
revised function:
Public Function ClearViolation(ByVal LabelNumber As Long)
Dim Msg, Style, Title, Response, MyString
Msg = "Did you want to change this Violation?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Violation Type"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
MyString = "Yes"
Me("txt" & LabelNumber & "Label") = ""
Me("txt" & LabelNumber & "Label").Locked = False
Me("txt" & LabelNumber & "Label").BackColor = 13303807
Else
MyString = "No"
End If
End Function
Hey Guys (and Gals),
Having fun with Do Until statements, can't get this one to work...
What I want is if you double-click on one field then clear the
contents. What it's doing is asking you the question 9 times instead of
just the one you double click on. Hope that makes sense. Can someone
help me?
Thanks in advance,
Gina Whipp
Public Function ClearViolation()
Dim LabelNumber As Long
Do Until LabelNumber = 9
'If IsNull(Me("txt" & LabelNumber & "Label")) Then Exit Function
Dim Msg, Style, Title, Response, MyString
Msg = "Did you want to change this Violation?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Violation Type"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
MyString = "Yes"
Me("txt" & LabelNumber & "Label") = ""
Me("txt" & LabelNumber & "Label").Locked = False
Me("txt" & LabelNumber & "Label").BackColor = 13303807
Else
MyString = "No"
DoCmd.CancelEvent
End If
LabelNumber = LabelNumber + 1
Loop
End Function
