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

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
        cmdNext.SetFocus
        cmdPrevious.Enabled = False
    Else
        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
    rst.MovePrevious
    AtFirstRecord = rst.BOF

    Set rst = Nothing
    Err.Clear

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.

Regards,

Bob

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.

:)