Change Required property of textbox via VBA

[MysticElaine]

Hello,

I have a form that has some controls whose properties are already listed as required in the table, as they are always enabled. The form also has a combo box labeled "Outcome" that changes other textboxes to be enabled depending on what is chosen in that combo box. What I would like to do, is make sure that whatever textboxes are enabled are also required. Is this possible? Here is my code for the Outcome combo box

Private Sub Outcome_Change()
If [Outcome] = "Pending" Then
    [Date Provided].Enabled = False
    [Date Provided] = Null
    [Providers].Enabled = False
    [Providers] = Null
    [Number of Pages].Enabled = False
    [Number of Pages] = Null
    [Time].Enabled = False
    [Time] = Null
    [Cost].Enabled = False
    [Cost] = Null
    [Comments].Enabled = True
Else
        If [Outcome] = "Interpretation" Then
            [Date Provided].Enabled = True
            [Providers].Enabled = True
            [Number of Pages].Enabled = False
            [Number of Pages] = Null
            [Time].Enabled = True
            [Comments].Enabled = True
        Else
                If [Outcome] = "Translation" Then
                    [Date Provided].Enabled = True
                    [Providers].Enabled = True
                    [Number of Pages].Enabled = True
                    [Time].Enabled = True
                    [Comments].Enabled = True
                Else
                    [Date Provided].Enabled = False
                    [Date Provided] = Null
                    [Providers].Enabled = False
                    [Providers] = Null
                    [Number of Pages].Enabled = False
                    [Number of Pages] = Null
                    [Time].Enabled = False
                    [Time] = Null
                    [Cost].Enabled = False
                    [Cost] = Null
                    [Comments].Enabled = True
                End If
        End If
End If
End Sub

[twinnyfo]

Elaine,

I would use a Select Case Statement for your Combo Box, which prevents the eternal nesting of If...Then statements. This would also make it easier to add additional values to your combo box.:

Private Sub Outcome_Change()
    Select Case Outcome
        Case "Pending"
            [Date Provided].Enabled = False
            [Date Provided] = Null
            [Providers].Enabled = False
            [Providers] = Null
            [Number of Pages].Enabled = False
            [Number of Pages] = Null
            [Time].Enabled = False
            [Time] = Null
            [Cost].Enabled = False
            [Cost] = Null
            [Comments].Enabled = True
        Case "Interpretation"
            [Date Provided].Enabled = True
            [Providers].Enabled = True
            [Number of Pages].Enabled = False
            [Number of Pages] = Null
            [Time].Enabled = True
            [Comments].Enabled = True
        Case "Translation"
            [Date Provided].Enabled = True
            [Providers].Enabled = True
            [Number of Pages].Enabled = True
            [Time].Enabled = True
            [Comments].Enabled = True
        Case Else
            [Date Provided].Enabled = False
            [Date Provided] = Null
            [Providers].Enabled = False
            [Providers] = Null
            [Number of Pages].Enabled = False
            [Number of Pages] = Null
            [Time].Enabled = False
            [Time] = Null
            [Cost].Enabled = False
            [Cost] = Null
            [Comments].Enabled = True
    End Select
End Sub

What confuses your post is your question: "What I would like to do, is make sure that whatever textboxes are enabled are also required. Is this possible?"

Based on your code, if you have run through enabling or disabling all of the available text boxes (based on the value of Outcome), then only those required text boxes would be enabled--unless there are some controls on your form that are not listed above.

But, it your really had to, you could query the Enabled Property of any other controls on your form.

As for some additional guidance and mentoring on building databases, it appears that your DB is in need of normalization. This may be a complete overhaul, but I would recommend it. You can find some guidance here: Database Normalization.

Additionally, with normalization, your combo box would not longer have text values, but numbers. Integers are a lot easier to work with, take up fewer resources, and it a standard in database development. In the long term, such changes will help you create robust and sound projects.

I cope the code above answered your question or at least hepped a bit.

[zmbd]

additionally:
---
-It is best practice when naming fields, tables, and files to avoid the use of anything other than alphanumeric characters and the underscore (spaces although allowed are problematic from a programing point of view and best avoided) and it is VERY importaint to avoid all reserved words and tokens (such as "Time" or "%" etc):
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

[MysticElaine]

Hello,

Thanks for the reply. Indeed I have a lot to learn. I just started teaching myself Access with this project mid May and have to have it complete by next Friday, so unfortunately, a complete overhaul is not gonna happen. However, I will definitely look at that link. I have been trying to read/watch as many tutorials as possible (even got a coding for dummies).

