Hidden items in list boxes

dbkester · August 28th, 2003, 10:40 AM

I am new to VB and am using the Access 2002 VBA book (Beginning). There are several things that I have found very useful, but they do not go as far as I'd like. As a newbie, I am not sure if the following can be accomplished, but would love some suggestions. My questions are probably very basic, but I'll get better.

------
1) Chapter 2 - Pg. 85 contains the code to disable the previous button when the new record button is selected.

Private Sub Form_Current()
    'Disables next button when form is on new Record
    If Me.NewRecord = True Then
        CmdPrevious.SetFocus
        cmdNext.Enabled = False
    Else
        cmdNext.Enabled = True
    End If
End Sub

The buttons on the buttons on the form include cmdNew, cmdFirst, cmdLast, cmdPrevious, cmdNext.

How can I disable the cmdPrevious button when the first record is selected, to avoid the error message if a user hits the previous button.

------
2) Chapter 5 Pgs. 189-192) uses a real nice routine that automatically adds any report written to the list box, so that you don't need to manually update a switchboard or add form buttons.

Private Sub Form_Load()

    Dim objao As AccessObject
    Dim objcp As Object
    Dim strValues As String

    Set objcp = Application.CurrentProject
    ListReports.RowSourceType = "Value List"

    For Each objao In objcp.AllReports
      strValues = strValues & objao.Name & ";"
      Me.ListReports.AddItem (objao.Name)
    Next objao

    ListReports.RowSource = strValues

End Sub

My questions are:

1) Some of the reports need to be run from specific forms/screens and generate an error message if they are run from this list.

Is there any way to hide or exclude specific reports form showing in the list box, while letting others be displayed?

2) The reports seems to change order/position between sessions.

Is there anyway to have them always appear in the list box in alphabetical order?

--------------------

Thanks in advance for any suggestions.

Brian

Braxis · August 28th, 2003, 11:32 AM

Hi Brian

With reference to the listbox questions:

One way to display only some of the reports in the list box would be to use different prefixes for the report names - i.e. prefix the ones you wish to show with 'vis'. The code would need to be altered to look like this:

Code:

    For Each objao In objcp.AllReports
      If left(objao.Name,3)="vis" Then
        strValues = strValues & objao.Name & ";"
        Me.ListReports.AddItem (objao.Name)
      End if
    Next objao

And here's a function for alphabetically sorting a value list rowstring:

Code:

Public Function SortValueList(intOrder As Integer, intSortCol As Integer, intColCount As Integer, ByVal strValueList As String, strDelimiter As String) As String
' Comments  : Sort the supplied delimited list - Does a text (not numerical) sort
' Parameters: intOrder - 0 for ascending and 1 for descending
'             intSortCol - The column to sort on
'             intColCount - the number of columns
'             strValueList - The list to sort
'             strDelimiter - The delimiter used to seperate values
' Returns   : String - The sorted value list
' Modified  :
'
' --------------------------------------------------
Dim intRowCount As Integer
Dim intStrPos As Integer
Dim strElement As String
Dim strSwap As String
Dim astrRow() As String
Dim astrSort() As String
Dim intCount1 As Integer
Dim intCount2 As Integer
Dim bolSorted As Boolean
On Error GoTo errSortValueList

    'Calcualte the number of rows in the list
    intRowCount = (CountInStr(strValueList, strDelimiter) + 1) / intColCount

    ReDim astrRow(intRowCount)
    ReDim astrSort(intRowCount)
    'Cycle through each row
    For intCount1 = 1 To intRowCount
        'Cycle through each column
        For intCount2 = 1 To intColCount
            'Find the next delimiter
            intStrPos = InStr(1, strValueList, strDelimiter)
            'If found
            If intStrPos <> 0 Then
                'Get the next element
                strElement = Left$(strValueList, intStrPos)
                'Remove this element from the value list
                strValueList = Mid$(strValueList, intStrPos + 1)
            'Else we're at the last element of the list
            Else
                strElement = strValueList
                strValueList = ""
            End If
            'Add the new element to the appropriate row array element
            astrRow(intCount1) = astrRow(intCount1) + strElement
            'If this is the column to sort on, capture this element
            If intCount2 = intSortCol Then
                astrSort(intCount1) = Replace(strElement, strDelimiter, "")
            End If
        Next
        'Remove the final delimiter
        If Right$(astrRow(intCount1), 1) = strDelimiter Then
            astrRow(intCount1) = Left$(astrRow(intCount1), Len(astrRow(intCount1)) - 1)
        End If
    Next

    'Now sort the array - use a classic bubble sort
    If intOrder = 0 Then
        'Keep on sorting until until we've made a complete pass through the list
        'without having to change anything
        Do While Not bolSorted
            bolSorted = True
            'Loop through the list
            For intCount1 = 1 To intRowCount - 1
                'Compare the current element and the next
                If StrComp(astrSort(intCount1), astrSort(intCount1 + 1), vbTextCompare) = 1 Then
                    'If they are out of order swap them round
                    strSwap = astrRow(intCount1)
                    astrRow(intCount1) = astrRow(intCount1 + 1)
                    astrRow(intCount1 + 1) = strSwap
                    strSwap = astrSort(intCount1)
                    astrSort(intCount1) = astrSort(intCount1 + 1)
                    astrSort(intCount1 + 1) = strSwap
                    'We've had to swap so we'll have to do another sweep
                    bolSorted = False
                End If
            Next
        Loop
    ElseIf intOrder = 1 Then
        'Keep on sorting until until we've made a complete pass through the list
        'without having to change anything
        Do While Not bolSorted
            bolSorted = True
            'Loop through the list
            For intCount1 = 1 To intRowCount - 1
                'Compare the current element and the next
                If StrComp(astrSort(intCount1), astrSort(intCount1 + 1), vbTextCompare) = -1 Then
                    strSwap = astrRow(intCount1)
                    astrRow(intCount1) = astrRow(intCount1 + 1)
                    astrRow(intCount1 + 1) = strSwap
                    strSwap = astrSort(intCount1)
                    astrSort(intCount1) = astrSort(intCount1 + 1)
                    astrSort(intCount1 + 1) = strSwap
                    'We've had to swap so we'll have to do another sweep
                    bolSorted = False
                End If
            Next
        Loop
    End If

    'Put the list back together
    For intCount1 = 1 To intRowCount
        SortValueList = SortValueList & astrRow(intCount1) & strDelimiter
    Next
    If Right$(SortValueList, 1) = strDelimiter Then
        SortValueList = Left$(SortValueList, Len(SortValueList) - 1)
    End If

exitSortValueList:
    Exit Function
errSortValueList:
    SortValueList = strValueList
    MsgBox Err.Description & " " & "mdlListBox" & ":" & "SortValueList"
    Resume exitSortValueList

End Function

Hope that helps!

Brian Skelton
Braxis Computer Services Ltd.

Bob Bedell · August 28th, 2003, 03:10 PM

Hi Brian,

I also posted a response to your questions on the Access forum. It's strongly recommended that we try and avoid cross-posting the same question of multiple forums for this reason. The conversation gets a bit scattered. :)

Regards,

Bob