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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 58

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

752 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