Enter Parameter Message Generated by Report based on Query

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
203
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:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,528
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 19, 2014
Messages
203
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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:

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
203
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

Board Regular
Joined
Dec 19, 2014
Messages
203
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));
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,792
Messages
5,655,332
Members
418,190
Latest member
Timex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top