CODEWITHSUNDEEP
ms-accessvbapositionrefreshvisible
missscripty
missscripty

Reputation: 537

Access Form Bring Text Box in Front of Listbox?

This application is Access 2010, with a SQL backend.

I have a form, which has a tab control, with 2 pages. 1 page has 1 listbox and the other has 2 listboxes. I use some code, on change of the tab control, to determine the active page, and set the listbox(es) row source(s) for the one(s) on the active page, and clear it out on the inactive page. This takes a sec and the listboxes look yucky while this is happening.

To prevent the user from seeing this, I made a text box, with a colored background and the control source ="Please Wait One Moment..." with like 48 font. On load of the form, this textbox is visible = false. On click of the tab control, the text box is to be made visible, and it is big enough to cover then entire tab control. It does cover the tab control, but I can still see the listboxes through the text box. The text box has a back color and is not transparent. I tried right-clicking the listboxes in design view and choosing Position - Send to Back, and right-clicking the textbox and choosing Position - Send to Front.

This doesn't seem to work. Is anyone familiar with this issue? Figuring someone has tried a trick like this.

The meat of my code is all functioning properly, for this on change of the tab control. I have this code at the beginning:

DoCmd.Hourglass True
Me.txtPleaseWait.Visible = True
Me.Repaint
Application.Echo False
Debug.Print Me.txtPleaseWait.Visible

and this at the end:

Application.Echo True               
Me.txtPleaseWait.Visible = False    
Me.Repaint                          
Debug.Print Me.txtPleaseWait.Visible
DoCmd.Hourglass False               
Debug.Print "got to end of resting form state"

Does that make sense?

Should I handle this differently?

Thank you.

Edit

I found that listboxes have a higher zorder than textboxes, so I changed my textbox to a listbox. I still see the listboxes from the tab control, through the listbox I'm using to cover them up.

Is there a control that would better cover these and could have this "intermission" type message show, while the listboxes are being set and unset?

Upvotes: 2

Views: 2614

Answers (1)

missscripty
missscripty

Reputation: 537

I researched different aspects of this, and found the listbox has a higher zorder than text boxes, which is position front or back relative to other objects. I tried just doing this onload of the form, and there wasn't time to hide anything. It just waited to load the form, until the rowsources were set.

Since the find tab is the default one, and quicker to load, I just load that on load. Then, if someone clicks on the other tab, I will load the add listboxes. I also set an integer variable to 0 on load of the form. When I click on the tab to go to the add page (add page is active), I check that variable, and if it is 0, I set the row source, and then +1 to the variable. Next time I click on it, I don't re-order it.

This way, I'm not using resources to load and unload the listboxes, and I only load 2 of them, if the user even goes to that tab. Many times, they might just be looking for one in the system, and going to view it.

Here is the full code, in case this method helps someone else. You could also make the variable boolean, and just set it to true, and then false.

Option Compare Database
Option Explicit
Dim AddLoaded As Integer

Property Get ActivePage() As Access.Page
    'PROPERTY TO IDENTIFY WHICH TAB WE ARE ON, FOR FILTERING AND IDENTIFYING WHICH ACTIVE LISTBOX TO LOOK AT, FOR VALUES AND ACTIONS
    With Me.tbAddFind
        Set ActivePage = .Pages(.Value)
    End With
End Property

Private Sub Form_Load()
    Dim Listctrl As Control
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Dim cSQL As String

    AddLoaded = 0

    For Each Listctrl In Form.Controls
        If (Listctrl.ControlType = acListBox) Then
            With Listctrl
                .RowSource = ""
            End With
        End If
    Next Listctrl

    Me.tbAddFind.Value = 0

    cSQL = "SELECT vw_CMP_Projects.CM_CID, [vw_CMP_Projects]![ProjectName] &" & Chr$(34) & " (" & Chr$(34) & "& [vw_CMP_Projects]![ProjectNo] &" & Chr$(34) & ") " & Chr$(34) & " AS Projects FROM vw_CMP_Projects ORDER BY [vw_CMP_Projects]![ProjectName] &" & Chr$(34) & " (" & Chr$(34) & "& [vw_CMP_Projects]![ProjectNo] &" & Chr$(34) & ")" & Chr$(34)
    Me.lstProjects.RowSource = cSQL
    Me.lstProjects.Requery

End Sub

Private Sub tbAddFind_Change()
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Select Case Me.ActivePage.Name
        Case "pgAddProjects"
            If AddLoaded = 0 Then
                cmd.ActiveConnection = GetCatalog()
                cmd.CommandType = adCmdStoredProc
                cmd.CommandText = "sp_RefreshProjectsAdd"
                cmd.Execute

                Me.lstAllProjects.RowSource = "Select * From qryAddProjectsYes"
                Me.lstAllProjects.Requery
                Me.lstAddProjects.RowSource = "Select * From qryAddProjectsNo"
                Me.lstAddProjects.Requery

                AddLoaded = AddLoaded + 1
            End If
    End Select
End Sub

Thanks for the discussion. It was helpful, as it gave me things to research.

Upvotes: 1

Related Questions