Good to know about the Select Case...indeed all the If...Then was getting annoying.

In regards to my question, I apologize it wasn't very clear. This is for my main form. The top half of the form has 9 controls that are always enabled and must be entered before the form can close. These fields are all set to required in the table. To get an idea, this form is for clients who wish to have an interpreter/translator provided to them and the top half is treated like an intake, where we enter the client information, the date they need the service, if they want an interpreter/translator, the language they want, etc. So when the case is first entered, the outcome will be "Pending" unless the service happened the same day as the request.

After the service is provided, we pull up the case again and change the "outcome" to interpretation or translation. Upon the outcome change, other controls become enabled, such as who provided the service, the date it actually was provided, the amount of time it took, the cost, etc. Once these fields are enabled, they should also be required before the form can be saved. However, right now, the form can be saved and closed even if they aren't filled out. I would like to prevent the users from just changing the "outcome" status (which would close the case) and not filling in the other fields.

I hope this clears up what I would like to do. Thanks!

[MysticElaine]

Im a dunce and thought I had something going with the setproperty on the macro but I was wrong.

[MysticElaine]

I thought I could try to use the Validation Rule, but it isn't working, so maybe I am doing it wrong.

I thought I could do:

If [Outcome]="Pending" OR [Outcome]="No Service" Then [Control]=Not Null

I also tried:

IIf([Outcome]="Pending" Or [Outcome]="No Service",[Providers]=Null,[Providers]=Not Null)

I also thought I could put in my code above, after each control is enabled to also have it =Not Null, but that didn't seem to work either.

[MysticElaine]

Ok, I think I figured it out for the most part ;D

Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case Outcome
    Case "Interpretation"
        If IsNull(Me.[Date Provided]) Then
            Cancel = True
            MsgBox "Enter the date the service was provided!"
        End If
        If IsNull(Me.Providers) Then
            Cancel = True
            MsgBox "Enter a Provider!"
        End If
        If IsNull(Me.Time) Then
            Cancel = True
            MsgBox "Enter the time in minutes spent interpreting/translating"
        End If
        If [Providers] = "Outsider" And IsNull(Me.Cost) Then
            Cancel = True
            MsgBox "Enter the cost it took for service to be provided!"
        End If
    Case "Translation"
        If IsNull(Me.Date_Provided) Then
            Cancel = True
            MsgBox "Enter the date the service was provided!"
        End If
        If IsNull(Me.Providers) Then
            Cancel = True
            MsgBox "Enter a Provider!"
        End If
        If IsNull(Me.Time) Then
            Cancel = True
            MsgBox "Enter the time in minutes spent interpreting/translating"
        End If
        If IsNull(Me.Number_of_Pages) Then
            Cancel = True
            MsgBox "Enter the number of pages that were translated!"
        End If
        If [Providers] = "Outsider" And IsNull(Me.Cost) Then
            Cancel = True
            MsgBox "Enter the cost it took for service to be provided!"
        End If
    Case "No Service"
        If IsNull(Me.Comments) Then
            Cancel = True
            MsgBox "Enter a reason for why service was not provided!"
        End If
End Select
End Sub

Only problem here is that it won't work for my combo box "Providers". I'm not sure why. It keeps popping up the message box after I select someone from the combo, and now it seems to have broken my code as it is still acting as if the code is there even after I deleted that portion of the code and restarted the program :(

Also, I am sure this doesn't look the best and could probably be written a different way, but it is what I figured out from multiple Google searches :D

[jimatqsi]

Elaine,
Please explain the problem clearly. You say "the messagebox for Providers" but in fact, there are multiple references to the Providers combo box and each has a different message. So which message are you seeing?

Jim

[MysticElaine]

Sorry, I meant the one with If IsNull([Providers]) Then...where the message states to enter a provider. I just saw something to try to add the Nz, so am going to try that.

Edit: This didn't work

If Nz(Me.Providers, "") = "" Then
            Cancel = True
            MsgBox "Enter a Provider!"
        End If

[MysticElaine]

Hmm, I pulled up a backup saved file and redid the code with the Nz and it works. So I don't know what happened to my other version but I think the problem of my combo is fixed. Thanks for the reply.

BTW, is there a way to mark this as solved?

[jimatqsi]

So glad you got a good result. Congratulations. :)

There should be a link for you to mark the best answer.