Query to select all data regarding a clicked field

[melody.anne]

Is this even possible? Let me talk you through this scenario. I have three tables. TableA contains certain order information, such as the username of the employee, type of order, alias number, etc. TableB contains phone numbers, username of the employee, etc. TableC contains supervisor names and alias numbers.

I didn't make these tables but that's what I'm working with. I connected TableA to TableB with the username of the employee, and then TableA to TableC using the alias numbers.

I have a report that summarizes how many orders of each type there are. It shows the supervisor, employee username, alias numbers and then each type of order with the amount of orders of each type under the headers.

My boss wants to be able to click on the amount of that type of order and have it show him the details for those orders. This is where my problem is.

I was able to use an Inner Join type of query that allows me to filter the data by order type (however, I have to make a new query for each order type since I'm not too experienced on this). The problem is that I cannot find a way to have a query filter the data by order type AND employee username, so that it only displays the orders taken by a certain employee and it only contains one order type at a time, depending on which order type you clicked.

Again, is this even possible?

 

[CJ_London]

anything is possible, providing it is set up right. I'm unclear of your table structure. You say

My boss wants to be able to click on the amount of that type of order

but I cannot see and amount.

Suggest you post a screenprint of your relationships for the three tables, showing all fields plus a copy of the sql used in your report.

Also, please clarify you mean a report and not a form and which version of Access you are using

 

[melody.anne]

anything is possible, providing it is set up right. I'm unclear of your table structure. You saybut I cannot see and amount.

Suggest you post a screenprint of your relationships for the three tables, showing all fields plus a copy of the sql used in your report.

Also, please clarify you mean a report and not a form and which version of Access you are using

Hi, I cannot currently post a screenprint of that since it is the weekend and I don't have the files at hand. When I say amount I mean this, for example, we have these order types: OC01, OC02, OC03 (there are more, but keeping it simple). The amount I mean is, X employee created 25 OC01 orders, 10 OC02 orders and 13 OC03 orders.

Yes, it is a report. It shows as follows:
Supervisor | Username | Alias | OC01 | OC02 | OC03 | TOTAL
--Johnson- | ASTEVENS | ##### |- 25 -|- 10 -|- 13 -|- 48
------------------TOTAL------ |- 25 -|- 10 -|- 13 -|- 48
*The dashes are merely for spacing.

Assuming the list is longer, and there is more than one supervisor, more than one username, more than one alias, and all of these make their own OC01, OC02, and OC03 orders, I want to be able to click (or double click) on, say, the number 25 that is under "OC01" and have it show me all the information regarding that (order description, supervisor, employee, location, etc).

I'm able to pull all the data regarding "OC01" orders, but I'm unable to pull only the orders created by "ASTEVENS" and instead it shows all the "OC01" data from "ASTEVENS" and everyone else.

I'll be sure to post screenprints tomorrow if this isn't clear enough (which I'm sure it isn't)

 

[CJ_London]

OK, I think I get the idea

Assuming your controls have the same names as the headings And assuming you are opening another report (we'll call it rptDetail) and it uses the sql you refer to here

I'm able to pull all the data regarding "OC01" orders

then for example, in the OC01 control doubleclick event (you can use click if you want, but users tend to click on a field to select a column, but up to you) put the following code

 docmd.OpenReport "",,,"UserName='" & me.username & "' AND OrderType='" & me.OC01 & "'"

Note that you should modify your report to not filter by the ordertype since it is handled here and means you can use the same report for all ordertypes.

You need to repeat this code for each ordertype, modifying the ordertype filter for each one

You may need to play around with the other openreport parameters to ensure the report 'appear on top' or set as dialog so the user has to close it before returning to the main report - to find out more, google docmd.openreport.

 

[melody.anne]

OK, I think I get the idea

Assuming your controls have the same names as the headings And assuming you are opening another report (we'll call it rptDetail) and it uses the sql you refer to here


then for example, in the OC01 control doubleclick event (you can use click if you want, but users tend to click on a field to select a column, but up to you) put the following code

 docmd.OpenReport "",,,"UserName='" & me.username & "' AND OrderType='" & me.OC01 & "'"

Note that you should modify your report to not filter by the ordertype since it is handled here and means you can use the same report for all ordertypes.

You need to repeat this code for each ordertype, modifying the ordertype filter for each one

You may need to play around with the other openreport parameters to ensure the report 'appear on top' or set as dialog so the user has to close it before returning to the main report - to find out more, google docmd.openreport.

I'm not sure I get this code entirely
docmd.OpenReport "",,,"UserName='" & me.username & "' AND OrderType='" & me.OC01 & "'"

What is the ",,," supposed to be? Also, the "UserName=" makes me believe that I'd have to input that username everytime I want to check, and I want it automatically. I could be wrong, but I haven't written codes in years. Unless the "me.username" part does the whole "input" thing?

I was just going to show the details in the Access database itself (but this is prettier)

 

[CJ_London]

What is the ",,," supposed to be?

to find out more, google docmd.openreport.

Unless the "me.username" part does the whole "input" thing?

Assuming your controls have the same names as the headings

It does - me.username is the name of your control

 

[melody.anne]

It does - me.username is the name of your control

Tried the code out, it still won't filter by username. Same problem but now it's on report form :/

 

[CJ_London]

can you post the code you are actually using

 

[melody.anne]

It does - me.username is the name of your control

UPDATE:

did
DoCmd.OpenReport "rptName", acViewPreview, , "[Username] = " & Me.[Username]

And it prompts me to enter a parameter value. However, the prompt tells me the Username associated with the field I clicked. If I type in the same username that is in the parameter input window, it returns exactly what I want. Now my new problem is how to input the parameter value automatically.

PS, the OC01 is filtered in the query, therefore I think it's unnecessary on the code.

 

[CJ_London]

Can you post the sql to the report you are opening - I suspect you still have parameters set which is fine when opening the recordsource as a query but not when it is used in a form or report.

PS, the OC01 is filtered in the query, therefore I think it's unnecessary on the code.

Unless you want a different report for each ordertype and prepared to add a new report every time you add a new ordertype (which is a very bad design) then I disagree

 

[melody.anne]

Unless you want a different report for each ordertype and prepared to add a new report every time you add a new ordertype (which is a very bad design) then I disagree

I agree. The reports for each order type are the same. However, it always gives me an error when I try to add a second filtering criteria.

This code is currently working exactly how want it to, minus the fact it's not filtering order types

DoCmd.OpenReport "rptName", acViewReport, , "[Username]='" & Me.[Username] & "'"

I do not know why it gives me errors when I add a second criteria.

PSS, Im replying on my phone since the computers are very strict with websites, so it is very tedious to post the SQL. What I refer to when filtering on the SQL is just WHERE and making it filter by OC01.

 

[CJ_London]

Well, I'm pretty sure the problem is with your recordsource sql for the report you are opening - I would remove all parameters/criteria for now and see what happens, then build your criteria back for everything except the username and ordertype

However, it always gives me an error when I try to add a second filtering criteria.

Are you adding as I suggested i.e.

docmd.OpenReport "",,,"UserName='" & me.username & "' AND OrderType='" & me.OC01 & "'"

Suggest you post the VBA code that is failing after checking
is your control for ordertype called OC01?
is your field in the report you are trying to open called OrderType?
What is the actual error you are getting - provide the description

 

[melody.anne]

Well, I'm pretty sure the problem is with your recordsource sql for the report you are opening - I would remove all parameters/criteria for now and see what happens, then build your criteria back for everything except the username and ordertype


Are you adding as I suggested i.e.

docmd.OpenReport "",,,"UserName='" & me.username & "' AND OrderType='" & me.OC01 & "'"

Suggest you post the VBA code that is failing after checking
is your control for ordertype called OC01?
is your field in the report you are trying to open called OrderType?
What is the actual error you are getting - provide the description

Here goes. The code I'm trying to use is:

DoCmd.OpenReport "rptName", acViewReport, , "[Username]='" & Me.[Username] & "' AND OC01='" Me.OC01 & "'"

Which for me is:

DoCmd.OpenReport "rptName", acViewReport, , "[Usuario]='" & Me.[Usuario] & "' AND OC01='" Me.OC01 & "'"

Yes the field name is oc01 and the source as well, which might be the source of the problem. Oc01 is the header of the table, which was cross tabbed from a table that had OC01 as a value in a column named OrderType. It prompts me to enter a parameter for OC01

This is the SQL:

SELECT[Listado de Empleados].Area,[Listado de Empleados].Supervisor, Teléfonos.Telefono, [E 037].Usuario, [E 037].Porción, [E 037].[Unidad de Lectura], [E 037].[Clase de Orden], [E 037].[Fecha Generación], [E 037].[Clase de Actividad], [E 037].Estado, [E 037].[Número Orden], [E 037].[Punto de Suministro], [E 037].[Cuenta Contrato], [E 037].[Número de Contador], [E 037].[Secuencia Teórica], [E 037].Cliente, [E 037].Dirección, [E 037].Municipio, [E 037].Latitud, [E 037].Longitud, [E 037].Comentario
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]
WHERE (([E 037].[Clase de Orden])="OC01");

I apologize for the Spanglish. The main table is "E 037", OrderType is actually "Clase de Orden"

 

[CJ_London]

OK

you need to remove this from your sql

WHERE (([E 037].[Clase de Orden])="OC01");

and modify openreport to

DoCmd.OpenReport "rptName", acViewReport, , "[Usuario]='" & Me.[Usuario] & "' AND [Clase de Orden]='" Me.OC01 & "'"

 

[CJ_London]

Also, to be clear

DoCmd.OpenReport "rptName", ....

rptName needs to be the name of your report

 

[melody.anne]

OK

you need to remove this from your sql

WHERE (([E 037].[Clase de Orden])="OC01");

and modify openreport to

DoCmd.OpenReport "rptName", acViewReport, , "[Usuario]='" & Me.[Usuario] & "' AND [Clase de Orden]='" Me.OC01 & "'"

Tried this, and it gave me a "Compile error: syntax error"

 

[CJ_London]

Tried this, and it gave me a "Compile error: syntax error"

and rptName?

Also, have you retried it, I can see no reason for you to get a syntax error at the moment with this code - if you do, please copy and paste what you actually have that generates the error

 

[melody.anne]

Also, to be clear

DoCmd.OpenReport "rptName", ....

rptName needs to be the name of your report

I know that, and I modified it. It just gives me a compilation error for some reason.

This is exactly the code as I used it:

DoCmd.OpenReport "rpt OC01 Details Aguadilla", acViewReport, , "[Usuario]='" & Me.[Usuario] & "' AND [Clase de Orden]='" Me.OC01 & "'"


It gives me a "Compile error. Expected: end of statement" error and highlights the "Me" that I have bolded.

 

[CJ_London]

you need another ampersand

[Clase de Orden]='" & Me.OC01 & "'"

missed it before, it was in my original, didn't notice it had dropped out

 

[melody.anne]

you need another ampersandmissed it before, it was in my original, didn't notice it had dropped out

Alright, error is gone. Now it's pulling no data at all, only giving me blank reports XD

PS, hope I'm not being too annoying