Solved If...then or loop help

[donkey9972]

Hi,

I have a form that has 2 fields. On the first field which is the date, I have my form setup that if there is no value all the other fields are disabled. Once a date is entered all the fields become enabled. Then on that same form I have a 2nd field and depending on the number entered there I want to have only specific fields become enabled. The problem I am having is that it appears to be an either or situation. How do I write an if..then (or some kind of loop) if the date field has the date and the aisles field has a number in it (1, 2, 3, 4....20) then the corresponding fields will be enabled. If there is no date and/or there is not number in the aisles field then nothing becomes enabled. This is the code I am using to enable the fields if there is a date entered. I have been able to get it to work also with the aisles depending on if there is a number entered in that field. But as I said I need it to be if both have an entry that the specific fields become enabled.

Public Sub EnableControl()
On Error Resume Next
Dim i As Integer
If IsNull(Me.txtDate) Then
    For i = 1 To 40
        Me("A" & i).Enabled = False
    Next
        Me.txtDate.SetFocus
    GoTo ONE
Else

    For i = 1 To 40
        Me("A" & i).Enabled = Me.txtDate >= 0
    Next
ONE:
End If
End Sub

 

[June7]

Try:

Public Sub EnableControl()
Dim i As Integer
For i = 1 to 40
    Me("A" & i).Enabled = Not IsNull(Me.txtDate) And Not IsNull(Me.2ndField) And i <= Me.2ndField
Next
End Sub

Is this a continuous or datasheet form? Won't work for those. ALL records will show the same setting. This is what textbox and combobox Conditional Formatting is for.

 

[donkey9972]

Its a single form. Thank you I will put this in and see what I come up with.

 

[June7]

I just edited previous post. Review again.

Actually, Not IsNull(Me.2ndField) might not be necessary. Try both, what happens?

 

[donkey9972]

I see, and it works really well. Thank you.

 

[donkey9972]

Ok I am running into a bit of a problem on this. It works for single fields. However, I have 3 fields in each aisle that have to be enabled when both the txtDate and the Aisles fields are populated. My thinking is no so robust, all I can seem to come up with is 1 line of code for each field.

 

[donkey9972]

Okay, I have this working how I want, but the problem is I would have to write this 8 times, is there anyway to make this work without writing this code 8 times?

Dim i As Integer
If IsNull(Me.txtDate) Then
For i = 1 To 3
        Me("A" & i).Enabled = False
        Me("B" & i).Enabled = False
Next
    Me.txtDate.SetFocus
End If

If Not IsNull(Me.txtDate) And Not IsNull(Me.Aisles) Then
GoTo TWO
End If

TWO:
    If (Me.Aisles) = 1 Then
    For i = 1 To 3
        Me("A" & i).Enabled = True
    Next
End If
    If (Me.Aisles) = 2 Then
    For i = 1 To 3
        Me("A" & i).Enabled = True
        Me("B" & i).Enabled = True
    Next
End If

 

[cheekybuddha]

Replace the whole lot with (no need to set .Enabled = False first):

For i = 1 To 3
  Me("A" & i).Enabled = IsDate(Me.txtDate) And (Me.Aisles = 1 Or Me.Aisles = 2)
  Me("B" & i).Enabled = IsDate(Me.txtDate) And Me.Aisles = 2
Next i
If Not IsDate(Me.txtDate) Then Me.txtDate.SetFocus

 

[June7]

So what happened to 1 to 40 loop?

And with this current loop, fields A3, B1, B3 will never be enabled.

Why would you have had to write your code 8 times?

Want to provide a current version of your db?

 

[donkey9972]

June7, I have multiple aisles, and each aisle has several sections below it. The initial code is from a different part of another form that I thought I could use for this part.

 

[donkey9972]

Replace the whole lot with (no need to set .Enabled = False first):

For i = 1 To 3
  Me("A" & i).Enabled = IsDate(Me.txtDate) And (Me.Aisles = 1 Or Me.Aisles = 2)
  Me("B" & i).Enabled = IsDate(Me.txtDate) And Me.Aisles = 2
Next i
If Not IsDate(Me.txtDate) Then Me.txtDate.SetFocus

Ok, this works really well. It is doing what I need and I can see how I can add on to it. Thank you, saves me a whole lot of unnecessary code writing.

 

[cheekybuddha]

Since it's difficult to visualise your form from your descriptions, this is a bit of a guess, but you can probably simplify further with:

Dim i As Integer, aisle As Integer, arrAisles As Variant
arrAisles = Array("A", "B", "C", "D", "E")   ' load array with all prefixes
For i = 1 To 3
  For aisle = 0 To UBound(arrAisles)
    Me(arrAisles(aisle) & i).Enabled = IsDate(Me.txtDate) And (Me.Aisles <= aisle + 1)
  Next aisle
Next i
If Not IsDate(Me.txtDate) Then Me.txtDate.SetFocus

 

[June7]

Or without array.

Dim i As Integer, aisle As Integer
For i = 1 To 3
  For aisle = 1 To 2 'two aisles
    Me(Chr(64 + aisle) & i).Enabled = IsDate(Me.txtDate) And (Me.Aisles <= aisle + 1)
  Next aisle
Next i
If Not IsDate(Me.txtDate) Then Me.txtDate.SetFocus