For..Next question

[coasterman]

I have a validation routine behind cmd button 1.

For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
        If Mid(ctl.Tag, 3, 1) = "£" And Trim(ctl & "") = "" Then
      '  i = 1 (my hairbrain idea)
         msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
               "You can't save this record until this data is provided!" & nl & _
               "Enter the data and try again . . . "
          Style = vbCritical + vbOKOnly
          Title = "Required Data..."
          MsgBox msg, Style, Title
          ctl.SetFocus
          Cancel = True
        ' If i = 0 Then GoTo Limitation (hairbrain idea cont.)
          Exit For
          
        End If
      End If
       Next

' the bit I want to get to once all controls meet validation

Limitation:
LimitationPeriod = 1095

If Not IsNull(Me.DateofAcc) Then
DaysSinceRTA = DateDiff("d", Me.[DateofAcc], Date)
etc.......

I also have a second cmd button which launches the code starting at 'LimitationPeriod......"

I was trying to combine the two bits of code to save a second cmd button but I am finding that once once code in the first part finds the first blank field it Exit for.. and carries on with the second chunk of code.

As that second chunk of code launches an excel sheet whose values are dependent on validated data in the first half of the code it's simply not fit for purpose.

I had some nonsense idea that if I could set a variable i to keep track of when validation failed it would let me exit the sub after the focus had been set to the empty control. That was as I've found not going to work, or at at least if it has some potential I have stuff in the wrong order?

What I am looking for is for the sub to exit after it finds the first invalid (in this case empty field" so it doesnt carry on after the For Next loop and launch the spreadsheet.

I thought I had this sorted in Excel but seems things are quite so simple in Access

Thanks for looking

 

[boblarson]

Use a flag

Dim blnValidated As Boolean

Then set blnValidated to True if it passes validation and if it doesn't then it will still be false.

Put this around your second part:

If blnValidated Then

' the second part of your code

End If

 

[coasterman]

I must admit I wasn't really understanding the response first off and had to go back to looking at this line by line over and over. When I finally got my head around the logic and had figured out what it was I was missing it was of course exactly exactly as you had said.

I'm guess this is all part of learning and not hearing the answer even when told to you is probably not an uncommon occurence. Anyway thanks again, doubtless I will be back

 

[boblarson]

I must admit I wasn't really understanding the response first off and had to go back to looking at this line by line over and over. When I finally got my head around the logic and had figured out what it was I was missing it was of course exactly exactly as you had said.

I'm guess this is all part of learning and not hearing the answer even when told to you is probably not an uncommon occurence. Anyway thanks again, doubtless I will be back

Yeah, it is sometimes hard to gauge how to phrase something so someone can get it. It really depends on their level of experience which we can't necessarily know up front. Glad you got it.