selecting report to open by Yes or No

[flect]

G'day Accessians

My Stocktake system has 2 different reports to chose from - one ordered by issue date, the other ordered by type of issue.

I've added a simple Yes/No column to my table to decide which report to open.

I've attempted to sus out the appropriate VBA but i just can't get it to work properly.

Private Sub openreport_click()
Dim reporttype As String
reporttype = DLookup("reportbytype", "tblagency", "agencyID = [forms]![frmnavigation]![masterfilter]")
    If reporttype = vbYes Then
        DoCmd.openreport "rptIssueStocktake", acViewNormal, "", "", acWindowNormal
    End If
    If reporttype = vbNo Then
        DoCmd.openreport "rptDatestocktake", acViewNormal, "", "", acWindowNormal
    End If
    
End Sub

I'm sure it's just a syntax error or something fundamental. I'm trying to wean myself off macros and queries and learn me some VBA

 

[georgedwilkinson]

What line is it failing on? What's the error?

What comes up in the immediate window when you add:

Debug.Print reporttype

after the DLookup?

It's interesting to note that both vbYes and vbNo are integer values but you're comparing them to a string (reporttype). That's not gonna work.

But you need to know what DLookup is returning (see debug line above).

 

[boblarson]

VbYes returns a 6 (which can still evaluate to True because any non-zero number will do so)

But, vbNo returns a 7 which will NOT evaluate to False.

So, if you have a reporttype is a boolean value then you can use:

    If reporttype = True Then
        DoCmd.openreport "rptIssueStocktake", acViewNormal, "", "", acWindowNormal
    Else
        DoCmd.openreport "rptDatestocktake", acViewNormal, "", "", acWindowNormal
    End If

But if it is Yes or No as text then

    If reporttype = "Yes" Then
        DoCmd.openreport "rptIssueStocktake", acViewNormal, "", "", acWindowNormal
    Else
        DoCmd.openreport "rptDatestocktake", acViewNormal, "", "", acWindowNormal
    End If

 

[doco]

VbYes returns a 6 (which can still evaluate to True because any non-zero number will do so)
But, vbNo returns a 7 which will NOT evaluate to False.

I have been bit by that dog at least twice. It is interesting MS Enum[eration] gave 6 & 7 instead of 0, -1 as one would expect. But then, that is the same old 'six and seven' with Microsoft ...

 

[flect]

To be honest - i'm pretty sure that Yes/No is one of the the simplest things i could of done.

I'm basically using one of two reports, some countries use Report A, some countries will use Report B, they'll only ever use one or the other, so i thought a yes/no would be appropriate way to discern between the two. I'm suspecting that the Yes/No could easily be defined by some other, more appropriate parameter.

 

[flect]

Update:
Problem solved

Debug.Print reporttype

returned the values of 0 or -1, so I just update my code to use the correct values

I was also having a problem where by the reports were going straight to the printer and not being viewed; once again it was just a syntax error.

Private Sub generate_Click()
Dim reporttype As integer
reporttype = DLookup("reportbytype", "tblagency", "agencyID = [forms]![frmnavigation]![masterfilter]")
 If reporttype = -1 Then
    DoCmd.openreport "Copy of HK Stocktake", acViewReport, , , acWindowNormal
    Else
    DoCmd.openreport "stocktake", acViewReport, , , acWindowNormal
    End If
End Sub

 

[georgedwilkinson]

Glad you got it fixed.