Setting property of label to visible yes or no, dependent upon other field value

[jondavidf]

Hey all,

I am working on a form to gather medical information for a research project. One of the data items is different types of chronic conditions. If a chronic condition of "diabetes" is entered then we need to gather additional information (annual eye and feet exam dates).

My form is currently set up where users can select chronic conditions via a drop-down or free type if the condition is not listed to enter the condition onto the record. I also have a listbox immediately below the data entry field which shows chronic conditions previously entered. The listbox populates from a query I've set up.

What I'd like to do is have a label either be visible or invisible based off whether diabetes is in the listbox or underlying query. Diabetes may be entered a couple different ways, so I'd like the argument to catch any entries containing diabetes, e.g. something like 'If Like & "*" & "diabetes" & "*", then label = visible, else label = invisible'. I just used the If-Then statement to explain and don't know if this is the best approach.

If I could also disable the Eye Exam Date and Feet Exam Date text boxes if a patient doesn't have diabetes listed, that would be incredible, too!

Below are a few screenshots of the form so you can get an idea of what I'm talking about.

Thanks so much for the assistance!

Jon




(above is pretty much the entire form)

(above is the the zoomed in "Chronic Conditions" section and the label I'd like to make visible or invisible in red)

[June7]

Could use a textbox for the 'label'. Have a conditional IIf() expression in the ControlSource.

Then use Conditional Formatting to enable/disable the date textboxes.

This way you avoid VBA code.

[apr pillai]

If you are familiar with VBA Code then copy and paste the following code into the Form's VBA Module with changes for ListBox Name & Label Name:

Private Sub OnCurrent()Dim ConditionList As ListBox, strItem As String
Dim listcount As Long, j As Long, condition As String
Dim cflag As Integer
On Error GoTo OnCurrent_Err

condition = "Diabetes"
Set ConditionList = Me.ListBoxName
listcount = CondditionList.listcount - 1
strItem = ""
If listcount = 0 Then
    Exit Sub
End If
        cflag = 0
        For j = 0 To listcount
       strItem = ConditionList.ItemData(j)
       cflag = InStr(1, strItem, condition)
       If cflag > 0 Then
              Exit For
       End If
        Next
       If cflag > 0 Then
           Me.LabelRed.Visible = True
       Else
           Me.LabelRed.Visible = False
       End If

OnCurrent_Exit:
Exit sub

OnCurrent_Err:
MsgBox Err & ":" & Err.Description
Resume OnCurrent_Exit
End Sub

I have not tested the Code. You may try it out on your own risk. Assumes that you don't have any other code running in OnCurrent() Event Procedure of your Form.

[isladogs]

A simpler solution with minimal code involves using the Tag property for each object on your form.
This is fully explained with an example database at https://www.access-programmers.co.uk...d.php?t=293439

[jondavidf]

I have added the code as Apr suggested as I hadn't seen the latest post by Ridders before I started entering the code. The code does work when the form is loaded initially or if I go into design view and back to form view. How could I get it to update as conditions are being entered, though? I tried adding the following VBA code to the "Save Condition" button:

Forms!frmEncounters.Refresh
Forms!frmEncounters.Repaint

Neither the refresh nor the repaint or both together actually refresh the form with the changes.

Thanks!

Jon

[isladogs]

Have a look at this article which explains the differences between refresh, requery, repaint and recalc.
http://www.projectperfect.com.au/blo...h-and-repaint/
Easy to get confused

Personally I'd suggest a different approach to the one you've tried

[June7]

Did you consider non-VBA approach suggested in post 2?

[jondavidf]

Not yet. I will try the non-VBA approach and post an update as soon as I can.