Query to select all data regarding a clicked field

[CJ_London]

well, we are making progress

Some things to check

1. Have you removed the WHERE clause from your report recordsource
2. check your report filter property that there isn't something there that shouldn't be - it should be blank
3. check the report properties that allow filters is set to yes

I presume if you open the report directly you do get all records per the recordsource and that there are records for that particular user and ordertype

try putting [E 037]. in front of both parts of the openreport filter string

"[E 037].[Usuario]='" & Me.[Usuario] & "' AND [E 037].[Clase de Orden]='" & Me.OC01 & "'"

Also, given the issues we've had with names, can you post the sql to the recordsource of your main form - I'm assuming it is a crosstab query at the moment

 

[melody.anne]

well, we are making progress

Some things to check

1. Have you removed the WHERE clause from your report recordsource
2. check your report filter property that there isn't something there that shouldn't be - it should be blank
3. check the report properties that allow filters is set to yes

I presume if you open the report directly you do get all records per the recordsource and that there are records for that particular user and ordertype

try putting [E 037]. in front of both parts of the openreport filter string

"[E 037].[Usuario]='" & Me.[Usuario] & "' AND [E 037].[Clase de Orden]='" & Me.OC01 & "'"

Also, given the issues we've had with names, can you post the sql to the recordsource of your main form - I'm assuming it is a crosstab query at the moment

1. yes, I removed it.
2. there is nothing in the filter property.
3. and yes, allow filters is set to yes.

Yes, if I open the report directly I get all the records there are, not just a particular user or order type.

I tried adding the [E 037], no errors, but still blank.

This is the main table SQL:

TRANSFORM Count([E 037].Porción) AS CountOfPorción
SELECT [Listado de Empleados].Supervisor, [E 037].[Alias de TP], [E 037].Usuario, Teléfonos.Telefono, Count([E 037].Porción) AS [Total Of Porción]
FROM Teléfonos INNER JOIN ([Listado de Empleados] INNER JOIN [E 037] ON [Listado de Empleados].[Codigo de Usuario] = [E 037].Usuario) ON Teléfonos.Alias = [E 037].[Alias de TP]
GROUP BY [Listado de Empleados].Supervisor, [E 037].[Alias de TP], [E 037].Usuario, Teléfonos.Telefono
ORDER BY [Listado de Empleados].Supervisor
PIVOT [E 037].[Clase de Orden];

 

[CJ_London]

I'm not seeing what the problem is but a couple of things to try,

1. Modify this part of your main report query by adding the 'in' bit

PIVOT [E 037].[Clase de Orden] IN ("OC01","OC02","OC03");

This dictates the columns required - in earlier versions of Access it was a requirement when used in forms and reports, perhaps not now.

2. At the top of the vba module it says Option Compare Database

just below it put Option Explicit

This will ensure all variables are declared

Now compile the code (click on Debug and choose the compile option at the top). If you have any typos with variable names or other code not working, this should show it up. So fix all errors (if any) and try again.

After that I think the only option is for you to upload a copy of the db with the two reports and the three tables with ideally just some dummy data to illustrate the problem.

 

[melody.anne]

I'm not seeing what the problem is but a couple of things to try,

1. Modify this part of your main report query by adding the 'in' bit

PIVOT [E 037].[Clase de Orden] IN ("OC01","OC02","OC03");

This dictates the columns required - in earlier versions of Access it was a requirement when used in forms and reports, perhaps not now.

2. At the top of the vba module it says Option Compare Database

just below it put Option Explicit

This will ensure all variables are declared

Now compile the code (click on Debug and choose the compile option at the top). If you have any typos with variable names or other code not working, this should show it up. So fix all errors (if any) and try again.

After that I think the only option is for you to upload a copy of the db with the two reports and the three tables with ideally just some dummy data to illustrate the problem.

The first option didn't work. I did that and when I went to open the report I'm trying to get these details on, it just prompted me to enter parameters.

Tried the second option, only found one error and that was with a "viewForm" I put in the Close button. I feel stuck :|

For some reason I have failed to mention that I'm on Access 2010

This is definitely a problem with the second criteria. I just tried to do the same for the total of the row, filtering by "Username" & "Supervisor" and it came out blank.

 

[CJ_London]

For some reason I have failed to mention that I'm on Access 2010

That's OK, so am I

I did that and when I went to open the report I'm trying to get these details on, it just prompted me to enter parameters

It shouldn't have done - there are no parameters unless you have a typo

One other thought - are you using multi value fields in your tables? that will certainly cause an issue

Any chance you can zip a cutdown copy of the db, I'm out of ideas

 

[melody.anne]

One other thought - are you using multi value fields in your tables? that will certainly cause an issue

...I probably am. I'll have to install Access at home so I can send you a zip. The forums won't let me upload or post links since I'm fairly new here.

I don't know how much of an option it is for me to change the multi value field thing, since the source is an Excel sheet that changes all the time; and the point of my program is to not have to be copying and pasting all this data to a template of sorts. Maybe there is something that can be done.

 

[CJ_London]

if you are linking to excel files, then you won't have multivalue fields, they are unique to Access and a pain in the a***

 

[melody.anne]

if you are linking to excel files, then you won't have multivalue fields, they are unique to Access and a pain in the a***

Here's the zip:
https://drive.google.com/a/upr.edu/file/d/0B_e8buRlLVTmQVhrVWRxQi1VTk0/view?usp=sharing_eid

remove parenthesis, obviously.

This is a very watered down version of my data; usually it has 5 main tables (like E 037). The only "working" order type is "LECT".
The filtering code is done on "Resumen Carga Aguadilla" report, on the "LECT" fields and on the "SUM" fields of LECT.
"rptDetails" report is where the details should be viewed.
"Count Orden Aguadilla" is the query that gives me the info for "Resumen Carga Aguadilla"
and "qry Details Aguadilla" is the query that should display the details on "rptDetails"

One last thing, you will have to make the connections from the excel files, since it does not work when transferred over.

Thank you for your help.

 

[CJ_London]

I've downloaded OK, will take a look later, I'm just about to go out

 

[melody.anne]

I've downloaded OK, will take a look later, I'm just about to go out

Alright, thanks.

 

[CJ_London]

Hi Melody,

I've tidied up the code so you don't have to repeat it so much and there is more that could be done using modules but keep for another time!. I've also simplified the names for your totals controls, again to simplify the code

I'm not sure why you had the problem, there was a filter which I removed, it may have been that.

Anyway, please see attached, it is all working as far as I can see - you'll need to relink to your spreadsheets

 

[melody.anne]

Hi Melody,

I've tidied up the code so you don't have to repeat it so much and there is more that could be done using modules but keep for another time!. I've also simplified the names for your totals controls, again to simplify the code

I'm not sure why you had the problem, there was a filter which I removed, it may have been that.

Anyway, please see attached, it is all working as far as I can see - you'll need to relink to your spreadsheets

I have checked it out, and decided to make this file my main project, since I can't be bothered to find that filter you mentioned.

It is working wonderfully. One question though. If I were to copy the main report, paste it and add a different crosstab query as its Record Source (same exact query, different main table), shouldn't it just work? For some reason when I do that and I click on the details, it gives me blank reports. I looked through the code and I didn't see anything that would make it only work with the one crosstab query, which is what confuses me.

Edit: I think I have found my mistake, I'm gonna have to create several "details" reports, since it feeds off different queries.