Yes, Another Enter Paramater Value ? Problem

[millsbrandonk]

Hello everyone,

I know this may seem very simple to most people, but I am completely stuck! When I run this query, I get the Enter Parameter Value pop up box. The box is not supposed to pop up. Here is some background on whats happened....
We have been running on this same exact access database, which is using a Front End and a Backend database as two separate file, for about 15 years now. It was originally on Office XP(2002?). We are actually still using it on an old Windows XP machine which is running Office XP. I have been toying with trying to upgrade this database to 2016 or newer for a couple of years now. So on my home machine, I am running VirtualBox which I have setup a clean fresh Windows 10 and Office 2007, as I am trying to go through all of the versions of office working my way up to catch all the problems.

It has worked fine in 2003, but with 2007, 2016, and 365, the same exact Enter Parameter Value box comes up. I have done the conversion to accdb, it has the same problem wether its the original mdb or not. I have read literally hundreds of forums, and have been through all of the procedures of using the document analyzer, and finding the parameter, have looked through every different view type of the query, and added quotes around the parameter, removed them, checked for spelling, removed and re typed all manually, added FROM clauses in different areas, removed the table name and left just the field name.


I just cannot figure out what would have changed between 2002 and 2007 that would cause this to only now start popping up.


The parameter value it is asking for specifically in the "Enter Parameter Value ? " Box is GeneralLedger.TransDateTime . The GeneralLedger is the table name, which is in another database, located in the same folder, and is linked to this database, which is the user front end. I have checked the linking and its still there. The value put in the box does not matter, and the program functions properly still when I dont enter any information, or if i enter the exact information, with the proper spacing and format. I have checked all of my data that its supposed to be logging, and its all there. So this pop up box, I dont need it. but need it to go away.


It is possible that this is not where the problem is, that is causing the Enter Parameter Value to pop up. But no other query has the GeneralLedger in it.


Any help would be Greatly Appreciated.
Thank you In Advance!!

SELECT count(GeneralLedger.TransDateTime) AS rowcnt, (select min(GeneralLedger.TransDateTime) FROM GeneralLedger WHERE GeneralLedger.TransDateTime>[Forms]![basLoggedOnEmployee]![CheckInDateTime] And GeneralLedger.AddUser = UserName() And GeneralLedger.TransDetails = "Cash Count Start") AS StartDateTime
FROM GeneralLedger WHERE GeneralLedger.TransDateTime>[Forms]![basLoggedOnEmployee]![CheckInDateTime] And GeneralLedger.AddUser = UserName() And GeneralLedger.TransDetails = "Cash Count Start";

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[millsbrandonk]

As the db was too big I created a dropbox link

Both files must be placed into C:\buddies
(c:\buddies\data.mdb , c:\buddies\pgm.mdb)

https://www.dropbox.com/s/k6c6z8ain5wtxi6/db.zip?dl=0

[June7]

Which query or form or report has issue?

If you had made copies and deleted most records and/or deleted objects not related to issue, could probably have posted here. Many will not download files from other sites.

[Micron]

I think I opened about 41 queries and never got that prompt. Where in your post did you say which of the 50 or so queries does this because I can't see that and am not going to run make table queries to look for it.

[Micron]

I suggest you look at all the path properties of the linked tables, especially GeneralLedger. Somewhere along the way you probably didn't successfully relink that table when creating your copies/new versions of the db. All you have to do to see the link path is mouse over a linked table and it will pop up. I found the query in question by writing a sub to iterate over the queries collection looking for part of the sql. Consider that my Christmas present to you It seems to be qryStartingDrawerDateTime and it does not present the same prompt to me.

If that's the wrong one, you'll have to exchange it.

[millsbrandonk]

I suggest you look at all the path properties of the linked tables, especially GeneralLedger. Somewhere along the way you probably didn't successfully relink that table when creating your copies/new versions of the db. All you have to do to see the link path is mouse over a linked table and it will pop up. I found the query in question by writing a sub to iterate over the queries collection looking for part of the sql. Consider that my Christmas present to you It seems to be qryStartingDrawerDateTime and it does not present the same prompt to me.

If that's the wrong one, you'll have to exchange it.

Yes, That is the query that I believe the problem is generating from. I have just double checked the linking and even refreshed the link, the table shows linked properly, and all other functions, work properly and it stores data to that table like it should.
Do you see any errors in the code that could be causing it to display the Enter Parameter Value box for GeneralLedger.TransDateTime ?


EDIT: If you run the macro, EmpCheckin, and click save/close, and OK, then it will display.

[Micron]

If I don't bypass on opening, I get told I'm not logged in, then I can't do anything. If I do bypass, then what? If I run the macro, of course you get a prompt when the query is run - because the form being referenced isn't open.

[millsbrandonk]

If I don't bypass on opening, I get told I'm not logged in, then I can't do anything. If I do bypass, then what? If I run the macro, of course you get a prompt when the query is run - because the form being referenced isn't open.

It is designed so that the user cannot edit or run anything until they are checked into payroll. It is supposed to hide the buttons on the FrontDesk form until they have checked in. The FrontDesk form opens by default, which triggers a macro to check wether the user is already logged into payroll or not.

[Micron]

Video image pretty small, but was able to figure out how to poke around. If you single step through the macro, you get to see at which point the problem occurs. That is the opening of a form (I forget the name). That form is based on the query. The query runs without error. That tells you the problem is with the form, not the query as you indicate in your post. What then is the issue with the form? It's your OrderBy expression. The field you want to order by is not part of the form's recordsource. Look at the query - that field has criteria applied to it, but it isn't part of the recordset because it's not checked.

I spent more time trying to break in than it took to find the problem. Next time, please provide instructions and don't make your db so difficult to trouble shoot. Remove barriers such as hiding the nav pane, disabling all short cut and standard menus, etc.

[millsbrandonk]

That has Solved it! Thank You very much!!!

Is that something that has changed or become more strict in the newer versions of Access? We have used this for about 15 years with no issues, on OfficeXP. Why now is it differnt?

[Micron]

No idea, assuming you are 100% correct. However, if anyone ever unchecked that field but left the rest of the query alone it would subsequently fail. It should never have worked as it was posted here because the field is not part of the form's recordset, thus you cannot impose a sort order on a field that isn't there. Pretty sure that's not a new thing. My money is on someone having made a design change, or somewhere along the road of moving things to different versions of Access/Office, an old version of the form was brought over.
Glad to have been able to help.