Do Until... Help Please! "Gina Whipp" <NotInterested[ at ]InViruses.com> 18.09.2006 18:48:26 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

Re: Do Until... Help Please! "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> 18.09.2006 19:19:26 I think maybe you wanted something that looked more like this: Public Function ClearViolation() Dim 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" Do Until LabelNumber = 9 'If IsNull(Me("txt" & LabelNumber & "Label")) Then Exit Function Me("txt" & LabelNumber & "Label") = "" Me("txt" & LabelNumber & "Label").Locked = False Me("txt" & LabelNumber & "Label").BackColor = 13303807 LabelNumber = LabelNumber + 1 Loop Else MyString = "No" 'DoCmd.CancelEvent 'Note: commented out the CancelEvent, as I believe it becomes redundant at this point. End If End Function "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message news:uSaVQI12GHA.4932[ at ]TK2MSFTNGP02.phx.gbl... [Quoted Text] > 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 >

Re: Do Until... Help Please! "John Spencer" <spencer[ at ]chpdm.edu> 18.09.2006 19:21:11 Guessing that you really want to do loop through the labels if the question is answered yes Try moving the loop inside the If - Then Public Function ClearViolation() Dim LabelNumber As Long '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 Do Until LabelNumber = 9 MyString = "Yes" Me("txt" & LabelNumber & "Label") = "" Me("txt" & LabelNumber & "Label").Locked = False Me("txt" & LabelNumber & "Label").BackColor = 13303807 LabelNumber = LabelNumber + 1 Loop Else MyString = "No" DoCmd.CancelEvent End If End Function "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message news:uSaVQI12GHA.4932[ at ]TK2MSFTNGP02.phx.gbl... [Quoted Text] > 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 >

Re: Do Until... Help Please! "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> 18.09.2006 19:40:01 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 "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message news:uSaVQI12GHA.4932[ at ]TK2MSFTNGP02.phx.gbl... [Quoted Text] > 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 >

Re: Do Until... Help Please! "Gina Whipp" <NotInterested[ at ]InViruses.com> 18.09.2006 19:46:23 Great minds think alike, thanks to you both!!!!! "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message news:uSaVQI12GHA.4932[ at ]TK2MSFTNGP02.phx.gbl... [Quoted Text] > 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 >

Re: Do Until... Help Please! "Gina Whipp" <NotInterested[ at ]InViruses.com> 18.09.2006 19:52:44 John Spencer, I unremmed the... If IsNull(Me("txt" & LabelNumber & "Label")) Then Exit Function and while it breaks at that point it still runs the code, am I missing something? Thanks, Gina Whipp "John Spencer" <spencer[ at ]chpdm.edu> wrote in message news:OZmQee12GHA.2228[ at ]TK2MSFTNGP03.phx.gbl... [Quoted Text] > Guessing that you really want to do loop through the labels if the > question is answered yes > > Try moving the loop inside the If - Then > > > Public Function ClearViolation() > Dim LabelNumber As Long > > '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 > > Do Until LabelNumber = 9 > MyString = "Yes" > Me("txt" & LabelNumber & "Label") = "" > Me("txt" & LabelNumber & "Label").Locked = False > Me("txt" & LabelNumber & "Label").BackColor = 13303807 > LabelNumber = LabelNumber + 1 > Loop > > Else > MyString = "No" > DoCmd.CancelEvent > End If > > > End Function > "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message > news:uSaVQI12GHA.4932[ at ]TK2MSFTNGP02.phx.gbl... >> 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 >> > >

Re: Do Until... Help Please! "Gina Whipp" <NotInterested[ at ]InViruses.com> 18.09.2006 20:00:25 But where is it getting the numbers from, now I am confused. But to go back... The code you both provided does what I want except it does not stop if the field is empty. I was trying to write a ClearViolation() that could be called from the double-click event of each control but if the control was empty to do nothing. Hope I explained that correctly and clearly! Gina Whipp "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> wrote in message news:u0PQEp12GHA.2096[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > 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 > > > "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message > news:uSaVQI12GHA.4932[ at ]TK2MSFTNGP02.phx.gbl... >> 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 >> > >

Re: Do Until... Help Please! "Gina Whipp" <NotInterested[ at ]InViruses.com> 18.09.2006 20:37:47 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" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> wrote in message news:u0PQEp12GHA.2096[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > 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 > > > "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message > news:uSaVQI12GHA.4932[ at ]TK2MSFTNGP02.phx.gbl... >> 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 >> > >

Re: Do Until... Help Please! "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> 19.09.2006 15:21:40 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" <NotInterested[ at ]InViruses.com> wrote in message news:ONWyWF22GHA.1252[ at ]TK2MSFTNGP04.phx.gbl... [Quoted Text] > 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" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> wrote in message > news:u0PQEp12GHA.2096[ at ]TK2MSFTNGP05.phx.gbl... >> 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 >> >> >> "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message >> news:uSaVQI12GHA.4932[ at ]TK2MSFTNGP02.phx.gbl... >>> 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 >>> >> >> > >

