Module Help (1 Viewer)

This is a re-post of one of a few days ago that seems to have gone down a black hole. It was either inadvertently lost or deleted, but here it is again.
What I am trying to do is avoid repeating a long line of VBA code that is used on five or six forms. I have it on the forms' OnCurrent event and on a control named [Country], the AfterUpdate event. One of the various address controls on the form and its label is either visible or not visible depending on what is in the [Country] control.
I am not sure if I should be using a function of public sub, but previous replies to the old post said public sub. But when I try to call the sub all kinds of bad things happen.
Attached is an updated sample database of what I was attempting. frmMain has the code in the events and it works fine. frmMain1 and frmMain2 were my two attempts at trying to use a module to do the same thing.
Any help you can give me would be greatly appreciated.

It's about time for me to wrap it up for the evening here, so I don't have time to produce any tested code right now, but here is how I would probably approach a situation like this.

I would take advantage of the Tag property of the Controls (labels, text boxes, etc.) involved. So, for example, in all the controls that you want to be visible when USA is selected, put USA in the Tag property. Ditto for Canada. For the other countries put Other in the Tag property. Then the code might look something like the following;


If you wanted to put this in a function that could be called from multiple forms, assuming each form had a Control named Country, it might look like;


Then you would call it in each form with;

SetVisibility Me

Again, this is untested so it may need some tweaks, but it's another way of approaching this that you might consider. I may be able to post some tested code tomorrow if you need.

I appreciate your quick reply. I thought of using the tag property, but some which would be tag "USA" might also be "CANADA" and some "CANADA" might be also "OTHER". Can a tag have more than one "name"?

Yeah, you could do that. Here is some tested code. It uses the InStr function, so some controls could just have USA or CANADA, some could have USA OTHER or CANADA OTHER and so on. I also decided the Len function would be a better option for checking if something exists in the Tag property, so modified that as well. See below;

After some mistyping on my part, I finally figured out that your solution was great. Thank you.
One of the aims of my original post was to try to figure out how public modules work. They are still a mystery to me.
But, as you have proven, sometimes there is a simple answer to a complex question.

I have been trying to tweak the code that was posted by a very helpful Beetle about a year ago. The use of the tag in a private sub works great, but since I have six users and about ten forms in which the code is used, any changes to the "Country" list present a problem to me. If I had this in a module, then I would only have to change it. Following Beetle's advice, I have written the following:

Public Sub SetVisibility(frm As Form)
Dim ctl As Control
If frm!Country = "USA" Or frm!Country = "CANADA" Or frm!Country = "ANTIGUA" Or _
frm!Country = "BAHAMAS" Or frm!Country = "BERMUDA" Or frm!Country = "BARBADOS" Or _
frm!Country = "CAYMAN ISLANDS" Or frm!Country = "DOMINICA" Or _
frm!Country = "DOMINICAN REPUBLIC" Or frm!Country = "GRENADA" Or _
frm!Country = "NETHERLAND ANTILLES" Or frm!Country = "ST. KITTS" Or _
frm!Country = "ST. MAARTEN" Or frm!Country = "ST. VINCENT" Or _
frm!Country = "TRINIDAD" Or frm!Country = "TURKS & CAICOS" Or _
frm!Country = "AMERICAN SAMOA" Or frm!Country = "ANGUILLA" Or _
frm!Country = "BVI" Or frm!Country = "GRENADA" Or frm!Country = "GUAM" Or _
frm!Country = "JAMAICA" Or frm!Country = "MONTSERRAT" Or _
frm!Country = "NORTHERN MARIANA ISLANDS" Or frm!Country = "PUERTO RICO" Or _
frm!Country = "NEVIS" Or frm!Country = "ST. LUCIA" Or frm!Country = "ST. VINCENT" Or _
frm!Country = "GRENADINES" Or frm!Country = "TRINIDAD" Or frm!Country = "TOBAGO" Or _
frm!Country = "USVI" Then
For Each ctl In frm!Controls
If Len(ctl.Tag) Then
ctl.Visible = InStr(ctl.Tag, frm!Country) > 0
End If
Next ctl
Else
For Each ctl In frm!Controls
If Len(ctl.Tag) Then
ctl.Visible = InStr(ctl.Tag, "OTHER") > 0
End If
Next ctl
End If
End Sub

