Access Tab Control - Set Pages Visibilty based on Subform Records

Question

I know how to do this, but wondering if I might be able to write a more elegant solution. I have a form with a tab control. The control has 14 pages, each one, with it's own sub form. One of the pages (pgRequirements) has a subform of requirements, with a combo control "Requirement Type". It is a continuous form, so the user can add as many requirements as they want, for the main record.

There are 9 of those requirements, which have their own tab control page / sub form. I want to set visibility of those tab control pages, based on this parent's sub form requirements. So a current main record, can have multiple sub-requirement records. If any of those match e.g. requirement type A, than page A should be visible, otherwise it should not be.

I need this code to run anytime the main form is loaded, and the detail is made visible (meaning a main record has been chosen from a find form). Also anytime a requirement record is added or removed. The below is assuming that the parent-child links on the main form to subform will limit the requirement records, to just those that are for the current main record.

Here is the simple code, that will do the job, but is probably over-written:

If Me.FKRequirementType.Column(1) = "ReqType1" Then
    Me.Parent!pgReqType1.Visible = True
Else
    Me.Parent!pgReqType1.Visible = False
End If

If Me.FKRequirementType.Column(1) = "ReqType2" Then
    Me.Parent!pgReqType2.Visible = True
Else
    Me.Parent!pgReqType2.Visible = False
End If

If Me.FKRequirementType.Column(1) = "ReqType3" Then
    Me.Parent!pgReqType3.Visible = True
Else
    Me.Parent!pgReqType3.Visible = False
End If

If Me.FKRequirementType.Column(1) = "ReqType4" Then
    Me.Parent!pgReqType4.Visible = True
Else
    Me.Parent!pgReqType4.Visible = False
End If

Thanks!

---

EDIT

I turned this into a public function, so I can call it from anywhere. One problem. It's not working lol (small problem). I don't get any errors, but all the tab control pages are visible. When I add a new record, most of them should be hidden. I have a tblReqType table, with all the requirement types. I added a column to this, with the exact name of it's corresponding tab control page name, so I can loop through that table, for all records where that page name is not null, and set their page visible or not, based on the current main record ID having a record-requirement (cross reference table) record for each requirement type.

This is the public function I wrote. Can anyone help me understand what I'm missing in these loops for setting the visibility true (vtrue) vs setting the visibility false (vfalse)

Public Function ShowRequirements()
    Dim db As DAO.Database
    Dim strRstVTrue As String
    Dim rstvTrue As DAO.Recordset
    Dim strRstVFalse As String
    Dim rstvFalse As DAO.Recordset
    Dim strFieldName As String

    'Setup the recordset
    Set db = CurrentDb
    strRstVTrue = "SELECT tblMRecordRequirements.ID, tblMRecordRequirements.FKMC, tblReqType.txtRequirementPage " & _
    "FROM tblMRecordRequirements LEFT JOIN tblReqType ON tblMRecordRequirements.FKRequirementType = tblReqType.ID " & _
    "WHERE tblReqType.txtRequirementPage Is Not Null AND tblMRecordRequirements.FKMC = " & Nz(Forms!frmMRecords!ID, 0)

    strRstVFalse = "SELECT tblReqType.ID, tblReqType.txtRequirementPage, tblMRecordRequirements.FKMC " & _
    "FROM tblReqType LEFT JOIN tblMRecordRequirements ON tblReqType.ID = tblMRecordRequirements.FKRequirementType " & _
    "WHERE tblReqType.txtRequirementPage Is Not Null AND tblMRecordRequirements.FKMC <> " & Nz(Forms!frmMRecords!ID, 0)

    Set rstvTrue = CurrentDb.OpenRecordset(strRstVTrue, dbOpenDynaset, dbSeeChanges)
    Set rstvFalse = CurrentDb.OpenRecordset(strRstVFalse, dbOpenDynaset, dbSeeChanges)
    strFieldName = "txtRequirementPage"
    Do While Not rstvTrue.EOF
        Forms!frmMRecords.tbMRecordSubs.Pages(rstvTrue.Fields(strFieldName)).Visible = True
    Loop
    Do While Not rstvFalse.EOF
        Forms!frmMRecords.tbMRecordSubs.Pages(rstvFalse.Fields(strFieldName)).Visible = False
    Loop
End Function

If anyone can help me figure out my stupidity, you deserve an up vote, a check mark, and a cookie.

---

EDIT again

Below is updated code for the public function. I fixed the rs for the true query, and I added in the MoveNext for the loops.

Public Function ShowRequirements() Dim db As DAO.Database Dim strRstVTrue As String Dim rstvTrue As DAO.Recordset Dim strRstVFalse As String Dim rstvFalse As DAO.Recordset Dim strFieldName As String

    'Setup the recordset
    Set db = CurrentDb
    strRstVTrue = "SELECT tblMRecordRequirements.ID, tblMRecordRequirements.FKMC, tblReqType.txtRequirementPage " & _
    "FROM tblMRecordRequirements LEFT JOIN tblReqType ON tblMRecordRequirements.FKRequirementType = tblReqType.ID " & _
    "WHERE tblReqType.txtRequirementPage Is Not Null AND tblMRecordRequirements.FKMC = " & Nz(Forms!frmMRecords!ID, 0)

    strRstVFalse = "SELECT tblReqType.ID, tblReqType.txtRequirementPage, tblMRecordRequirements.FKMC " & _
    "FROM tblReqType LEFT JOIN tblMRecordRequirements ON tblReqType.ID = tblMRecordRequirements.FKRequirementType " & _
    "WHERE tblReqType.txtRequirementPage Is Not Null AND tblMRecordRequirements.FKMC <> Is Null"

    Set rstvTrue = CurrentDb.OpenRecordset(strRstVTrue, dbOpenDynaset, dbSeeChanges)
    Set rstvFalse = CurrentDb.OpenRecordset(strRstVFalse, dbOpenDynaset, dbSeeChanges)
    strFieldName = "txtRequirementPage"
    Do While Not rstvTrue.EOF
        Forms!frmMRecords.tbMRecordSubs.Pages(rstvTrue.Fields(strFieldName)).Visible = True
    rstvTrue.MoveNext
    Loop
    Do While Not rstvFalse.EOF
        Forms!frmMRecords.tbMRecordSubs.Pages(rstvFalse.Fields(strFieldName)).Visible = False
    rstvFalse.MoveNext
    Loop
End Function

---

EDIT REDUX

I think I may have it worked out, but let me know what you all think. I really appreciate all your thoughts on this, as I know you all have a lot of experience not just in figuring out these kinds of challenges, but ensuring the code is good and not prone to issues.

Here is where I am at:

Public Function ShowRequirements()
    Dim db As DAO.Database
    Dim db2 As DAO.Database
    Dim strRstVTrue As String
    Dim rstvTrue As DAO.Recordset
    Dim strRstVFalse As String
    Dim rstvFalse As DAO.Recordset
    Dim strFieldName As String

    strFieldName = "txtRequirementPage"
    Set db = CurrentDb
    Set db2 = CurrentDb

    strRstVTrue = "SELECT tblReqType.txtRequirementPage " & _
    "FROM tblReqType LEFT JOIN tblMRecordRequirements ON tblMRecordRequirements.FKRequirementType = tblReqType.ID " & _
    "WHERE tblReqType.txtRequirementPage Is Not Null AND tblMRecordRequirements.FKMC = " & MCID

    strRstVFalse = "SELECT tblReqType.txtRequirementPage " & _
    "FROM tblReqType LEFT JOIN tblMRecordRequirements ON tblMRecordRequirements.FKRequirementType = tblReqType.ID " & _
    "WHERE tblMRecordRequirements.ID Not In (Select ID From [tblMRecordRequirements] WHERE [tblMRecordRequirements]![FKMC] = " & MCID & _
    ") AND tblReqType.txtRequirementPage Is Not Null;"

    Set rstvTrue = db.OpenRecordset(strRstVTrue, dbOpenDynaset, dbSeeChanges)
    Set rstvFalse = db2.OpenRecordset(strRstVFalse, dbOpenDynaset, dbSeeChanges)
    Do While Not rstvTrue.EOF
        Forms!frmMRecords.tbMRecordSubs.Pages(rstvTrue.Fields(strFieldName)).Visible = True
    rstvTrue.MoveNext
    Loop
    Do While Not rstvFalse.EOF
        Forms!frmMRecords.tbMRecordSubs.Pages(rstvFalse.Fields(strFieldName)).Visible = False
    rstvFalse.MoveNext
    Loop
End Function

Answer 1

I need this code to run anytime the main form is loaded, and the detail is made visible (meaning a main record has been chosen from a find form). Also anytime a requirement record is added.

Put the code you shared inside a sub procedure and call the sub procedure from Form_Load(), Form_Current(), Form_AfterInsert() event handler, etc.

As for elegance, I'd focus on maintainability and efficiency rather than looks, but concise code is also nice. 1) You can use a With block to reduce redundant object method calls, but that'll only work for one reference at a time. 2) Instead create another variable to temporarily hold a value/object from a series of dot property-accessors. 3) It looks like pages and column values are already numbered with a consistent naming pattern, so leverage that in a loop. 4) Comparison operations in VBA are largely Boolean operations, so they return True or False. The result of an entire Boolean expression can be assigned to another Boolean variable/property. (Boolean operations can also return Null... which is usually, but not always, treated like False. If you're certain that your data doesn't have Null values, then you can simplify the code and ignore this issue. If the data can contain null, then you need to adjust the code appropriately.)

Me.Parent!pgReqType1 is calling the default property of the Parent form which is Controls, which default property is Item. The bang operator ! passes the code text as a string into the collection Item method. In short, it is equivalent to Me.Parent.Controls.Item("pgReqType1").

Dim i as integer
Dim ctls as Controls
Dim reqValue as string

Set ctls = Me.Parent.Controls
reqValue = Me.FKRequirementType.Column(1)

For i = 1 to 4
  ctls.Item("pgReqType" & i).Visible = (reqValue = "ReqType" & i)
Next i

