Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Testing BOF and EOF (Access 2000)

    Hello Access Gurus,

    I am currently trying to test the BOF and EOF before I display a report so that it won't load if there is no data. I am using the following code which worked fine in access 97 and won't work in Access 2000 (typical!). The query for the report has parameters that are passed from the dialog box that runs the report. I am getting a Type Mismatch message and have no idea where the problem is coming from. It falls over on the line "For Each prm In qdf.Parameters" and goes to the error msg. Here is the code:

    Dim db As Database
    Dim rst As Recordset
    Dim qdf As QueryDef
    Dim prm As Parameter

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryArchiveActionsTest")
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm
    Set rst = qdf.OpenRecordset(dbOpenDynaset)

    'Test that records are found before opening the report"

    If Not rst.BOF And Not rst.EOF Then
    DoCmd.OpenReport "rptArchiveActionTest", acPreview, "", ""

    'set focus back to dialogue box to close it
    Forms!fdlgArchiveAction.SetFocus
    DoCmd.Close
    Else
    MsgBox ("No records found for this report. Please try again")

    End If

    mcrReports_PrintPreview_Exit:
    Exit Function

    mcrReports_PrintPreview_Err:
    MsgBox Error$
    Resume mcrReports_PrintPreview_Exit

    End Function

    I look forward to some help here.

    Thanks,

    Jocelyn

  2. #2
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing BOF and EOF (Access 2000)

    Hi

    I'm sure I will be corrected by those in the know if I get this wrong. I understand in Access 2000 and above DAO is not the preferred option (??), and has to be explicitly stated eg DAO.Database in Dim statements. Also, you may need to change prm.Value = Eval(prm.Name) to prm!Value = Eval(prm!Name). These may be the cause of your problem, then again they may not.

    I defer to Hans, smelly socks and all!

    Regards
    WTH

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing BOF and EOF (Access 2000)

    Thanks WTH,

    I shall give this a try.

    By the way, what is with the smelly socks and all statement?

    Cheers,

    Jocelyn

  4. #4
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing BOF and EOF (Access 2000)

    Hi WTH,

    I tried your solution but alas it still does not like the statement "For Each prm In qdf.Parameters" and falls over here. The same message of "Type Mismatch" is displayed.

    Regards,

    Jocelyn

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Testing BOF and EOF (Access 2000)

    Did you set a reference to DAO library and explicitly declare recordset variable as DAO recordset? ADO is default in A2K or later, and also has a Recordset object. Ex:

    Dim rst As DAO.Recordset

    I never used Eval with parameters in this context so don't know if that's a problem. I usually set parameter values directly:

    Set qrydef = db.QueryDefs("Export_qry")
    With qrydef
    .Parameters("<!t>[Forms]<!/t>!<!t>[EXPORT_frm]<!/t>!<!t>[Combo1]<!/t>") = Me.Combo1
    ' etc

    Note that ADO also has a Parameter object and Parameters collection so I would also explicitly declare prm variable as DAO:

    Dim prm As DAO.Parameter

    Wouldn't it be simpler just to use Report NoData event for this? Example:

    Private Sub Report_NoData(Cancel As Integer)
    Beep
    MsgBox "No records exist for the options you selected.", _
    vbCritical, "NEGATIVE REPORT"
    Cancel = True

    If using NoData event, the sub that opens report should have error handling in event of report being cancelled, as shown in this generic example:

    Private Sub PrintReport(ByVal intView As AcView)
    On Error GoTo Err_Handler

    Dim strRpt As String
    Dim strMsg As String

    strRpt = Me.Listbox1.Value

    DoCmd.OpenReport strRpt, intView

    If intView = acViewPreview Then
    ' Hide if popup:
    Me.Visible = False
    ' Set Zoom to 100%:
    DoCmd.SelectObject acReport, strRpt
    DoCmd.Maximize
    DoCmd.RunCommand acCmdZoom100
    End If

    Exit_Sub:
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 2501 'Action Cancelled (ie, No Data for report)
    Resume Exit_Sub
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "OPEN REPORT FUNCTION ERROR"
    End Select
    Resume Exit_Sub

    End Sub

    If report has no data, then user will just see the msgbox stating no records available. You can modify as necessary.

    HTH

  6. #6
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing BOF and EOF (Access 2000)

    Thanks MarkD,

    I have since set a reference to the DAO library and also included DAO before the dim statements for Database, Recordset, Parameter, and QueryDef objects and it all works again.
    Thanks for your coding example, it will also come in handy.

    Thanks for your assistance.
    Regards,

    Jocelyn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •