How to write a "where" condition for a report?

[sueb]

I have a report that I want to preview from a button on a form. This report should execute only for the record currently displayed in the form. Here's the button's code:

Private Sub Preview_This_18_1_Click()
On Error GoTo Err_Preview_This_18_1_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stLinkCriteria = "[18-1.Index]=" & Me![Index]
 
    stDocName = "18-1 State Form"
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
    stDocName = "18-1 Continuation"
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
 
Exit_Preview_This_18_1_Click:
    Exit Sub
 
Err_Preview_This_18_1_Click:
    MsgBox Err.Description
    Resume Exit_Preview_This_18_1_Click
 
End Sub
 

I am failing to set the link criteria string correctly (it complains about how I'm bracketing it, regardless of what I try), but I think also that's not what a report calls for (as opposed to a form).

[TheSmileyCoder]

Assuming 18-1 is your table, have you tried:
stLinkCriteria = "[18-1].[Index]=" & Me![Index]

You may be having trouble because I believe that Index is a reserved word. I do not really know what trouble can come from using reserved words as field names, since I simply do not use them.

The stLinkCriteria is simply a WHERE clause with the WHERE keyword. It works the same for reports as it does for forms, in limiting the underlying recordset to those fulfilling the where clause.

[sueb]

On your advice, SmileyCoder, I went through the entire database and changed all instances of the name "Index" to "[modulename] Index", so no more complaints about that--thanks! (I didn't realize "Index" was reserved.)

However, my report is still not grabbing the correct record. I have a form that looks just like the report (it displays the data with a background picture that looks the State form they are used to, so they can edit it in a manner similar to how they've always used pen-and-paper), and both that form and the report are called from separate buttons on the same overall form.

The form does display the correct record, but the report does not. The report seems to be simply displaying the first record in the table.

Here's the code for the form that looks like the report:

Private Sub Edit_18_1_Click()
On Error GoTo Err_Edit_18_1_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
    stDocName = "18-1"
 
    stLinkCriteria = "[18-1 Index]=" & Me![18-1 Index]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Edit_18_1_Click:
    Exit Sub
 
Err_Edit_18_1_Click:
    MsgBox Err.Description
    Resume Exit_Edit_18_1_Click
 
End Sub
 

and here's the code for the report:

Private Sub Preview_This_18_1_Click()
On Error GoTo Err_Preview_This_18_1_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stLinkCriteria = "[18-1 Index]=" & Me![18-1 Index]
 
    stDocName = "18-1 State Form"
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
    stDocName = "18-1 Continuation"
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
 
Exit_Preview_This_18_1_Click:
    Exit Sub
 
Err_Preview_This_18_1_Click:
    MsgBox Err.Description
    Resume Exit_Preview_This_18_1_Click
 
End Sub
 

It looks to me like this should be working, but it's not.

[TheSmileyCoder]

That all looks correct to me.

My first suggestion as I have noticed your quite active and engaged in Access, is to read NeoPa's debugging articles in the Bytes Insights section. Good debugging skills are essential to any serious programmer.

Next suggestion would be to insert BEFORE line 10 and line 12 the line:

Msgbox "About to open report:" & stDocName & " with filter:" & stLinkCriteria

If that is still not working, check that the field [18-1 index] is part of your reports recordsource.

[sueb]

Thanks, SmileyCoder, I will certainly do both those things! I've always loved debugging; I just have no foundation in it at all in Access.

[NeoPa]

There are a few possibilities I suppose Sue. We don't know about what RecordSources are used for the form and the report. Are they identical? Like Smiley, the code looks good to me, but neither of us can see what things are actually called in your database, so whereas the form may be bound to a RecordSource that includes a field called [18-1 Index], the report may not. It's mostly about having the relevant information available. This can be difficult to provide sometimes of course, as knowing what is relevant is not always easy unless you have the experience which would often mean there were no need to ask the question anyway.

Sometimes it can help to run the RecordSource query as a standalone query to see more clearly what is going on.

Let us know if you manage to sort this, or what else you need if not.

PS. The following indented comments may be of assistance :

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[sueb]

Well, it's fixed, and the problem lay in an entirely different direction than how I presented it!

Because I thought the problem was with how I was calling the report, I told one of my users that I would print each page for her; I intended just to modify the query/VBA to directly point to the records that she wanted.

However, in trying to do this manually, I discovered that the links among the 4 tables were... well, just not correct. I guess the relationships were a little complicated, and I hadn't correctly defined the links and their properties.

Once I cleaned that up, it all worked great! Thanks for the thought you put into this. I'm still reading NeoPa's Debugging writeups (along with some others that I found scrolling through the Insights section--great stuff in there), so this has been, for me, an enormously useful exercise.

[TheSmileyCoder]

Thats good to know. While it can be frustrating to spend hours on end getting nowhere, I try to remind myself that I did learn something, even if it came at the expense of some hair being pulled out at the roots.

[NeoPa]

Very gratifying to hear Sue. You have a great attitude and it makes working with you so much easier :-)