Re: Do Until... Help Please! "John Spencer" <spencer[ at ]chpdm.edu> 19.09.2006 16:57:22 You probably need to move that line inside the loop? Perhaps you should take a step back and try to write out a description of exactly what you want this function to do. "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message news:u%23F8Ls12GHA.3944[ at ]TK2MSFTNGP04.phx.gbl... [Quoted Text] > John Spencer, > > I unremmed the... > > If IsNull(Me("txt" & LabelNumber & "Label")) Then Exit Function > > and while it breaks at that point it still runs the code, am I missing > something? > > Thanks, > Gina Whipp > > "John Spencer" <spencer[ at ]chpdm.edu> wrote in message > news:OZmQee12GHA.2228[ at ]TK2MSFTNGP03.phx.gbl... >> Guessing that you really want to do loop through the labels if the >> question is answered yes >> >> Try moving the loop inside the If - Then >> >> >> Public Function ClearViolation() >> Dim LabelNumber As Long >> >> '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 >> >> Do Until LabelNumber = 9 >> MyString = "Yes" >> Me("txt" & LabelNumber & "Label") = "" >> Me("txt" & LabelNumber & "Label").Locked = False >> Me("txt" & LabelNumber & "Label").BackColor = 13303807 >> LabelNumber = LabelNumber + 1 >> Loop >> >> Else >> MyString = "No" >> DoCmd.CancelEvent >> End If >> >> >> End Function >> "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message >> news:uSaVQI12GHA.4932[ at ]TK2MSFTNGP02.phx.gbl... >>> 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 >>> >> >> > >

Re: Do Until... Help Please! "Gina Whipp" <NotInterested[ at ]InViruses.com> 19.09.2006 18:54:40 Thanks Robert, I like the optimization... and will be using it! Gina Whipp "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> wrote in message news:%23fjGZAA3GHA.3944[ at ]TK2MSFTNGP04.phx.gbl... [Quoted Text] > 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" <NotInterested[ at ]InViruses.com> wrote in message > news:ONWyWF22GHA.1252[ at ]TK2MSFTNGP04.phx.gbl... >> 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" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> wrote in message >> news:u0PQEp12GHA.2096[ at ]TK2MSFTNGP05.phx.gbl... >>> 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 >>> >>> >>> "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message >>> news:uSaVQI12GHA.4932[ at ]TK2MSFTNGP02.phx.gbl... >>>> 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 >>>> >>> >>> >> >> > >

Re: Do Until... Help Please! "Gina Whipp" <NotInterested[ at ]InViruses.com> 19.09.2006 18:55:31 John, Tried that but no matter switched to what Robert had suggested and it works just fine. Thanks, Gina Whipp "John Spencer" <spencer[ at ]chpdm.edu> wrote in message news:eMhGoyA3GHA.4648[ at ]TK2MSFTNGP04.phx.gbl... [Quoted Text] > You probably need to move that line inside the loop? > > Perhaps you should take a step back and try to write out a description of > exactly what you want this function to do. > > > "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message > news:u%23F8Ls12GHA.3944[ at ]TK2MSFTNGP04.phx.gbl... >> John Spencer, >> >> I unremmed the... >> >> If IsNull(Me("txt" & LabelNumber & "Label")) Then Exit Function >> >> and while it breaks at that point it still runs the code, am I missing >> something? >> >> Thanks, >> Gina Whipp >> >> "John Spencer" <spencer[ at ]chpdm.edu> wrote in message >> news:OZmQee12GHA.2228[ at ]TK2MSFTNGP03.phx.gbl... >>> Guessing that you really want to do loop through the labels if the >>> question is answered yes >>> >>> Try moving the loop inside the If - Then >>> >>> >>> Public Function ClearViolation() >>> Dim LabelNumber As Long >>> >>> '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 >>> >>> Do Until LabelNumber = 9 >>> MyString = "Yes" >>> Me("txt" & LabelNumber & "Label") = "" >>> Me("txt" & LabelNumber & "Label").Locked = False >>> Me("txt" & LabelNumber & "Label").BackColor = 13303807 >>> LabelNumber = LabelNumber + 1 >>> Loop >>> >>> Else >>> MyString = "No" >>> DoCmd.CancelEvent >>> End If >>> >>> >>> End Function >>> "Gina Whipp" <NotInterested[ at ]InViruses.com> wrote in message >>> news:uSaVQI12GHA.4932[ at ]TK2MSFTNGP02.phx.gbl... >>>> 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 >>>> >>> >>> >> >> > >