Form with Tab Control - Set visibility of Pages Based on Subform Records

[misscrf]

I have a database with a SQL back end. This is the process I am currently debugging:

1) Open application to Main menu
2) Click button on main menu to go to the "Main Record Form"
3) Main record form has detail hidden until an existing record is found or a new record is added (2 buttons on form header)
4) Click to add a new record. A pop-up form opens and the "Main Record" form is made visible = false
4.a) The pop-up form is so the user can choose a "contact" and a "match", as the hierarchy is contact --> match --> main record. A User must assign a new record to a match, being that each match belongs to a contact.
4.b) Click a button on the pop-up form, and on the main form, code is run to go to a new record, updating the match FK into that record
4.c) the pop-up form is closed, and the "Main Record" form is made visible, but it doesn't look like the form is really on the record it should be on
5) Another option is to find an existing main record.
5.a) choose from 2 drop downs - contact and/or match, to filter the main records listbox listing of all records, which are subsets of contacts --> matches
5.b) choose a record from listbox, and click button to go back to main record form
All of this is complicated because I'm trying to control which pages on a tab control are visible, based on the main record's sub requirement records. A main record can have many requirements, which are set on a subform, which exists on 1 page of the tab control. I have a tblReqType table, with the requirement types. This has a column called txtRequirementPage, which has the exact name of the corresponding tab control page. Not all requirements have a page, so some will be null. The requirement pages only correspond to 1 requirement type. So 15 tabs, 9 correspond to a requirement type. There are 12 requirement types, only 9 of which have a corresponding page to be visible or not, based on the main record having that requirement.

This is the public function I have been working on, to ensure only the tab control pages that should be visible, are.

Public Function ShowRequirements(MCID As Integer)
    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
    
    'Setup the recordset
    Set db = CurrentDb
    Set db2 = 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 Is Null"
    
    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 = db.OpenRecordset(strRstVTrue, dbOpenDynaset, dbSeeChanges)
    Set rstvFalse = db2.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

It looks like it sets them all not visible, when I go to a contract that has no requirement sub records. It flips one on (page = visible) , when I add one (sub record/requirement record), but when I add another, it makes the first go away, and flickers and doesn't seem to do the proper visible/not visible setting to correspond to the main record's current sub requirement records.

Any help would be greatly appreciated!

Thanks!

ps. I started this in a different thread, edited and wanted to delete it (because it wasn't my main issue, and wasn't formed well), but I can't seem to delete it.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Andrzejek]

A few questions:
1. This is a 'Public' code, yet it could only be used with [tt]Forms!frmMRecords.tbMRecordSubs[/tt] That defeats the 'Public' part.
2. This is a 'Function' which is supposed to return a value, which is does not.

So you may as well have a [tt]Private Sub ShowRequirements(MCID As Integer)[/tt] instead.

3. Do you really have a field in the [tt]tblReqType[/tt] table named [tt]txtRequirementPage[/tt] ?
4. How many records do you have in [tt]rstvTrue[/tt] and in [tt]rstvFalse[/tt]?

From your logic it looks to me that you should always have some records in one recordset and nothing, zero in the other, and you should never have records in both recordsets at the same time.
Is that correct?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[misscrf]

I made this a public function, so I call it from multiple places:

1) Add New Main Record pop-up form, which opens from a button on the Main Record form. The main record form is set to visible = false, while the user picks a contact and match, to assign the new record to. Then I run this, which would set all requirement pages to visible = false, as there would be no sub records for a new record.

2) Find Main Record pop-up form, which opens from a button the Main Record form. The main record form is set to visible = false, while the user picks a contact and/or match, to filter a listbox of main records to choose form. Then I run this, when a main record is chosen, to set the right requirement pages should be visible = true/false, based on the chosen main record's sub requirement records.

4) from the main form - requirements sub form, on change of the combo, and on current of the form - to catch any time a user adds or deletes a main record - sub requirement record.

---

Yes, I really have a field in tblReqType named txtRequirementPage. This was recommended to me, so I could match requirement to page. That way, I don't have to make an explicit loop of each requirement type/page.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Andrzejek]

OK, so you do have a field in tblReqType named txtRequirementPage
And I see you need to keep in this field the valid names of the Pages of your tab control tbMRecordSubs - risky approach if you ask me.

You did not answer 4. but it looks like your rstvTrue and rstvFalse have to include all Pages' names of your tab control. I would be tempted to try something like this:

strFieldName = "txtRequirementPage"
[blue]
With Forms!frmMRecords.tbMRecordSubs
    For X = 0 To .Pages.Count - 1
        .Page(X).Visible = False
    Next X
End With
[/blue]
Do While Not rstvTrue.EOF
    ...

Code not tested.

And got rid of [tt]rstvFalse [/tt]

Stepping thru the code and see what's going on will help.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[misscrf]

Let me make sure I clarify something here. There are 15 requirement types in the tblReqType. Only 12 of those have a corresponding page name. The rest of those have a null txtRequirementPage. Also, on the main record form, the tab control has 20 pages. Only 12 of those correspond to a requirement type. The rest will always need to be visible.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Sorry for your question 4:
4. How many records do you have in rstvTrue and in rstvFalse?

This is completely dependent on the main record I am on. For instance a new record added would have 0 true and 12 false (all requirement types that have a page, would be false because the new main record has no sub-requirement records yet). For a current record, it's directly dependent on how many requirements (of ones that have a corresponding page) exist for that main record. We could have 3 req's and then 9 would be false, etc.

I can do a debug.print to get a dcount of those, to check.

misscrf

It is never too late to become what you could have been ~ George Eliot