Likes Likes:  0
Results 1 to 8 of 8

Thread: Enter Parameter Message Generated by Report based on Query

  1. #1
    Board Regular
    Join Date
    Dec 2014
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Enter Parameter Message Generated by Report based on Query

    Hi everyone!

    In my database I have implemented a number of login features to protect the database. Every 60 days users are forced to update their password, and if they have not logged in for 90 days they are prevented from logging in until an administrator restores their privileges. In order to help managers track which users have been inactivated, I created a report that is emailed to them after a inactivated user attempts to log on.

    However, a message box pops up asking for the "UserID" parameter, after the user has attempted to log in and the report is being generated and emailed to the managers. I have rewritten the query, and it still produces this message. The query is based off of two tables ... tblUsers, where the PK is "ID", and tblPWChange, where "ID" is a FK but stored as "UserID".

    Here is the OG query:

    Code:
    SELECT a.ID, a.UserName, b.dtPwExpiry, b.dtUserInactivate
    FROM tblUsers AS a INNER JOIN tblPWChange AS b ON a.ID = b.UserID
    WHERE a.daysToInactivate <= 0;
    Here is the new Query:

    Code:
    SELECT a.ID, a.UserName, (SELECT MAX(dtPwExpiry) FROM tblPWChange WHERE UserID = a.ID) AS dtPwExpiry, (SELECT MAX(dtUserInactivate) FROM tblPWChange WHERE UserID = a.ID) AS dtUserInactivate
    FROM tblUsers AS a
    WHERE a.daysToInactivate <= 0;
    I thought the issue was related to the fact that there are multiple rows in tblPWChange where the ID/UserID is referenced ... hence, my solution was to add the Select Subqueries. But this has not worked. Can anyone help me to troubleshoot this issue?

    Many thanks!!!
    Last edited by AlexB123; Apr 17th, 2019 at 01:59 PM.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    48,938
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Enter Parameter Message Generated by Report based on Query

    Often times, when you get an unexpected Parameter box pop up, it oftens means you have a typo or an error in your query somewhere. Basically, it is looking for a field with the name that is popping up in the Parameter box, and it cannot find it.

    You see that often if someone renames or deletes a field from the underlying table (and the query was referencing it). Take a look at your queries and make sure that references to "UserID" in your query are valid (i.e. a field with the exact name "UserID" appears the tblPWChange table).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Dec 2014
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enter Parameter Message Generated by Report based on Query

    I reviewed the queries, as well as the reports, and everything seems good. However, I was able to confirm it was the report that generates the Parameter box. I double checked all the fields and such, found nothing. All I can think to do is rename my tblPWChange UserID column as "ID"? Can you think of any other solution or lines of inquiry?

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,300
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Enter Parameter Message Generated by Report based on Query

    Code:
    SELECT a.ID, a.UserName, (SELECT MAX(dtPwExpiry) FROM tblPWChange WHERE UserID = a.ID) AS dtPwExpiry, (SELECT MAX(dtUserInactivate) FROM tblPWChange WHERE UserID = a.ID) AS dtUserInactivate
    FROM tblUsers AS a
    WHERE a.daysToInactivate <= 0;
    this query does have a a field reference in it called UserID which appears to not be in tblUsers (where you have just an ID field). So the UserID needs to be supplied - hence, a popup message. The offending part looks to be here: (SELECT MAX(dtPwExpiry) FROM tblPWChange WHERE UserID = a.ID)
    Last edited by xenou; Apr 17th, 2019 at 05:19 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,300
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Enter Parameter Message Generated by Report based on Query

    THis might be one way of getting what you are after (as well as using the select subqueries as you did above - but you still need to joint the rights fields with the right names on the right values).


    Code:
    SELECT 
    
    	b.UserID, 
    	a.UserName, 
    	b.MaxOfdtPwExpiry as PWExpiredDate,
    	b.MaxOfDtUserInactivate as UserInactivatedDate
    	
    FROM 
    
    	tblUsers a
    	inner join 
    	(
    		select 
    		b.UserID, 
    		Max(b.dtPwExpiry) as MaxOfdtPwExpiry, 
    		max(b.dtUserInactivate) as MaxOfDtUserInactivate 
    		from 
    			tblPWChange b 
    		Group By b.UserID
    	) b
    	
    	on 
    	a.ID = b.UserID
    
    WHERE 
    
    	a.daysToInactivate <= 0;
    Untested as you might guess - no sample data created for this on my end to test with.
    Last edited by xenou; Apr 17th, 2019 at 05:21 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    Board Regular
    Join Date
    Dec 2014
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enter Parameter Message Generated by Report based on Query

    this query does have a a field reference in it called UserID which appears to not be in tblUsers (where you have just an ID field). So the UserID needs to be supplied - hence, a popup message. The offending part looks to be here: (SELECT MAX(dtPwExpiry) FROM tblPWChange WHERE UserID = a.ID)
    UserID is the column name in tblPWChange where the equivalent numbers are stored. Does having a different name, in this table, from the corresponding tblUsers PK "ID" violate some sort of SQL rule? The query itself seems to run fine ...

  7. #7
    Board Regular
    Join Date
    Dec 2014
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enter Parameter Message Generated by Report based on Query

    Quote Originally Posted by xenou View Post
    THis might be one way of getting what you are after (as well as using the select subqueries as you did above - but you still need to joint the rights fields with the right names on the right values).


    Code:
    SELECT 
    
        b.UserID, 
        a.UserName, 
        b.MaxOfdtPwExpiry as PWExpiredDate,
        b.MaxOfDtUserInactivate as UserInactivatedDate
        
    FROM 
    
        tblUsers a
        inner join 
        (
            select 
            b.UserID, 
            Max(b.dtPwExpiry) as MaxOfdtPwExpiry, 
            max(b.dtUserInactivate) as MaxOfDtUserInactivate 
            from 
                tblPWChange b 
            Group By b.UserID
        ) b
        
        on 
        a.ID = b.UserID
    
    WHERE 
    
        a.daysToInactivate <= 0;
    Untested as you might guess - no sample data created for this on my end to test with.
    @xenou, this code worked! However, I have two similar reports, one of active users and another of inactive users. Both of these are based solely off of tblUsers. When I load these reports, I get another Parameter Box for "UserID" ... even though it's not referenced all. However, I do believe that when I created these two reports, I simply copied the report above and then changed the relevant fields/controls. Ideas/solutions?

    qryActiveUsers:
    Code:
    SELECT ID, UserName, daysToPwExpiry, daysToInactivate
    FROM tblUsers
    WHERE [Active?] = True;
    qryInactiveUsers
    Code:
    SELECT tblUsers.ID, tblUsers.UserName, tblUsers.daysToPwExpiry, tblUsers.daysToInactivate
    FROM tblUsers
    WHERE (((tblUsers.[Active?])=False));

  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,300
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Enter Parameter Message Generated by Report based on Query

    One solution would be to recreate the report instead of copying it from another report. Otherwise, you have to keep looking to find where the problem is.

    The problem is not in the names (whether UserID, ID, User_ID, or any other possible name). It's that in the query there is nothing that provides the value for it.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •