Report Filter not working

[PNGBill]

Hi Forum, 2000 converted to 2010

After installing 2010 when I click for a single record report to run All 7,000 odd records have a report produced.

Any Ideas what may be causing the "strWhere" to not restrict the records printed??

This code is the On Click event to print a Report

Private Sub CmdFrmSingleLoanStatement_Click()
On Error GoTo Err_CmdFrmSingleLoanStatement_Click
    
    Dim LoanID As Integer                           'Variable to hold Loan ID
        
    LoanID = Me.txtLDPK                             'put value from form control to variable
    LoanID = CStr(LoanID)                           'convert variable to string
 
    LoanStatementSingle (LoanID)                    'call function to Generate Statement
    
Exit_CmdFrmSingleLoanStatement_Click:
    Exit Sub
    
Err_CmdFrmSingleLoanStatement_Click:
    MsgBox Err.Description
    Resume Exit_CmdFrmSingleLoanStatement_Click
End Sub

And here is part of the Function the above code calls

Public Function LoanStatementSingle(LoanRef As String, Optional DoNotEmail As String)   'general function to print a single Loan Statement with Option to email or not
    
    Dim strWhere As String
    Dim FullName As String                          'Variable to hold Full Name
    Dim FirstName As String                         'Variable to hold first Name
    Dim strSQL As String                            'Variable to hold SQL Statement
    Dim Response As String                          'Variable to hold response to Message Box Questions
    Dim TeamMember As String                        'variable to hold Team Member Full Name
    Dim dbs As DAO.Database, rst As DAO.Recordset
    Set dbs = CurrentDb()
    
    TeamMember = TeamMemberName()                   'put result of function as Team member Full Name
    
        'SQL to Collect Club Member Full Name
    strSQL = "SELECT TBLLOAN.LDPK, [ADFirstname] & "" "" & [ADSurname] AS FullName " & _
        "FROM TBLACCDET INNER JOIN TBLLOAN ON TBLACCDET.ADPK = TBLLOAN.ADPK " & _
        "WHERE (((TBLLOAN.LDPK)=" & LoanRef & "));"
          'Open Recordset
    Set rst = dbs.OpenRecordset(strSQL)
    FullName = rst!FullName 'Put Result of sql as Variable FullName
    
    strWhere = "LDPK = " & LoanRef
    DoCmd.OpenReport "RptStatementNewStyle", acViewPreview, , strWhere
       
        ' Stephen Lebans Save to .PDF Code
    Dim blRet As Boolean
        ' Call our convert function
        ' Please note the last param signals whether to perform
        ' font embedding or not. I have turned font embedding ON for this example.
    blRet = ConvertReportToPDF("RptStatementNewStyle", vbNullString, _
    "W:\Attachments\" & FullName & " Loan " & LoanRef & " Statement " & TeamMember & ".pdf", False, False, 150, "", "", 0, 0, 0)
        ' To modify the above call to force the File Save Dialog to select the name and path
        ' for the saved PDF file simply change the ShowSaveFileDialog param to TRUE.

 

[JamesMcS]

Try sticking a msgbox in the code above the strwhere to see what's in the variable:

MsgBox LoanRef

Might point to the problem.

 

[JamesMcS]

strwhere might also have to be

strWhere = "[LDPK]='" & LoanRef & "'"

 

[vbaInet]

If it was a quotes problem it would have thrown an error.

Here's an amendment to some parts of your function:

    'SQL to Collect Club Member Full Name
    strSQL = "SELECT TBLLOAN.LDPK, [B][COLOR=Red][ADFirstname] [ADSurname][/COLOR][/B] AS FullName " & _
             "FROM TBLACCDET INNER JOIN TBLLOAN ON TBLACCDET.ADPK = TBLLOAN.ADPK " & _
             "WHERE TBLLOAN.LDPK = " & [B][COLOR=Red]Me.[/COLOR][/B]LoanRef & ";"

    Set rst = dbs.OpenRecordset(strSQL, [B][COLOR=Red]dbopensnapshot[/COLOR][/B])
    
    [B][COLOR=Red]if rst.recordcount > 0 then[/COLOR][/B]
        [B][COLOR=Red]rst.movefirst[/COLOR][/B]
        FullName = rst!FullName
    [COLOR=Red][B]end if[/B][/COLOR]
    
    strWhere = "LDPK = " & [B][COLOR=Red]Me.[/COLOR][/B]LoanRef

Just as a side note, you seem to be writing comments that are self explanatory, e.g. "Open recordset"

 

[JamesMcS]

Ah I see. I thought it was required when building a string for this sort of thing. Would

[ADFirstname] [ADSurname]

produce an error though? I'd have thought it should be

 [ADFirstname] & ' ' & [ADSurname]

And

"WHERE TBLLOAN.LDPK = " & Me.LoanRef & ";"

Should be

"WHERE TBLLOAN.LDPK = '" & Me.LoanRef & "';"

I thought, anyway....

 

[vbaInet]

Ah I see. I thought it was required when building a string for this sort of thing. Would

[ADFirstname] [ADSurname]

produce an error though? I'd have thought it should be

 [ADFirstname] & ' ' & [ADSurname]

That's quite right James. Well spotted.

And

"WHERE  TBLLOAN.LDPK = " & Me.LoanRef & ";"

Should be

"WHERE  TBLLOAN.LDPK = '" & Me.LoanRef & "';"

I thought, anyway....

If that was an issue it would throw a data mismatch error (or something along those lines). But PNG didn't mention there was an error so we can deduce that the data type is Number.

 

[JamesMcS]

Eeejit... I got confused with the cstr'ing of LoanID in the first module. Excellent deductional powers Sherlock

 

[vbaInet]

Eeejit... I got confused with the cstr'ing of LoanID in the first module. Excellent deductional powers Sherlock

He was explicitly casting to string which wasn't necessary, so another couple of brownie points for you. Maybe you're the real Sherlock

 

[JamesMcS]

Mmmm brownies

 

[PNGBill]

Try sticking a msgbox in the code above the strwhere to see what's in the variable:

MsgBox LoanRef

Might point to the problem.

Thanks James,

MsgBox returns the correct record number yet all records are printed

 

[PNGBill]

strwhere might also have to be

strWhere = "[LDPK]='" & LoanRef & "'"

I remarked the code changing LoanID to string so the first code now sends LoanID as Integer to the function.

Have changed the function to have loanRef As Integer

Still outputs all records.

 

[PNGBill]

If it was a quotes problem it would have thrown an error.

Here's an amendment to some parts of your function:

    'SQL to Collect Club Member Full Name
    strSQL = "SELECT TBLLOAN.LDPK, [B][COLOR=red][ADFirstname] [ADSurname][/COLOR][/B] AS FullName " & _
             "FROM TBLACCDET INNER JOIN TBLLOAN ON TBLACCDET.ADPK = TBLLOAN.ADPK " & _
             "WHERE TBLLOAN.LDPK = " & [B][COLOR=red]Me.[/COLOR][/B]LoanRef & ";"
 
    Set rst = dbs.OpenRecordset(strSQL, [B][COLOR=red]dbopensnapshot[/COLOR][/B])
 
    [B][COLOR=red]if rst.recordcount > 0 then[/COLOR][/B]
        [B][COLOR=red]rst.movefirst[/COLOR][/B]
        FullName = rst!FullName
    [COLOR=red][B]end if[/B][/COLOR]
 
    strWhere = "LDPK = " & [B][COLOR=red]Me.[/COLOR][/B]LoanRef

Just as a side note, you seem to be writing comments that are self explanatory, e.g. "Open recordset"

Thanks vbaInet, have made changes except Me.LoanRef as system won't allow.
LoanRef is a declared variable as it is in the function name. Why would it need Me. prefixed?

No errors when debugging.

 

[vbaInet]

I guessed it was a control name or field name

Remove the Me.s

 

[JamesMcS]

What happens if you replace strwhere in the openreport line with the string itself?

 

[PNGBill]

This issue has emerged again
Converted from 2000 to 2010 mdb and somehow the report filter no longer works.
I have added MsgBox's and they indicate all should be ok
Here is an example:
Function is

LoanStatementSingle(LoanRef As String, Optional DoNotEmail As String)

MsgBox LoanRef

returns 8057

strWhere = "[LDPK] = " & LoanRef

MsgBox strWhere

returns [LDPK]=8057

yet

DoCmd.OpenReport "RptStatementNewStyle", acViewPreview, , strWhere

returns 1,000's of reports instead of just the report for above two message box results.

I can not recall how this was resolved on the original post but believe it has something to do with the line of code above (strWhere =...) yet the 2nd MsgBox appears to return the correct result.

Does 2010 handle strWhere print command differently ??

Appreciate any advice, Thanks,
Bill

 

[JamesMcS]

What happens if you put the filter string in the form's properties in layout view?

 

[JamesMcS]

Gold star for randomest comment of the day, jarryslink

 

[PNGBill]

What happens if you put the filter string in the form's properties in layout view?

The Form displays the unique details with the relevant text box control for the Club Member.
The subform displays all the Loans with the relevant Loan Numbers.

The control on the subform appears to allow the function to see the correct data but it may be reading same as text rater then integer or vice versa.

The form control activates a functions which prints a report.

The report should only show one Loan but instead returns all loans for all Club Members (customers)

Do you mean the reports layout view ??

Thanks,
Bill

 

[JamesMcS]

Yeah sorry report layout was what I meant - just so you can see the report running, if you will, but still have the property sheet displayed. Type in the filter string into the relevant box in the property window and see if that works OK...