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

 

[Pat Hartman]

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.

Your tables are not correctly designed. You have two repeating groups. If you post your schema, we will help you to rework them and then you can use subforms to enter the data rather than field names with numeric suffixes.

Think about your current store layout and think about what code and forms and queries you will need to change if you need to add/remove aisles and sections. With a properly normalized schema, you would have NO changes if you added/removed aisles and sections. PERIOD. That is the primary benefit of normalization. ONE set of code handles all logic regardless of how many items are in a set.

If you had an employee with 5 children, would you add 5 dependent names, 5 dependent birth dates, 5 dependent .... fields? What if you got a new employee and he had 6 children? Would you then modify all forms, queries, and code to handle the additional dependent? I sure hope not.