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

[jim_koug]

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.

[June7]

[] 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".

[jim_koug]

Thank you for your quick and thorough reply, 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.

[] 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.

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"...

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

Thank you, thank you, thank you.

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!

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

[Gasman]

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?

[jim_koug]

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

[Gasman]

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.

[xps350]

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?

[Gasman]

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

[jim_koug]

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.

[June7]

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

[jim_koug]

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)

[Gasman]

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.