Problem opening report to specific record

[Hank.School]

Hi guys,

I have a report based off of a query which pulls data from multiple tables. I also have a search form to locate a specific record. The search form works fine and on the form I have a button to open the report with:

DoCmd.OpenReport strReportname, acViewPreview, , strCriteria

No matter what I set the Criteria string to, the report always seems to open to the same record (first one in the table). It does display only one record but it is not the right record. I even tried setting it manually to a specific record to test it and it still opens to the first record.

The Criteria right now is set with:
strCriteria = "[Loc_Code] =" & [SearchResults].[Column](3)

Loc_Code is the name of the field from the query that I want to filter on (the text box on the report where that field lands is named Text5. I tried using that too).

SearchResults is the query for the search form and column 3 of a listbox holds the Loc_Code parameter. It displays properly in the search form so I know the reference to that value is correct.

When the report comes up, the fields are filled in correctly (though with the wrong record) including the Loc_Code field.

For some reason, the criteria is not filtering properly. When I look for examples on the web they all seem to follow this format. Does anyone know what I am doing wrong here? :banghead:

Thanks
Hank

 

[sneuberg]

If [Loc_Code] is a text field then you need single quotes like:

strCriteria = "[Loc_Code] ='" & [SearchResults].[Column](3) & "'"

but if that's not the problem then put in a

Debug.Print strCriteria after the assignment and see if what's in the Immediate window tells you anything.

Also make sure your column count is right in the combo box properties

 

[Hank.School]

Hi sneuberg,

Thanks for the reply.

The Loc_Code field is numeric. I added the debug statement and the string appears to be set correctly. Just to test, I temporarily set:

strCriteria = "[Loc_Code] = 111"

to see if it would open to record 111. It does not.

I tried without the brackets, still no good.

I am sure it is something with the formatting of the criteria but I don't know what it is. Should I be using a reference to 'Me.' or to [QueryName]![FieldName] or something?

No matter what I do I can't seem to get the Criteria string to have any effect at all.

Hank

 

[sneuberg]

If Loc_Code is in the table as Loc Code, no underscore then drop the underscore and try

strCriteria = "[Loc Code] =" & [SearchResults].[Column](3)

if that doesn't help please post the SQL of record source query of the report.

 

[Hank.School]

The field definitely has the underscore. Here is the SQL statement for the report query:

SELECT TBL_Sites.Loc_Code, TBL_Sites.[Site Name], TBL_Sites.Alt_Names, TBL_Sites.[Remedy Name], TBL_Sites.[Location Service], TBL_Boros.Borough, TBL_Lines.[Line Name], TBL_Site_Types.Site_Type, TBL_Sites.Notes, TBL_Sites.[Location Help], TBL_Rooms.[Room Code], TBL_Rooms.[Phone #], TBL_Rooms.[Level/Floor], TBL_Rooms.[End/Bound], TBL_Rooms.Key, TBL_Rooms.Notes, TBL_Rooms.[Location Help]
FROM TBL_Boros INNER JOIN (TBL_Site_Types INNER JOIN (TBL_Lines INNER JOIN (TBL_Sites INNER JOIN TBL_Rooms ON TBL_Sites.SITE_ID = TBL_Rooms.SITE_ID) ON TBL_Lines.Line_ID = TBL_Sites.Line) ON TBL_Site_Types.Type_ID = TBL_Sites.[Location Type]) ON TBL_Boros.ID = TBL_Sites.Boro;

The query is pretty straight forward... it just pulls data from five related tables to display on the report. Could it be that I have to specify which table Loc_Code is in? The only record that ever displays is the first record in the TBL_Rooms table, no matter what I set the criteria string to.

Thanks
Hank

 

[sneuberg]

Sorry but the SQL isn't telling me anything new. As longs as Loc_Code is in only one table you shouldn't have to qualify it with the table name.

I do find it odd that you are only seeing the first record. I'd expect it to be either all or nothing. What do you get when you remove strCriteria from the DoCmd.OpenReport. You should get all of the records the query produces.

I can't think of anything else to try. I think I need to see this. Could you upload the database?

 

[Hank.School]

Hi Steve,

Sorry, I didn't get to work on it today except to verify that all records are indeed displayed (not just one like I originally thought). Apparently I had to use the navigation buttons to get to the next page rather than just the scroll bars.

The database is pretty big, I would have to make a copy and strip it down to be able to post it but I appreciate that you would look at it for me. I think I am going to try with a new simpler form without a query this weekend to see if I can get it to work at all. If not, I will strip it down and post it Monday.

Thanks again for your help and have a great weekend.

 

[sneuberg]

I hope you don't find this question insulting but do you happen to have the assignment of strCriteria after the open like:

DoCmd.OpenReport strReportname, acViewPreview, , strCriteria
strCriteria = "[Loc_Code] =" & [SearchResults].[Column](3)

rather than as they should be like:

strCriteria = "[Loc_Code] =" & [SearchResults].[Column](3)
DoCmd.OpenReport strReportname, acViewPreview, , strCriteria

If strCriteria isn't set to anything when the report is open you will get all of the records.

 

[Hank.School]

LOL, not insulting at all. I am fairly new to access (well, at this level at least) so ANY question is legitimate.

I have the variable assignment before the DoCmd.OpenReport.

I am having suspicions that it might be related to my original search query mucking up the works. It is from a thread on this site by John Big Booty called 'Dynamically search multiple fields'. When it works, it works great. Unfortunately, it stops working often (when I first open Access) and I don't understand why. Rather than trying to troubleshoot two problems, I am going to simplify my search form and use a table as the source for the report rather than a query with multiple tables. At least from there I will be able to (hopefully!) get the Criteria to work for the DoCmd.OpenReport method. I will post my results after the weekend.

Thanks

 

[sneuberg]

That might be going in the wrong direction. You said

The Loc_Code field is numeric. I added the debug statement and the string appears to be set correctly. Just to test, I temporarily set:

strCriteria = "[Loc_Code] = 111"

If this

SELECT TBL_Sites.Loc_Code, TBL_Sites.[Site Name], TBL_Sites.Alt_Names, TBL_Sites.[Remedy Name], TBL_Sites.[Location Service], TBL_Boros.Borough, TBL_Lines.[Line Name], TBL_Site_Types.Site_Type, TBL_Sites.Notes, TBL_Sites.[Location Help], TBL_Rooms.[Room Code], TBL_Rooms.[Phone #], TBL_Rooms.[Level/Floor], TBL_Rooms.[End/Bound], TBL_Rooms.Key, TBL_Rooms.Notes, TBL_Rooms.[Location Help]
FROM TBL_Boros INNER JOIN (TBL_Site_Types INNER JOIN (TBL_Lines INNER JOIN (TBL_Sites INNER JOIN TBL_Rooms ON TBL_Sites.SITE_ID = TBL_Rooms.SITE_ID) ON TBL_Lines.Line_ID = TBL_Sites.Line) ON TBL_Site_Types.Type_ID = TBL_Sites.[Location Type]) ON TBL_Boros.ID = TBL_Sites.Boro;

it the record source of whatever is in strReportname and that record source has a Loc_Code equal to 111 the report should be displaying that record. I think you should track down why that's not working. I'd start by putting criteria directly in the open like:

DoCmd.OpenReport strReportname, acViewPreview, ,  "[Loc_Code] = 111"

and try to get zero records with something like:

DoCmd.OpenReport strReportname, acViewPreview, ,  "[Loc_Code] = 1234567890"

 

[Hank.School]

Good Morning,

I created a very simple table, form, and report to test the WhereCondition of the DoCmd. I still can't get it to work. No matter what I change it to, the report has all records on it. If you have an opportunity, please give it a look and maybe you can see what I am doing wrong. It is probably something simple that I am missing.

Table has five entries with an Autonumber field named ID. A command button on the form uses the DoCmd.OpenReport method and the report opens fine but it will not select a record based on the WhereCondition criteria.

Thanks
Hank

 

[sneuberg]

I changed the code to

Private Sub Command17_Click()
DoCmd.OpenReport "Report1", acViewPreview, , "[ID] =[COLOR="Red"] 5[/COLOR]"
End Sub

And I get

and I got the record with ID=2 before I changed it, i.e., this works on my system. I will send you a test application for you to try shortly.

 

[sneuberg]

Please tried the attached database. Just select a customer and click the Run Report button. If this doesn't result in a report with the selected customer then your Access application may be in need of repair or reinstallation.

 

[Hank.School]

Your file runs fine and produces the desired output. There is an extra small box under the single record but at least it seems to be working.

Now, here is where it gets crazy. I have tested my DB on my system at work and at home (two different Access installs) and it didn't work with either one. Now, after running your file, the test I just sent you is working as expected. Don't ask me what happened, I didn't change anything at all. All I did was load yours, confirm it worked, then loaded mine and it started working. You must have a golden Access touch

However, something is still wrong with my original DB. Using the exact same code as before, I still get all records. I am manually setting [Loc_Code] to a valid record and it still results in all records.

I added another test form and report to my original DB, this report is based on the table instead of a query (though I see yours has a query Data Source and it works fine). That form/report works fine, it goes right to the proper record and shows only one.

I changed the source on this test report to the same query I am using for my real application and it gives all records starting from the first (same behavior I started with) so it seems to be something with the query. I will change my main report to be data sourced from a table and see what happens.

Thanks for sticking with me!

Hank

 

[sneuberg]

so it seems to be something with the query.

I'd play with the query a while. Just open it in design view, put let's say 111 in the criteria for Loc_Code, and see what kind of result you get.

 

[Hank.School]

Playing with the query... it seems I can have two tables with a relationship between them but if I add a third table something seems to break. Still experimenting.

 

[Hank.School]

Ok, so it isn't the number of tables that breaks it... it seems if I add specific tables to the query then it breaks. I am trying to figure out why. If I do, I will let you know. Thanks again for all the help.

 

[sneuberg]

Do you mean by break that it shows all records rather than the specific record in the criteria?

Wish I could help more. I suspect this is a simple problem.

 

[Hank.School]

Actually, no. Now when it breaks it shows a blank form... no records at all. I can confirm that it is the mere act of adding a table to the query that does it. I have identified two tables so far that do it. I am looking at them to try to figure out why but so far they are normal tables.

 

[Hank.School]

Ok, I found the problem (at least partially). Not sure what I was doing wrong to have all records come up instead of the one specified record but I think compacting/repairing the DB fixed that.

So the problem with the report being displayed blank was that I used the default inner join. The tables that I was adding to 'break' it simply didn't have an entry for the one record I hard-wired into the code as a test. As soon as I changed it to an outer join everything worked fine!

I suspect that might be the same issue I'm having with John Big Booty's search code... not all records were being displayed when I would add tables to the query. I will check that later and hopefully it is the problem.

Anyway, thank you so much for the help... I really appreciate your working on this with me. The hardest part of fixing simple problems is finding out what it is... I still have a lot to learn!

Hank