Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 28th, 2003, 10:22 AM
Registered User
Join Date: Aug 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Active list box and button error messages - Help!

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
        cmdNext.Enabled = False
        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.

Old August 28th, 2003, 02:38 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Hi Brian,

1. The following should fix your previous button issue. Call a function that returns a boolean value to let you know if you're at the first record or not:

Private Sub cmdPrevious_Click()

    If AtFirstRecord Then
        cmdPrevious.Enabled = False
        DoCmd.GoToRecord , , acPrevious
    End If

End Sub

Private Function AtFirstRecord() As Boolean

    ' Return True if at first row, False otherwise.
    Dim rst As DAO.Recordset

    On Error Resume Next

    Set rst = Me.RecordsetClone
    rst.Bookmark = Me.Bookmark
    AtFirstRecord = rst.BOF

    Set rst = Nothing

End Function

2. Here's a thought about your report issue. Since AllReports is a collection, objects added to it are assigned an index value as they are added to the collection. Your For Next block is then accessing the objects by their index value, which can change as ojects are added to and deleted from the collection. If you want to control the sort order of the report names in the list box, why not pull them from the Jet MSysObjects table using SQL. Just place the following in your list boxes row source property:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Type)=-32764) AND ((Left([Name],1))<>"~"))
ORDER BY MsysObjects.Name;

Type the SQL statement in the property field as one long string. Place you cursor in the property field and press F2 to get the Zoom Box (it'll make it easier to write the SQL).

This will list all of your reports.

Finally, if certain reports recieve their parameters from a loaded form, just load the form in the reports Open event. Then check to see if the form is loaded, and if not cancel the report. Paste the following IsLoaded function in a standard module so all your reports can see it:

Function IsLoaded(ByVal strFormName As String) As Boolean
  ' Returns True if the specified form is open in Form view or
    Datasheet view.

    Dim oAccessObject As AccessObject

    Set oAccessObject = CurrentProject.AllForms(strFormName)
    If oAccessObject.IsLoaded Then
        If oAccessObject.CurrentView <> acCurViewDesign Then
            IsLoaded = True
        End If
    End If

End Function

Then paste the following in each reports Open event:

Private Sub Report_Open(Cancel As Integer)
' IsLoaded function (defined in a standard module) determines
' if a specified a form is open.

    ' Open form.
    DoCmd.OpenForm "MyForm", , , , , acDialog

    ' If MyForm isn't loaded, don't preview or print report.
    ' (For example, user may have clicked a Cancel button on form.)
    If IsLoaded("MyForm") = False Then Cancel = True

End Sub

This way, all your reports can be displayed in your list box and open their required parameter dialogs in their Open event as needed.

Hope that gives you some ideas.



Old August 28th, 2003, 02:55 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Forgot to mention (in case you didn't catch it) that you'll need to manually set a reference to the DAO 3.6 library for the AtFirstRecord function to work. The default MDAC library for A2K is ADO.


Similar Threads
Thread Thread Starter Forum Replies Last Post
multi-column list box values moved to 2nd list box sbmvr Access VBA 1 May 14th, 2007 01:58 PM
List Box Error zoltac007 Visual Basic 2005 Basics 0 June 5th, 2006 12:56 PM
select box/List box alphabetic sort sasidhar79 Javascript How-To 3 November 10th, 2004 03:04 AM
Populate List Box by Combo Box Selection mmcdonal Access 2 June 15th, 2004 12:08 PM
Search using drop down list box and a text box tcasp Classic ASP Basics 1 July 31st, 2003 02:58 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.