Solved Shorten IF Then to For Next in Access 2000 in VBA

VacaOldguy

Razz
Thread Starter
Joined
Oct 23, 2020
Messages
5
Am running Windows 10 and using Access 2000, soon to be upgrading to Access 2019, and am not new to Access but always have the long approach to code. With that said, I am trying to eliminate Long "If then statements in VBA" by using a "For Next" Loop and have tried several instances that have not worked. This is my "If then" at present upon form load:
If Me.FieldName = "EnteredName" Then
Me.Q11.Visible = False
Me.Q12.Visible = False
Me.Q13.Visible = False
Me.Q14.Visible = False
Me.Q15.Visible = False
Me.Q16.Visible = False
Me.Q17.Visible = False
Me.Q18.Visible = False
Me.Q19.Visible = False
Me.Q20.Visible = False
Me.Q21.Visible = False
Me.Q22.Visible = False
Me.Q23.Visible = False
Me.Q24.Visible = False
Me.Q25.Visible = False
Me.Q26.Visible = False
Me.Q27.Visible = False
Me.Q28.Visible = False
Me.Q29.Visible = False
Me.Q30.Visible = False
'Thru Q50'
End if

Would appreciate any help to make a working "For Next" Loop to make this function work in shorter form of "For Next"!
Thanks in advance for any help. Am retired and this is my hobby developing for my own use.

Appreciate it.......VacaOldguy
 
Joined
Jun 17, 2002
Messages
2,556
have you looked at "For EACH" ... Control... for the controls you want to manage enter a value in the TAG property, then do something like:

I don't have a precise answer for you but, you can look at this code. I use it to check required fields. you might be able to revise your IF to set the property.

Code:
Dim ctl As Control
Dim strMessage As String
Dim strControl As String

    For Each ctl In Screen.ActiveForm.Controls
        If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox) And ctl.Tag = "Req" Then
'only build a message for the first required control without an entry
          
'this function requires that the label for the control have the same name as the control
'followed by _Label
            strMessage = Screen.ActiveForm(ctl.ControlName & "_Label").Caption & " is a required field."
'set the strControl variable to the name of the required control that is not filled in
            strControl = ctl.ControlName
            End If
      
    Next
 

VacaOldguy

Razz
Thread Starter
Joined
Oct 23, 2020
Messages
5
Ziggy1 Thanks for the code! Have a more than basic knowledge of VBA , and will try to digest this code and possibly adapt it but as of right now...I'm pretty lost to understand what this will do but give me some time and I will try to figure it out. Will also look at For Each in access 2000. Just got my Access 2019 and cross my fingers, hope all goes well with it. Again, Thanks for the reply and I'll post if this has helped in any way. More or less, I develop custom databases for hobbyists and could use some expertise in understanding more VBA coding, but at 69yrs of age....ALL things hurt except my thought processes so as they say "less code is more".
Again regards!!!
 
Joined
Jun 17, 2002
Messages
2,556
looking at your code I thought if .... If Me.FieldName = "EnteredName" Then = TRUE

then with the For Each... when the control has some key word in the TAG property, you could loop through the controls and set visible false.

I just haven't had a chance try to adapt to your scenario.

Code clean up is always a chore you put off for a rainy day :)
 
Joined
Jun 17, 2002
Messages
2,556
I had some time to play around, this is what I would do


Code:
If Me.FieldName = "EnteredName" Then

    Dim X As Integer
    Dim ctlN As String

        For X = 1 To 50
       
        ctlN = "Q" & X
       
        Me.Controls(ctlN).Visible = True
       
        Next X

End If
 

VacaOldguy

Razz
Thread Starter
Joined
Oct 23, 2020
Messages
5
Ziggy1

Thank you so much for the follow up to my post! The code that you sent worked perfectly and I no longer have to code all those lines of 'Me.whatever.Visible = False'. Am going to leave this thread open for another day to experiment with this using Access 2019, but am relatively sure that all will be okay. Again, thanks for the review of my post and your help saved me tons of time.

Razz
 
Joined
Jun 17, 2002
Messages
2,556
No problem, I have not replied on this forum for some time ( you may notice I've been here for a long time). My Knowledge of Access goes back to Access 97.... 97 - 2000 was a mess and 2000 - 2003 introduced some core changes but in general all versions since 2000 will work. I have many old DB's created in the older versions that run without issue.
 

VacaOldguy

Razz
Thread Starter
Joined
Oct 23, 2020
Messages
5
Same here my friend and I have some 46 odd databases already developed and in the hands of many with a run-time version of access going back just as far as yourself. Adapted the code you gave and put it to use with 4 other problem areas in Global code as functions and call each individually from different forms. Effortless coding as it reduced the code in each form significantly. Probably knew how to do it myself but memory and programming are like trying to freeze ice in Hades! Again thanks for the quick help with the issue and don't know if we are permitted to give out email addresses as I'd like to keep in contact with older geezers like us!

Razz
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top