424,294 Members | 1,898 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,294 IT Pros & Developers. It's quick & easy.

stLinkCriteria

P: 7
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Open_Report_Click()
  2. On Error GoTo Err_Open_Report_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "RAKC1_CPR_REPORT_BENEFICIARY"
  7.     DoCmd.OpenReport stDocName, acViewReport
  8.  
  9.     stLinkCriteria = "Me![beneficiary]=" & [beneficiary]
  10.  
  11.  
  12. Exit_Open_Report_Click:
  13.     Exit Sub
  14.  
  15. Err_Open_Report_Click:
  16.     MsgBox Err.description
  17.     Resume Exit_Open_Report_Click
  18.  
  19. End Sub
1 Week Ago #1
Share this Question
Share on Google+
4 Replies


Seth Schrock
Expert 2.5K+
P: 2,920
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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport ReportName:=stDocName, View:=acViewReport, WhereCondition:=stLinkCriteria
Give that a try and let us know.
1 Week Ago #2

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Open_Report_Click()
  2.  
  3.     Dim stDocName As String
  4.     Dim stLinkCriteria As String
  5.  
  6.     stDocName = "RAKC1_CPR_REPORT_BENEFICIARY"
  7.  
  8.     stLinkCriteria = "[beneficiary]=" & "'" & Me![beneficiary] & "'"
  9.  
  10.     DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
  11.  
  12. End Sub
  13.  
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Open_Report_Click()
  2.  
  3.     Dim stDocName As String
  4.     Dim stLinkCriteria As String
  5.  
  6.     stDocName = "RAKC1_CPR_REPORT_BENEFICIARY"
  7.     stLinkCriteria = "[beneficiary]=" & "'" & Me![beneficiary] & "'"
  8.     stLinkCriteria = "[project]=" & "'" & Me![project] & "'"
  9.  
  10.     DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
  11.  
  12. End Sub
  13.  
I know that I am missing a stupid elementary mistake but maybe I could get it faster with your help. Thanks.
1 Week Ago #3

Seth Schrock
Expert 2.5K+
P: 2,920
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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. stLinkCriteria = "[beneficiary]=" & "'" & Me![beneficiary] & _
  2.                  "' 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.
1 Week Ago #4

P: 7
Works! Thanks a million Seth!
1 Week Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.