Show hidden tabs based on Listbox Column Value (1 Viewer)

ScrmingWhisprs · Mar 31, 2015

Hi,

I am designing a contact database for a diocese and the contacts record form is divided into several tabs, some of which are hidden by default. One of the controls on the form is a listbox (lboRoles), where a user can add one or more roles to contacts. The listbox has a hidden column that defines the TabIndex for the assigned role, and my goal is to make the associated tab on the form that was previously hidden, to now be visible.

For example, if a contact is assigned the role "Committee Member" and the tabindex value for that role is 3, the form should make the hidden tab (where the page index is also 3) now visible. Contacts can be assigned more than one role, so I'm trying to figure out the most efficient way to code this.

Any little nudge of guidance is appreciated!

Thanks,
Mike

MarkK · Mar 31, 2015

How are your roles saved and retrieved for one person? You say you have a ListBox "where a user can add one or more roles to contacts," but let's say that user adds those roles. Then, next Tuesday you navigate back to this same record. Does the list display those roles again for this person? How does that work in your system? That is the point where you'd show the tabs, when you populate that list, but that's sort of a tricky problem too.

Do you need help with that too, or is it just a matter of showing and hiding tab pages?

ScrmingWhisprs · Mar 31, 2015

Hi Mark,

I have a junction table (jtblRoles) to keep track of the many-to-many relationship between the contacts (tblParishioners) and the roles (tblRoles). The listbox on the contacts form shows the description and contains the hidden tabindex field, both fed from tblRoles. That's working great.

It is just a matter now of getting the hidden tabs to show based on the hidden tabindex (in Column 2) of the listbox. This database is still in development, and currently I only have 2 tabs right now that are hidden by default and would like to show based on roles, however I would need a mechanism that easily scales.

Thanks!
-Mike

MarkK · Apr 1, 2015

So then you have a loop, right, that selects the roles in a multi-select listbox? So in that loop, when you select an item in the list, at that moment also show the appropriate tab.

ScrmingWhisprs · Apr 2, 2015

Hi Mark,

The idea behind the listbox is to just simply list the Roles. Users will not be making selections in the listbox. In fact the listbox is "Locked". To add or remove roles, users would double-click the box and be presented with another form to make changes.

Once a contact's record is retrieved, the code should loop through any values present (not selected) in lboRoles.Column(2) and make those page (tab) indexes visible.

I have this so far in the form's OnCurrent event:

Code:

Dim i As Variant

For Each i In Me.lboRoles.Column(2)
Me.PAR_TabCtl.Pages(i).Visible = True
Next i

However I get a Type Mismatch error when the code runs. I'm not sure if I'm on the right track with this loop, or if there is a little more to it...

Thanks,
Mike

MarkK · Apr 3, 2015

I either don't get it, or I'm not making myself clear, but I'll take another shot here, first with my assumptions

The way I understand it is this: You have a main form that shows a person, and that person may occupy one or more roles, and for each role that person does occupy, two things must occur, 1) a list item must be selected, and 2) a tab must be shown. My underlying assumption is that there is a one-to-one correspondence between selected list items and visible tabs.

Code:

[COLOR="Green"]'my way has one loop[/COLOR]
do
   select list item
   show tab
loop

Your proposal seems to be to use the selected items in the list as a control structure to show the tabs, but then you essentially enumerate that list twice, once to populate the list itself, and then again, to show the tabs.

Code:

[COLOR="Green"]'yours has two[/COLOR]
do
   select list item
loop

for each item in list
   show tab
next

Furthermore, if your list selection loop is already working, showing the tabs will be much easier if you just leverage that loop (for which you must already have an adequate control structure--so you don't need to use the listbox.)

Is that more clear?

ScrmingWhisprs · Apr 5, 2015

I've attached a screenshot of my contact form. The items in the Roles listbox never get "selected" or highlighted on this form. They simply just list the roles assigned to the displayed contact.

The list does NOT include all possible roles that are available. There is a hidden column in the listbox (TABINDEX) that contains a number that would match the TabControl Page index number that needs to be unhidden if it exists in this list. The listbox rowsource is the following:

Code:

SELECT jtblRoles.ID, tblRoles.ROL_DESCRIPTION, tblRoles.ROL_TABINDEX 
FROM jtblRoles INNER JOIN tblRoles ON jtblRoles.ROL_ID = tblRoles.ROL_ID 
WHERE (((jtblRoles.PAR_ID)=[Forms]![frmParishioner]![PAR_ID])) 
ORDER BY tblRoles.ROL_DESCRIPTION;

For example, in the screenshot provided, the list item "CAL" has a hidden TABINDEX value of 4 and the list item "Committee Member" has a hidden TABINDEX value of 3. So when this record is retrieved, I need the hidden tabs "Committees" and "CAL" unhidden. Each of their page index values are 3 and 4, respectively.

So I need to loop through the values in the hidden column of the listbox (of items that are present, not selected or highlighted), then display the hidden tabs that have the corresponding number in the hidden column. I hope this helps.

-Mike

MarkK · Apr 5, 2015

Yes, sorry, I misunderstood how you were using your listbox.

I wouldn't use the items in a control to do what you say, rather, I would use the current event of the form, hide all the tabs, open a recordset for the current parishioner, and show the tabs they qualify for.

Code:

private sub form_current()
   HideTabs
   ShowCurrentTabs
end sub

private sub ShowCurrentTabs
   dim rst as dao.recordset
[COLOR="Green"]   'open recordset of tabs to show for current parishioner[/COLOR]
   set rst = currentdb.openrecordset("YourSQLHere")
   with rst
      do while not .eof
[COLOR="Green"]         'show the page[/COLOR]
         me.tab.pages(!TabIndex).visible = True
         .movenext
      loop
      .close
   end with
end sub

private sub HideTabs()
   dim i as integer
   for i = 0 to me.tab.pages.count
      me.tab.pages(i).visible = false
   next
end sub

Hope that helps,

ScrmingWhisprs · Apr 5, 2015

Hi Mark,

So I have modified my form's Current event to the following:

Code:

Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngPar As Long

lngPar = Me.PAR_ID 'This is the Parishioner ID number

strSQL = "SELECT jtblRoles.ID, tblRoles.ROL_DESCRIPTION, tblRoles.ROL_TABINDEX " & _
         "FROM jtblRoles INNER JOIN tblRoles ON jtblRoles.ROL_ID = tblRoles.ROL_ID " & _
         "WHERE (((jtblRoles.PAR_ID) = " & lngPar & "));"

Set rs = CurrentDb.OpenRecordset(strSQL)

With rs
    Do While Not .EOF
        Me.PAR_TabCtl.Pages(!tblRoles.ROL_TABINDEX).Visible = True
        .MoveNext
    Loop
    .Close
End With

However, I receive the following error: "Run-time error '3265': Item not found in this collection."
The debugger highlights this line of code: Me.PAR_TabCtl.Pages(!tblRoles.ROL_TABINDEX).Visible = True

The syntax for referring to a Tab Control's pages looks okay to me....

MarkK · Apr 5, 2015

this line . . .

Code:

Me.PAR_TabCtl.Pages(!tblRoles.ROL_TABINDEX).Visible = True

should be . . .

Code:

Me.PAR_TabCtl.Pages(!ROL_TABINDEX).Visible = True

The recordset knows not to include the table name portion in the field name.

ScrmingWhisprs · Apr 5, 2015

Now I get a Type Mismatch error. I'm thinking I need to declare the ROL_TABINDEX value as Long or an Integer? But I'm not sure how to go about that...

MarkK · Apr 5, 2015

What data type is the tab index field? You can also do a conversion, like . . .

Code:

Me.PAR_TabCtl.Pages(CLng(!ROL_TABINDEX)).Visible = True

. . . depending on the datatype in the TabIndex field.

ScrmingWhisprs · Apr 11, 2015

Hi Mark,

The datatype for the Tabindex field is Long, so I finally got around to adding your suggestion and it now works perfectly!!

Thank you so much for all your help!

-Mike

MarkK · Apr 11, 2015

You bet, good work getting it figured out.

Show hidden tabs based on Listbox Column Value (1 Viewer)

ScrmingWhisprs

I &lt;3 Coffee Milk.

MarkK

bit cruncher

ScrmingWhisprs

I &lt;3 Coffee Milk.

MarkK

bit cruncher

ScrmingWhisprs

I &lt;3 Coffee Milk.

MarkK

bit cruncher

ScrmingWhisprs

I &lt;3 Coffee Milk.

Attachments

MarkK

bit cruncher

ScrmingWhisprs

I &lt;3 Coffee Milk.

MarkK

bit cruncher

ScrmingWhisprs

I &lt;3 Coffee Milk.

MarkK

bit cruncher

ScrmingWhisprs

I &lt;3 Coffee Milk.

MarkK

bit cruncher

Similar threads

Users who are viewing this thread

Show hidden tabs based on Listbox Column Value (1 Viewer)

I &amp;lt;3 Coffee Milk.

bit cruncher

I &amp;lt;3 Coffee Milk.

bit cruncher

I &amp;lt;3 Coffee Milk.

bit cruncher

I &amp;lt;3 Coffee Milk.

Attachments

bit cruncher

I &amp;lt;3 Coffee Milk.

bit cruncher

I &amp;lt;3 Coffee Milk.

bit cruncher

I &amp;lt;3 Coffee Milk.

bit cruncher

Similar threads

Users who are viewing this thread

I <3 Coffee Milk.

I <3 Coffee Milk.

I <3 Coffee Milk.

I <3 Coffee Milk.

I <3 Coffee Milk.

I <3 Coffee Milk.

I <3 Coffee Milk.