About all I can do is translate the specific code snippet you show. I have the feeling there is probably more to it than this, because the code snippet you shared ensures that there will only be one tab visible: It is testing the same column value multiple times which could only have one value. Bug? Incomplete example?

Answer 2

This really goes against my better judgement of Stack Overflow principles --to not answer multi-part, continuing debugging questions-- but I really want a cookie.

How could the posted code have ever worked, since you are not moving through either recordset? There are no MoveNext calls. That means that both recordsets are empty or an error is being thrown that is being ignored somewhere (i.e. On Error Resume Next). Otherwise, it should lock up Access with infinite loops. Sometimes you can stop the code with Ctrl+Break, but not always successful in Access.

More precise table schema is required to properly interpret your data, but I'll make some assumptions. You state that tblReqType contains all requirement types. I'll assume that tblMRecordRequirements contains rows only for requirements which are "applied" (a.k.a. "on", "selected") for the ID value in tblMRecordRequirements.FKMC. Assuming the converse, if there are no rows in tblMRecordRequirements with ID in tblMRecordRequirements.FKMC for a given tblMRecordRequirements.FKRequirementType, then the requirement is not "applied" to that ID.

Does every row in tblReqType have a value in txtRequirementPage, or do some rows have null values? Also, can multiple requirements have the same page specified? Or is it a true one-to-one requirement-to-page mapping with no null values?

First of all why would the first query not be an INNER JOIN, since I assume that only records that match in both tables should be returned for the Visible = True condition? Depending on your answers above, this would probably make the condition tblReqType.txtRequirementPage Is Not Null unnecessary in the first query.

Simply reversing a LEFT JOIN will not return what you want especially if you select all other ID values ( tblMRecordRequirements.FKMC <> Nz(Forms!frmMRecords!ID, 0) ). All that does is give you the requirements for every other ID values. Not only will that be inefficient since it could return many, many irrelevant records, it could be likely that over all other ID values that every possible requirement would be applied, so that the second query will essentially cause all requirements to be invisible.

Further picky observations:

• If Forms!frmMRecords!ID is null, then you might as well not even execute the queries. You should check that value for null separately and perform appropriate actions rather than letting that specific condition fall through the other code, even if the end side effect is what you desire. It makes the code harder to debug and interpret properly. In other words write code that does "If ID is null, set all pages to visible = false, then exit the sub (i.e. skip other code)"
• It's more efficient to get a read-only snapshot rather than an fully-updatable Dynaset recordset: Too much overhead just for looping through without data manipulation.
• Proper break points, debug output and error handling code can help identify bad code. It is worth tracing through results of recordsets manually to inspect values and proper SQL syntax.

Try this:

Public Sub ShowRequirements()
    Dim db As DAO.Database
    Dim iID As Long '? Assuming long integer
    Dim strSQL As String
    Dim rsTabs As DAO.Recordset

    On Error Resume Next
    iID = -1 '* Set to bogus value
    If Not IsNull(Forms!frmMRecords!ID) Then
      iID = Forms!frmMRecords!ID
    End If

    If iID = -1 Or Err.Number <> 0 Then
      '* Problem accessing ID control on form (empty recordset, new record row, etc.)
      '*  or it is null

      'Set all tab pages to Visible = False?
      Exit Sub
    End If

    On Error GoTo Catch
    '* Setup the recordset
    Set db = CurrentDb

    '* Use embedded query (replacable with saved query) for filtering on ID values.
    '* This is critical so that the LEFT JOIN does not return or filter records
    '* based on other ID values.
    strSQL = _
      "SELECT tblReqType.ID, tblReqType.txtRequirementPage, (IDReq.FKRequirementType Is Not Null) As ShowTab " & _
      " FROM tblReqType LEFT JOIN" & _
        " (SELECT MReq.FKRequirementType FROM tblMRecordRequirements AS MReq " & _
          " WHERE MReq.FKMC = " & iID & ") AS IDReq" & _
        " ON tblReqType.ID = IDReq.FKRequirementType" & _
        " WHERE tblReqType.txtRequirementPage Is Not Null"

    Set rsTabs = db.OpenRecordset(strRstVTrue, dbOpenSnapshot, dbReadOnly)
    Do While Not rsTabs.EOF
        Forms!frmMRecords.tbMRecordSubs.Pages(rsTabs!txtRequirementPage).Visible = rsTabs!ShowTab
        rsTabs.MoveNext '* Advance recordset.  (Avoid infinite loops.)
    Loop

CloseAll:
    On Error Resume Next
    '* Best habit to explicitly close recordsets and database connections, even when not necessary (since they'll close automatically when they go out of scope)
    If Not rsTabs Is Nothing Then
      rsTabs.Close
      Set rsTabs = Nothing
    End If
    Set db = Nothing

    Exit Sub
Catch:
    '* At least report error for development
    Debug.Print "ShowRequirements(): Error: " & Err.Number & ": " & Err.Description

    '* Show MsgBox or update form status control?
    '* Set all tab pages to Visible = False?
    '* Form state could be misleading without proper user notification and/or error handling

    Resume CloseAll
End Sub