Hide/Show columns based on combo-box

[mari_hitz]

Hi everyone,

I would like to know if the following is possible: I have a table, I have a form based on that table (datasheet view) and I would like for certain columns to visible or not based on the input on a Combobox (list of countries) that is based on another form.
I have searched on the web and I found that the following:
On a button based on the main menu form:

Private Sub Go_Click()

    Dim stDocName As String
    Dim stOpen As String
 
    stOpen = Me!cboDest
 
    stDocName = "Datasheet"
    DoCmd.OpenForm stDocName, , , , , , stOpen

and then on the event "on open" of the form Datasheet I have inserted the following:

Private Sub Form_Open(Cancel As Integer)
Select Case OpenArgs
  Case "USA"
    Forms![Overflow]![Tasks].Visible = True
    Forms![Overflow]![Constant Number (Min)].Visible = True
    Forms![Overflow]![Country].Visible = True
    Forms![Overflow]![Transactional Time (Min)].Visible = True
    Forms![Overflow]![Business Visitor].Visible = True
    Forms![Overflow]![Non-Visa Required].Visible = True
    Forms![Overflow]![Work Permit (Home India)].Visible = True
    Forms![Overflow]![Work Permit (Home Phillipines)].Visible = True
    Forms![Overflow]![Work Permit (ROW)].Visible = True
    Forms![Overflow]![P750].Visible = True
    Forms![Overflow]![P44].Visible = True
    Forms![Overflow]![Complex].Visible = True
    Forms![Overflow]![Outbound].Visible = True
    Forms![Overflow]![Inbound Compliance].Visible = True
    Forms![Overflow]![Inbound Non Compliance].Visible = True
    Forms![Overflow]![Inbound Prior Years].Visible = True
    Forms![Overflow]![Outbound Equalized].Visible = True
    Forms![Overflow]![Outbound Not Equalized].Visible = True
    Forms![Overflow]![Inbound Compliance].Visible = True
    Forms![Overflow]![EEAA].Visible = False
    Forms![Overflow]![Work Permit].Visible = False
    Forms![Overflow]![Schengen].Visible = False
  Case "United Kingdom"
      Forms![Overflow]![Tasks].Visible = True
    Forms![Overflow]![Constant Number (Min)].Visible = True
    Forms![Overflow]![Country].Visible = True
    Forms![Overflow]![Transactional Time (Min)].Visible = True
    Forms![Overflow]![Business Visitor].Visible = False
    Forms![Overflow]![Non-Visa Required].Visible = False
    Forms![Overflow]![Work Permit (Home India)].Visible = False
    Forms![Overflow]![Work Permit (Home Phillipines)].Visible = False
    Forms![Overflow]![Work Permit (ROW)].Visible = True
    Forms![Overflow]![P750].Visible = True
    Forms![Overflow]![P44].Visible = True
    Forms![Overflow]![Complex].Visible = True
    Forms![Overflow]![Outbound].Visible = True
    Forms![Overflow]![Inbound Compliance].Visible = True
    Forms![Overflow]![Inbound Non Compliance].Visible = True
    Forms![Overflow]![Inbound Prior Years].Visible = True
    Forms![Overflow]![Outbound Equalized].Visible = True
    Forms![Overflow]![Outbound Not Equalized].Visible = True
    Forms![Overflow]![Inbound Compliance].Visible = True
    Forms![Overflow]![EEAA].Visible = True
    Forms![Overflow]![Work Permit].Visible = True
    Forms![Overflow]![Schengen].Visible = True
  Case Else
  'Nothing
End Select
End Sub

I have done it as a test, but it appears that it does not work. It opens the forms, but it does not hide or show the columns based on the option, it brings all the columns. Could you please help me?

Thanks in advance!

 

[pbaldy]

Make sure the combo is returning the value you think it is, rather than an ID value.

 

[MarkK]

In datasheet view you want to use .ColumnHidden, not .Visible.

 

[mari_hitz]

Hi Paul!

Based on your question I had tried to change to the number ID in "Case" and it is the same.

Please find attached the database, I have also created a Query based on the table and it is still not working....

 

[mari_hitz]

Hi Paul,

I have tried columhidden also and it did not work :S any word of advice? I have attached my database in my previous message.

 

[MarkK]

Your database is a later version of Access than I have, so I can't open it, but if you save it as an older version and post that, I'll take a look.

 

[RainLover]

Your database is a later version of Access than I have, so I can't open it, but if you save it as an older version and post that, I'll take a look.

I have the same problem. Wrong Version. I need 2003.

Not really difficult to solve but as suggested it could be a combination of incorrect settings.

Please post in Access 2003.

 

[mari_hitz]

Hi Rainlover and lagbolt,

