stLinkCriteria

[nsiotto]

I have a MS Access database made over ten years back and slowly improved through the year. Now I am attempting to improve also the reporting. For instance, I need to summarize the expenses paid to a specific beneficiary but can't make it work. With the coding below it's opening the document with the full data and not the selected one.

FYI I am opening the report from a form where I can select input a specific text and would like to show only data matching the same.

Any suggestion?

Private Sub Open_Report_Click()
On Error GoTo Err_Open_Report_Click
 
    Dim stDocName As String
 
    stDocName = "RAKC1_CPR_REPORT_BENEFICIARY"
    DoCmd.OpenReport stDocName, acViewReport
 
    stLinkCriteria = "Me![beneficiary]=" & [beneficiary]
 
 
Exit_Open_Report_Click:
    Exit Sub
 
Err_Open_Report_Click:
    MsgBox Err.description
    Resume Exit_Open_Report_Click
 
End Sub

[Seth Schrock]

There are a couple problems. First, your criteria isn't formatted correctly. Assuming that the Me!beneficiary control is bound to a field named beneficiary. If that is correct, then change stLinkCriteria to stLinkCriteria = "[beneficiary]=" & Me![beneficiary].

Second problem is that your criteria isn't ever applied. Move line 9 above 7 and then change 7 to

DoCmd.OpenReport ReportName:=stDocName, View:=acViewReport, WhereCondition:=stLinkCriteria

Give that a try and let us know.

[nsiotto]

Seth thanks for your input. While waiting the answer actually I managed to get it right.

Private Sub Open_Report_Click()
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "RAKC1_CPR_REPORT_BENEFICIARY"
 
    stLinkCriteria = "[beneficiary]=" & "'" & Me![beneficiary] & "'"
 
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
 
End Sub
 

It works fine now (guess there are various variable to obtain the same). However now I have another issue. I need to select two criteria and not just one.

I did the following but it takes only one:

Private Sub Open_Report_Click()
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "RAKC1_CPR_REPORT_BENEFICIARY"
    stLinkCriteria = "[beneficiary]=" & "'" & Me![beneficiary] & "'"
    stLinkCriteria = "[project]=" & "'" & Me![project] & "'"
 
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
 
End Sub
 

I know that I am missing a stupid elementary mistake but maybe I could get it faster with your help. Thanks.

[Seth Schrock]

Good catch on adding the quotes to the criteria string. I should have checked on that myself.

On adding a second criteria...
A variable only contains what it is assigned. It doesn't get added to. Seems simple, but look again at lines 7 and 8. On line 7, you assign a value to stLinkCriteria. On line 8, you assign another value to stLinkCriteria, wiping out the previous value from line 7. This can be done by concatenating the value of stLinkCriteria with another value and assigning that back to stLinkCriteria.

stLinkCriteria = stLinkCriteria & " And [project]=" & "'" & Me![project] & "'"

Notice the And I put before [project]. That is how you have multiple criteria in a SQL WHERE statement (which is what you are building minus the word WHERE).

However, in this case you can just do it all on one line.

stLinkCriteria = "[beneficiary]=" & "'" & Me![beneficiary] & "' And [project]=" & "'" & Me![project] & "'"

if you want the line shorter, then use a line continuation character and then make a new line.

stLinkCriteria = "[beneficiary]=" & "'" & Me![beneficiary] & _
                 "' And [project]=" & "'" & Me![project] & "'"

One quick note about posting questions, we only allow one question per thread. This one was kind of one the edge of being a new question, but about the same problem so I did answer it here. Just for future reference, try to keep each thread on one focus. New questions can be posted in a new thread. It makes it easier for answers to be found in a search.

[nsiotto]

Works! Thanks a million Seth!