When I try to call it with:
Call SetVisibility it says "Compile Error, Argument Not Optional.
So, the questions are: Have I not written the sub correctly or am I not calling it in the proper way. Any help would be greatly appreciated.

When you call the sub, you must supply the expected parameter.

Call SetVisibility(YourFormHere)

Thank you for your prompt reply. I did as you suggested and it now comes up with other errors. Instead of me trying to explain it, I have posted a sample update of the DB. The error is Error '91"; Object variable or With Block variable not set. Any help you can give me with this will be greatly appreciated.

Did you get this resolved or are you still in need of help?

Yes, and no. MarkK has been very helpful showing me how to code a module to do this and I am still working on that, but I do have a question regarding the use of "tags".

This is what I posted previously in another post. I am still mystified why the tag "OTHER" does not set the visibility.

[FONT=&quot]Code:[/FONT]
[FONT=&quot]Public Sub SetCountry(frm As Form)[/FONT]
[FONT=&quot] Dim ctl As Control[/FONT]
[FONT=&quot] If DCount("*", "tblCountries", "Country = '" & frm.Country & "'") > 0 Then[/FONT]
[FONT=&quot] For Each ctl In frm.Controls[/FONT]
[FONT=&quot] If Len(ctl.Tag) Then[/FONT]
[FONT=&quot] ctl.Visible = InStr(ctl.Tag, frm!Country) > 0[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] Next ctl[/FONT]
[FONT=&quot] Else[/FONT]
[FONT=&quot] For Each ctl In frm.Controls[/FONT]
[FONT=&quot] If Len(ctl.Tag) Then[/FONT]
[FONT=&quot] ctl.Visible = InStr(ctl.Tag, “OTHER") > 0[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] Next ctl[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] End Sub[/FONT]

[FONT=&quot]The new code works great except for the tag “OTHER”. The tag property for the various controls like [Telephone] or [TelephoneForeign] list the various countries. If it is not one of the countries that have the 10 digit telephone numbers like the USA, CANADA, Caribbean Islands, etc., then I merely type in “OTHER” to signify that the [State], [Zip4], [CanadaProvince], etc. fields will not be visible and the others by using “OTHER” will be visible. For example, for the [State] I have the tag

USA USVI GUAM PUERTO RICO MARSHALL ISLANDS NORTHERN MARIANA ISLANDS ST. CROIX

For [CanadaProvince] I have the tag CANADA.

For [TelephoneForeign] I have OTHER

So it would seem that if I have a country listed in the tag, the code works great. But if I have the tag OTHER, it won’t. Why is this?
[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]This module works fine, it is just more cumbersome:[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]Public Sub SetVisibility(frm As Form)
Dim ctl As Control
If frm!Country = "USA" Or frm!Country = "CANADA" Or frm!Country = "ANTIGUA" Or _
frm!Country = "BAHAMAS" Or frm!Country = "BERMUDA" Or frm!Country = "BARBADOS" Or _
frm!Country = "CAYMAN ISLANDS" Or frm!Country = "DOMINICA" Or _
frm!Country = "DOMINICAN REPUBLIC" Or frm!Country = "GRENADA" Or _
frm!Country = "NETHERLAND ANTILLES" Or frm!Country = "ST. KITTS" Or _
frm!Country = "ST. MAARTEN" Or frm!Country = "ST. VINCENT" Or _
frm!Country = "TRINIDAD" Or frm!Country = "TURKS & CAICOS" Or _
frm!Country = "AMERICAN SAMOA" Or frm!Country = "ANGUILLA" Or _
frm!Country = "BVI" Or frm!Country = "GRENADA" Or frm!Country = "GUAM" Or _
frm!Country = "JAMAICA" Or frm!Country = "MONTSERRAT" Or _
frm!Country = "NORTHERN MARIANA ISLANDS" Or frm!Country = "PUERTO RICO" Or _
frm!Country = "NEVIS" Or frm!Country = "ST. LUCIA" Or frm!Country = "ST. VINCENT" Or _
frm!Country = "GRENADINES" Or frm!Country = "TRINIDAD" Or frm!Country = "TOBAGO" Or _
frm!Country = "USVI" Or frm!Country = "ST. CROIX" Then
For Each ctl In frm.Controls
If Len(ctl.Tag) Then
ctl.Visible = InStr(ctl.Tag, frm!Country) > 0
End If
Next ctl
Else
For Each ctl In frm.Controls
If Len(ctl.Tag) Then
ctl.Visible = InStr(ctl.Tag, "OTHER") > 0
End If
Next ctl
End If
End Sub

Thanks.[/FONT]

Sorry it took me a bit to get back to you on this, been busy at work. My original advice last year was based on having a very small, static list of possible values. With a larger and/or dynamic list like yours, I would not use quite the same approach. It's cumbersome because, even if you get the current code problem solved to where you're not trying to manage the list in code, you still have the problem of the tags themselves. Each time you add a new value (country) to the list, you have to find the associated Controls (labels, text boxes, etc.) and add the new value to the Tag property.

Another approach might be to add a field to the Country table hold what you might define as a "group" number. The idea being that you have a lot of Countries but maybe only 4 to 5 "groups" of associated Controls (one for "USA" group countries, one for the "Canada" group, one for "Foreign" countries, etc.).

Then on your forms you can add this new field as a hidden column in your Country combo box, so that it can be referenced, and in the Tag property of your controls can simply put 1 or 2 numbers to indicate which "group" each control is associated with. The code in this case is very simple as you just need to test if each Control has a number in the Tag property that matches the number in the hidden column of the combo box. If you need to add a new Country to the list, you just add it to the table with the appropriate group number. No need to go back and modify code or Control properties.

I'm not entirely sure if this approach will work for you, but I have reattached your db, modified using this approach, so you can evaluate for yourself. If you like the approach I'm sure it will need a bit of tweaking, it's just a rough example right now. Post back if you have any questions.

Super! Easy and to the point. I suppose I should add back some of the fields so that if there are any additions to the list of countries, one would know which code to use. Just took a quick look at it. Will study it more tonight. But it sure makes life simple. Don't know why I always try to make things difficult for myself.

A quick question. You have ctl.Visible = InStr(ctl.Tag, frm!cboCountry.Column(1)) > 0. And I see how you did this. But I am curious to know if you can reference the column in the table directly.
And I looked at it again very thoroughly and the coding is simple and the results are just what I wanted. Simple is best.

There are a couple ways you could reference the table directly.

You could use DLookup, although, that wouldn't necessarily be the most efficient method if you have a lot of Controls because you would be executing a domain function for each Control with a Tag property value.

You could also use a recordset (ie open a recordset in code) to examine the table data directly. Both methods would require a few more lines of code. Post back if you have any questions about either.

if I can make an observation, the trick is, as beetle is showing you, is to let the data manage itself.

if you want the visible effect for certain countries to be X, then the solution is to add a field to the table that determines any given country as a type X country. Now all your code needs to do is to react to the type of the country.

The best place for this, is almost certainly the current event, using a function call something like

setallcontrols (countryxsetting)


Alternatively, you could have a command button on the form that selects or ignores just the X countries, or not, and requeries the form based on the selection You can do whatever you like with a simple query, if the data is structured appropriately

Thanks to both of you. The method of setting a tag just the four categories is going to work just fine. I was just curious when I asked the additional question. Not need to do anything else.