please find the access database in a previous version attached. Note that took me a bit to convert because a lot of things that I had with the 2007 version cannot be converted to 2000 so I had to erase a lot of stuffs, not sure if it works. In addition I forgot to mention that I don't want only for the columns to be hidden/shown when you choose an option of the Combo box, I also want for the records to be filtered based on the combo box option chosen, I don't know if what I am looking for is possible, my brains are going crazy trying to solve this :banghead:

 

[MarkK]

I still get an unrecognized database format error, sorry. Also, an Access 2000 database will have an .mdb extension.

 

[mari_hitz]

Hi lagbolt,

I am really sorry, I posted the same database. Here it goes again.
Do you think it is possible to filter the data and also hide/show columns based on combo box option choosen?

 

[RainLover]

Good Evening.

10:03 PM here, which is my favourite time of the day?

Now let's get you sorted.

First you MUST set up your relationships up correctly or nothing will work.

Country in tblBA is related to, or should I say should be related to the ID in tblCountries.

In tblBA your Field is text and shows USA. It should be Number and read 173 to correspond to USA in the Countries Table.

So delete the current Text Field and replace it with a Number field.

This may affect your Combo Box so have a look at that when finished. If you Combo select USA you in actual effect select 173. Then 173 can be related to the BA Table. So only USA shows. This is your filter.

We can hide your columns later.

Can you change what you have and post that.

BTW. This is a big lesson to learn. It is simple but can and has confused many a person. You will use this technique in just about every database you ever write.

 

[mari_hitz]

Hi RainLover!

Thanks for your answer! Please find attached the database with the relationship created. Actually in my database made on 2007 I had the relationship created, it did not created when I changed it to 2000 version.
I have another problem now, the form does not appears on datasheet view :banghead: it appears that it returns only the records for USA, however I wouldn't know because I can't see all the records.
The worst part is that this is only the beginning, I was finishing my last database which made everything correctly, and now my managers have "changed the perspective" and I am busting my brains out.

Here it is 9.15 AM, I really appreciate all your effort and work on helping me. Regards!

 

[RainLover]

NEVER, Ever use lookups in Tables.

It is correct to use them in Forms but Never Tables.

Have a read of This.

http://access.mvps.org/access/lookupfields.htm

 

[mari_hitz]

Thanks RainLover,

I have changed to what you have mentioned. However, I have only one question, I need that in the column "Country" to appear USA not a number, is this possible? Because this will be column people will see regardless the country, and people will not understand numbers and they refer to the country ID.

Thanks

 

[RainLover]

The properties box of the Combo.

Column Count = 2
Column Widths - 0, 2.5
Bound Column = 1
Limit to List - Yes
Row Source = SELECT tblCountries.ID, tblCountries.Country FROM tblCountries ORDER BY tblCountries.Country;

More to come

 

[RainLover]

This should help a bit more.

After this you need the code to hide columns etc.

I hope someone else can jump in as it is late for me.

 

[mari_hitz]

Thanks RainLover,

Hope you can let me know what's wrong in here and how to solve this, I have to present this database on Friday and would be good to have it on time.

Thanks again for everybody's help, you are awsome!

 

[MarkK]

This doesn't make sense to me to solve this problem this way. I would expect to see a country table on one side, and a tasks table on the other, and then a join table between them that relates the task to the country. If the item is true, then a record exists, if not, not. That will get rid of all those columns and you won't have to hide them. They should be in rows.
Does that make sense? These are the tables I would expect to see . . .

tCountry
CountryID (PK)
Country

tTask
TaskID (PK)
TaskDescription
TransactionTime

tTravellerType
TravellerTypeID (PK)
TravellerType

tTypedCountryTask
TypedCountryTaskID (PK)
CountryID (FK)
TaskID (FK)
TravellerTypeID (FK)

So that's my guess from what it looks like you are trying to do. It looks like you need to store the list of Tasks associated with processing specific types of Travelers from different Countries.
hope this helps,

 

[RainLover]

mari_hitz

You haven't replied to Lagbolt as yet.

He has brought up a good point which really should be addressed before going on. Most importantly before Friday.

Are you having problems? Are you lost?

Please come back with your current position.

 

[mari_hitz]

Lagbolt, thanks for your reply, however what you are mentioning - even if I don't fully understand it- I don't think fits what I am looking for since, you see, I have a list of different tasks, more than 500 that will be inserted in here and they will vary they won't be always the same. I could have a list with the tasks, but the times differ depending on the country. In addition I am looking to build a database that is easy to maintain in time.

RainLover: I loved the database you had provided, it makes the filter, however it does not show/hide columns based on the information entered in the combobox, I assume that you have left that part for me. I have tried with things I saw on the internet: Forms!Formname!Field.ColumnHidden = -1 and Me!Fieldname.Visible = Yes and nothing works. Like you have said, I am little bit frustrated and lost here.
Fin this post what I have tried to do.

I am sorry for not replying you sooner, I did not see the answers. Thanks for all your help and reply, hope I can resolve this