Full Version: Query pop-up when the form closes
UtterAccess Forums > Microsoft® Access > Access Forms
rgonzalez
Hello Everyone,
Thank you for this wonderful hub of Access knowledge!
I have this problem that seems pretty easy to solve, but for one reason or another I just can't put my finger on what to do.
The form I'm working on has a check box that is used to requery and show the results in a list box, depending if you want to search by the last name or the Personnel number. The two underlining queries are set up to accept a value which are entered via a text box (same text box, caption is just set to change according to what you're searching by) found on the form. The reason for the text box is that there are MANY employees to choose from, so it's set up to search the list box and narrow down the results... which then can be clicked on via the list box and corresponding record is displayed.
This part works fine. On form load, it defaults to search by Last name. When you click the check box then you have then search by personnel number. If you click again, back to search by last name.
My problem is when I exit the form via the close button (just your regular docmd.close). I always get a query pop-up asking for a value. If I exit using the "x" on top, it closes fine. Eventually all that is going to be hidden though. I'd like to get rid of this pop-up box when the form closes. Here is the code I use for my check box...
Private Sub Check126_AfterUpdate()
If Me.Check126.Value = True Then
List120.RowSource = "SELECT qrySearchLastName.EmployeeID, qrySearchLastName.EmployeeNumber, qrySearchLastName.[Last Name], qrySearchLastName.[First Name], qrySearchLastName.Initials FROM qrySearchLastName ORDER BY qrySearchLastName.[Last Name]; "
List120.Requery
Me.Label135.Caption = "Search By Last Name"
End If
If Me.Check126.Value = False Then
List120.RowSource = "SELECT qrySearch2.EmployeeID, qrySearch2.EmployeeNumber, qrySearch2.[Last Name], qrySearch2.[First Name], qrySearch2.Initials FROM qrySearch2 ORDER by qrySearch2.[EmployeeNumber];"
List120.Requery
Me.Label135.Caption = "Search By Personnel Number"
End If
End Sub
Any help or suggestions would be greatly appreciated!!!
Robert
balaji
Is there any code in the form's onclose() event? What does the query popup ask for?
Larry Larsen
Hi
Just a small observation.. you don't need to requery the listboxes after their respective rowsources are rebuilt like in your code..
eg:
CODE
List120.RowSource = "SELECT qrySearchLastName.EmployeeID, qrySearchLastName.EmployeeNumber, qrySearchLastName.[Last Name], qrySearchLastName.[First Name], qrySearchLastName.Initials FROM qrySearchLastName ORDER BY qrySearchLastName.[Last Name]; "
[color="red"]List120.Requery[/color] [color="green"]  << Needs removing[/color]

thumbup.gif
Larry Larsen
Hi
Sorry..
Welcome to Utter Access Forums..
thumbup.gif
rgonzalez
Thanks for the welcome!
Is for the form, I removed the list120.requery(s). I also checked the Unload and Close events. No code there.
What it asks for is a value, just like if you were to click on the queries themselves... the 'Enter Parameter Value" pop-up box
The way I designed it is by creating 2 similar queries. They both query the main employee table for the
Employee ID - Primary Key
Personnel Number - assigned number unique to every employee
Last Name
First Name
Initial
They both accept values from the same text box on the form. One accepts the text value under "Last Name". The other accepts the value under "Personnel Name". The text box is named txtLName and this is placed as the criteria for both queries...
Like [Forms]![subfrmUpdate_Employee]![txtLName] & "*"
Thanks again!
daveg44
Robert,
I am having exactly the same problem with my event booking application. It records Events (primary key EventID), Customers (primary key CustomerID) and a link table for Booking (primary key EventID and CustomerID), simple enough. I have a simple screen bound to the Event table that has a list box showing customer booking info so I can cycle though the events and see how bookings are going. The list box uses a simple query to links to the form by EventID. It all works fine until I close the form using a close button. I also get the "Enter Parameter Value" pop up which asks for a value corresponding to the list box query parameter [Forms]![FBooking]![EventID]. I also cannot solve the problem
Dave
rgonzalez
Hello Dave, at least someone shares my frustration! wink.gif
From all the tests I've run, it's definately the fact that I'm using 2 queries pointing back to the same text box, but I can't think of another way to make this work. If I try to use select statements from the same query all the "null" values get filtered out... so using two queries has been the only way to get this to work right (which work right it does, except for the query box that pops up when I close).
If any one else has any other ideas or needs more information please let me know. Thanks again!
balaji
Can you post your db? Remove all confidential and sensitive information, compact and repair, zip and then attach it to your post. Make sure the attachment is less than 500 kb. Also, explain what steps I should take to duplicate the error once I open the database.
rgonzalez
Balaji,
I can make another copy of my database and "dummy" it up, but that would take a quite a bit of time that I don't have at the moment... but I will when I have a chance. I truly appreciate your offer to look into it for me though.
onestly, the issue doesn't sound that complicated I'm just missing something minor. I just can't seem to pinpoint it. Here's another clue... I put a Msgbox("I'm here!") statement on the OnClose event procedure, just to see if something is happening right as the form closes. Well, the message box pops up, I hit ok, the form closes, then ANOTHER instance of the form pops up in a minimized window, and once again asks to "Enter the Parameter" in another pop-up box while the old form is still in the background.
rgonzalez
Well, I redid my form a little and accidently found the fix.
ave, if you're still having an issue type this in your OnClose Event
me.<yourlistboxname>.rowsource = " "
This should stop your listbox from querying for a final value on close. Good luck!
Robert
balaji
I am glad you got the issue sorted out. Good luck with your project. thumbup.gif
daveg44
Robert
you are a legend. I have wasted hours on this. I have done so many tests etc it was sending me nuts. Importing dummy forms and tables from a test database, even running in different versions of Access.
I am now highly suspicious that it is a 'feature' of V2002 as I cannot get the symptom to appear in identical test applications running in Access97 or 03
Thanks again
Dave
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.