Consulting

Results 1 to 12 of 12

Thread: Use Loop to modify the visibility of multiple fields in a form

  1. #1

    Use Loop to modify the visibility of multiple fields in a form

    Hello, all. Completely new to vba here and coding in general. What i learned is from forums and youtube videos.

    I' making an access database for a friend of mine, to store informations about her patients. I have a form (ViewPatient) to view single patient information, meaning general informations, pill informations and exam informations. As there are people who take only 1 pill and others who take 15, i want to make the empty pill fields not visible on load.
    I made it happen with simple IF and visibility true or false on load, but since there are to many fields (pill1...pill20 / dose1...dose20 / epi1...epi20 / note1 ...note20 / plus the labels for all that), i wonder if there is a way to make it work with a loop, to not fill that coding page. And to learn something new basically.


    This is the code that works:
    If pill20.Value <> "" Then
       Label_pill20.Visible = True
       Pill20.Visible = True 
       Label_dose20.Visible = True
       dose20.Visible = True
       Label_epi20.Visible = True
       epi20.Visible = True
       Label_note20.Visible = True
       note20.Visible = True 
       Else
       Label_Pill20.Visible = False
       Pill20.Visible = False
       Label_dose20.Visible = False
       dose20.Visible = False
       Label_epi20.Visible = False
       epi20.Visible = False 
       Label_note20.Visible = False
       note20.Visible = False
    End If
    And this is what i come up for a loop, that doesn't work ( i get an error it cant find the field '"Pill" & i' ):
    Dim i As Long
    i = 2
    Do While i < 21
    If Forms![ViewPatient].Form!["Pill" & i].Value <> "" Then
       Forms![ViewPatient].Form!["Label_Pill" & i].Visible = True
       Else
       Forms![ViewPatient].Form!["Label_Pill" & i].Visible = False
    End If
    i = i + 1
    Loop
    If what i m thinking can be done, i m pretty sure i lost it with the [ ] and the " " and the ' 's. Those things are confusing as f...

    Thanks in advance.
    Last edited by Aussiebear; 12-08-2022 at 08:09 PM. Reason: Added code tags to supplied code

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    372
    Location
    [] are only needed if names have space or punctuation/special characters and sometimes if it is a reserved word.

    Syntax is not correct.

    If labels are associated with data control (textbox, combobox, etc) they will not be visible when data control is not visible.

    Why are you using a number iterating loop? All controls have the same number. What needs to change is the text prefix. If you want a number iteration then controls would need a generic name like: tbxData1, tbxData2, etc.

    Most likely controls that are blank are Null, not empty string. I never allow empty string in fields.

    Since visibility is dependent on value of Pill20 and I see only 3 other controls:

    For i = 1 to 3
    Me("tbxData" & i).Visible = Not IsNull(Me.Pill20)
    Next

    If you actually have 20 sets of these 4 fields/controls, then your db structure is bad.

    If you want to keep the names as they are, alternative is to use control's Tag property. Set it to some value like "Grp1". Then code would loop through ALL controls on form and look at the Tag value and only set visibility for those that are "Grp1".
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Thank you for your quick and thorough reply, June.

    Quote Originally Posted by June
    Why (so many mistakes)
    Because i dont know shit! I dont have any education on the subject, its the second database i make in my life, and its something i dont need in my job, so i dont really have to learn it. Its something i do on my own, to pass my time and learn as i go.
    I m sure if anyone with a minor knowledge look at my database will see errors everywhere, but i m proud of it, thinking of the times something didnt work and after searching for 2 hours i found the solution.

    Quote Originally Posted by June7 View Post
    [] are only needed if names have space or punctuation/special characters and sometimes if it is a reserved word.

    If labels are associated with data control (textbox, combobox, etc) they will not be visible when data control is not visible
    Thanks for the lesson. I didnt know any of that and now i do.

    Quote Originally Posted by June
    Why are you using a number iterating loop?
    I though i cant have it 20 times longer that it has to, searched "access vba loop" on my friend google and come across "do while loop"...

    Quote Originally Posted by June
    For i = 1 to 3
    Me("tbxData" & i).Visible = Not IsNull(Me.Pill20)
    Next
    Thank you, thank you, thank you.

    Quote Originally Posted by June
    If you actually have 20 sets of these 4 fields/controls, then your db structure is bad.
    I hope you dont have to see my whole db... Its all bad!

    Quote Originally Posted by June
    If you want to keep the names as they are, alternative is to use control's Tag property. Set it to some value like "Grp1". Then code would loop through ALL controls on form and look at the Tag value and only set visibility for those that are "Grp1".
    Not something to add now, as i want its record searched independently, but something to search and learn.


    *Edit*
    This is my code that works now:
    Private Sub Form_Load()
    
    For i = 2 To 20
    If Me("Pill" & i).Value <> "" Then
    Me("Pill" & i).Visible = True
    Me("dose" & i).Visible = True
    Me("epi" & i).Visible = True
    Me("note" & i).Visible = True
        Else
    Me("Pill" & i).Visible = False
    Me("dose" & i).Visible = False
    Me("epi" & i).Visible = False
    Me("note" & i).Visible = False
    End If
    Next
    End Sub
    Last edited by jim_koug; 12-09-2022 at 03:44 AM.

  4. #4
    VBAX Contributor
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    113
    Location
    The time to normalize your DB is at the start.
    Then if that is not possible, then ASAP. You are only going to jump through hoops like this with your current structure. You are thinking with an excel head on and not an Access one.

    In reality, you should have a subform with a record for each pill taken by a patient and that record records the number, frequency etc.

    Your call?

  5. #5
    Quote Originally Posted by Gasman View Post
    The time to normalize your DB is at the start.
    Then if that is not possible, then ASAP. You are only going to jump through hoops like this with your current structure. You are thinking with an excel head on and not an Access one.

    In reality, you should have a subform with a record for each pill taken by a patient and that record records the number, frequency etc.

    Your call?
    I used to make excels that did everything an access db can do, just to avoid having to deal with access... Always seemed like a big jump for me.

    In the same form i have the "exams" as a subform, with a record for each one as a continuous form. I tried making pills the same way, but that continuous form is taking to much space. To "save" space from pills and show them all at once, by having them next to each other, i ended up making them as fields in the same table as the personal info.
    I' l add a screenshot of my form (sorry for the greek in it). The info around the red square is the pill section.

    printscreen.jpg

  6. #6
    VBAX Contributor
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    113
    Location
    It is a big jump. It requires a completely different way of thinking.

    That probably is the biggest mistake Excel users make when trying their hand at Access.
    Whilst it might look nice visually, it is a nightmare to maintain as you have found and will continnue to find out.

    FWIW, when I have had to do similar in Excel, I use it as it was an Access DB, lookups etc, so it happens in the other direction as well.

  7. #7
    VBAX Regular xps350's Avatar
    Joined
    Jul 2022
    Posts
    13
    Location
    I totally agree with Gasman. Your database design is incorrect. Besides, I think your solution cannot work. For example, if someone takes 2 pills, only 2 sets of fields will be shown. If a third drug is prescribed, where do you enter it?
    Groeten,

    Peter

  8. #8
    VBAX Contributor
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    113
    Location
    Quote Originally Posted by jim_koug View Post
    I used to make excels that did everything an access db can do, just to avoid having to deal with access... Always seemed like a big jump for me.

    In the same form i have the "exams" as a subform, with a record for each one as a continuous form. I tried making pills the same way, but that continuous form is taking to much space. To "save" space from pills and show them all at once, by having them next to each other, i ended up making them as fields in the same table as the personal info.
    I' l add a screenshot of my form (sorry for the greek in it). The info around the red square is the pill section.

    Have you not heard of Tab controls?
    Using those you could have one for Exams, one for Pills, one for Medical History etc.

    printscreen.jpg

  9. #9
    Quote Originally Posted by xps350 View Post
    I totally agree with Gasman. Your database design is incorrect. Besides, I think your solution cannot work. For example, if someone takes 2 pills, only 2 sets of fields will be shown. If a third drug is prescribed, where do you enter it?

    I have another form for editing data. That particular form should have been a report (as it is for show only), but i dont like the look of a report compared to a form (really cant explain it, i just dont like it!).
    As broken as my db may be, i m ok as long as patients take up to 20 pills a day. If someone takes 21, first i feel sorry for them and wish them the best, second i mildly hate them for ruining my db!

    I thank you all for your responses and remarks, but i think now we are entering philosophical issues, of what looks better.

  10. #10
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    372
    Location
    So this form with the Visible code is only for viewing, not data entry?

    Our advice to normalize is not based on what 'looks' better - it's what normally works better. It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."

    Final advice is about code indenting. Consider:
    Private Sub Form_Load()
       For i = 2 To 20
          If Me("Pill" & i).Value <> "" Then
             Me("Pill" & i).Visible = True
             Me("dose" & i).Visible = True
             Me("epi" & i).Visible = True
             Me("note" & i).Visible = True
          Else
             Me("Pill" & i).Visible = False
             Me("dose" & i).Visible = False
             Me("epi" & i).Visible = False
             Me("note" & i).Visible = False
          End If
       Next 
    End Sub
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Quote Originally Posted by June7 View Post
    So this form with the Visible code is only for viewing, not data entry?
    Yeap, just for viewing. The final user doesnt "have" it with technology, so i m trying to make it as easy as possible for her. I m afraid she will mistakenly change values, so i made it that she has to click on a big button first to take her to seperate, smaller edit forms, if she wants to edit something.
    Instead of a form, it could have been a report, as it is for view only, but as i said before, i find reports to look like shit. No fixed size, scrolling like pages, data moving everywhere. Generally not neat and fixed like forms.
    Lastly, i heard the advices in here and made the pills an entirely different table with a connection to the patient, an added fields to have a history for old pills. And that meant changing almost every form i v made so far and add tabs to be able to show everything...
    Oh, Access, how fun and easy you are!(not)

  12. #12
    VBAX Contributor
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    113
    Location
    Don't go blaming Access.
    I would expect a newbie user to excel would do things 'not the Excel way'?

    In fact when I took over from a treasurer for SSAFA, he showed me his Excel workbook, where each day of the month was a column, a row was deposits and the row below it payments, and the row below that the difference. So three rows per client.
    So he would enter £300 in, £142.50 out and then do the math and enter £157.50 in the Balance row.

    ignoring the structure, I asked him 'You do know that excel can do that calculation for you, for the Balance?'. No, he did not.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •