Program Macro to Return Results Based on Checkbox (1 Viewer)

Kstruth

New member
Local time
Yesterday, 19:23
Joined
Oct 10, 2019
Messages
9
I have a SharePoint list attached to Access to that feeds all fields in a beautiful Access report.

Currently, a macro currently in the form created prior to my arrival. One complete report with all personnel's reports and one specifically for each personnel to view their own reports to check for accuracy.

Each individual should be able to hit the programmed button and see their reports only and if the reports are "Ready for Meeting" (checkbox is checked). This way they can preview in Print Preview to ensure all data transferred/etc.

The macro is not working correctly (I do not write macros) and is currently returning results for each individual, however it includes all of their reports instead of just ones marked "Ready for Meeting"
 

theDBguy

I’m here to help
Local time
Yesterday, 16:23
Joined
Oct 29, 2018
Messages
8,597
Hi. Can you post the code behind the macro?
 

Kstruth

New member
Local time
Yesterday, 19:23
Joined
Oct 10, 2019
Messages
9
Hi there - so a bit simplistic - no code.

Report Selection: Command12 On Click

OpenReport

Report Name:
View:print Preview
Filter Name:
Where Condition:
Window Mode:Normal

Otherwise not sure how to see the code. It is not Visual Basic as of now. it looks like a wizard action.
 

theDBguy

I’m here to help
Local time
Yesterday, 16:23
Joined
Oct 29, 2018
Messages
8,597
That's good enough for now. It looks like the WhereCondition is empty, that's why you're seeing all the records. To filter the report to a specific user, you'll need to add a Criteria in the WhereCondition. It might look something like this:
Code:
"UserID=" & [UserID]
That example will probably not work by itself. We may have to see more about your form or report to give you the exact syntax.
 

Kstruth

New member
Local time
Yesterday, 19:23
Joined
Oct 10, 2019
Messages
9
OK. So the button in particular is returning the correct user, however the one parameter I don't know how to program is only show if "Ready for Meeting" field is checked YES. What is the syntax for that?

I should add the user name is coming from an unbound list of personnel the user selects before hitting the "View by Personnel" button.
 

theDBguy

I’m here to help
Local time
Yesterday, 16:23
Joined
Oct 29, 2018
Messages
8,597
Maybe, what we need to change is the query you're using for the report. Can you post the SQL statement of the query? Thanks.
 

Kstruth

New member
Local time
Yesterday, 19:23
Joined
Oct 10, 2019
Messages
9
SELECT [PjM Presentation Order].[Presentation Order], [eQuad - PMR Ready].[PMR Ready?], [eQuad - PMR Ready].[TO Status], [PjM Presentation Order].[PjM FirstName LastName], [eQuad - PMR Ready].Notes, [eQuad - PMR Ready].[Is this to be briefed at next PMR?] AS Expr1, *
FROM (SELECT DISTINCTROW eQuad.*, [eQuad Image Library].[Embedded PowerPoint], eQuad.[Mark for Archive] FROM eQuad LEFT JOIN [eQuad Image Library] ON eQuad.[CRQ #] = [eQuad Image Library].[CRQ #] WHERE (((eQuad.[Mark for Archive])=No)) ORDER BY eQuad.[Presentation Order], eQuad.[Slide Order]) AS [eQuad - PMR Ready] INNER JOIN [PjM Presentation Order] ON [eQuad - PMR Ready].PjM = [PjM Presentation Order].PjM
WHERE ((([eQuad - PMR Ready].[PMR Ready?])=Yes) And (([eQuad - PMR Ready].[TO Status])="Current TO") And (([eQuad - PMR Ready].[Is this to be briefed at next PMR?])=Yes) And (([eQuad - PMR Ready].eQuad.[Mark for Archive])=No) And (([eQuad - PMR Ready].Hold)=No)) Or ((([eQuad - PMR Ready].[TO Status])="Current TO") And (([eQuad - PMR Ready].[Is this to be briefed at next PMR?])=Forms![Report Selection]![PMR only check]))
ORDER BY [PjM Presentation Order].[Presentation Order], [eQuad - PMR Ready].[Slide Order], [eQuad - PMR Ready].[Task Order #];
 

theDBguy

I’m here to help
Local time
Yesterday, 16:23
Joined
Oct 29, 2018
Messages
8,597
Hi. I don't see anywhere in your SQL where you were trying to limit the result to those ready for a meeting. Or, is that the question, how to add it?
 

Kstruth

New member
Local time
Yesterday, 19:23
Joined
Oct 10, 2019
Messages
9
Yes, please tell me how to do that because it worked long before coming on board - but I have never seen it work since I've been here.
 

theDBguy

I’m here to help
Local time
Yesterday, 16:23
Joined
Oct 29, 2018
Messages
8,597
Yes, please tell me how to do that because it worked long before coming on board - but I have never seen it work since I've been here.
Well, first, you could try to experiment by removing all the criteria first. When you run the query, you should see all the records. Then, you could add each criteria one at a time to make sure it keeps working. For example, you could start with (assuming the "ready for meeting" field is a yes/no field):


...WHERE [Ready for meeting]=True
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom