Enter Parameter Message Generated by Report based on Query

[AlexB123]

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:

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:

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!!!

 

[Joe4]

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).

 

[AlexB123]

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?

 

[xenou]

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)

 

[xenou]

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).

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.

 

[AlexB123]

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 ...

 

[AlexB123]

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).

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:

SELECT ID, UserName, daysToPwExpiry, daysToInactivate
FROM tblUsers
WHERE [Active?] = True;

qryInactiveUsers

SELECT tblUsers.ID, tblUsers.UserName, tblUsers.daysToPwExpiry, tblUsers.daysToInactivate
FROM tblUsers
WHERE (((tblUsers.[Active?])=False));

 

[xenou]

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.