getting the correct value on a requery in a subform

Posted on 2003-03-11
Medium Priority
Last Modified: 2011-04-14
Hi, I'm trying to get a combobox on a control on a subform to requery, so the new values that i add will show up.  

I have a form called Study_Information and a subform called LotStudy_subform.  The record source for the subform is a query.  Here is the SQL for the query, if needed:  

SELECT DISTINCTROW LotStudy.InternalStudyID, LotStudy.LotID, Lot.SpeciesID, Lot.StrainID
FROM Lot INNER JOIN LotStudy ON Lot.LotID = LotStudy.LotID;

There is a combo box on the LotStudy_subform called "LotID".  I want this control to requery automatically, when I click the combo box.  I'll be checking back periodically to see if you guys needs additional information.
Question by:marchesinij
  • 2
  • 2

Expert Comment

ID: 8112758
I think you could put code in the AfterUpdate or OnClick event of the combo box like this:


Or you might just be able to do


I think the .Refresh command might do it alone, but I am not positive.

Author Comment

ID: 8113370
I've tried all of these.  The code for this goes in the subform.  I put a breakpoint in the code after on the AfterUpdate or Onclick event, but it doesn't even stop there.  Therefore, it never gets to the Me.Refresh or Me.Requery lines.  I'm thinking I need something in the code of the form not the subform.  
LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 600 total points
ID: 8113808
In the combo's GotFocus event, you can do Me.Requery, however you really only want to update it when you actually change its underlying data, not everytime it gets the focus.

To do that, you would update the recordsource, then do:


  Note that 'LotStudy_subform' should be the name of the subform *control* on the main form. This may or may not be the same as the name of the subform you see in the database container.


Expert Comment

ID: 8113830
I am not sure why, but I created a new subform, stuck a combo box on it, and I found the same issue (events for the subform were not working). Then I made a new form, put in a subform, and the events started triggering again. Sorry I am not able to identify why this happened. Perhaps another user can.

Author Comment

ID: 8114015
Dettman, you are a hoss.  big up

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question