Requery subform

[ST4RCUTTER]

I have two subforms that are sourced from the same query. One is in continuous form view and is used to select values, the second is in datasheet view and shows all the values that have been selected (multiple rows here).

My goal is that the user will use the first form to make selections and then the second subform in datasheet view will update with the new row of data. This confirms to the user that the date has been added.

Subform1 is called: frm_sub_milestones
Subform2 is called: frm_sub_milestones2

The afterupdate is set to trigger when the control [DateEntered] on Subform1 is populated. Subform2 should requery and refresh (not sure if both are required). My code looks like this:

Private Sub DateEntered_AfterUpdate()

Me.subfrm_dates.Form.Requery

End Sub

After entering a date nothing happens, although the record is added to the table.

 

[boblarson]

Is the name of the subform CONTAINER control on the main form which houses the subform on the main form named subfrm_dates? If not, you need to either change it to be that name or use the control's name instead of the subform name.

 

[ST4RCUTTER]

Thanks for such a fast reply Bob! Let me see if I can clarify. When you say subform CONTAINER are you referring to the "Name" as found on the Other tab in the properties window? If so, then yes, it is named subfrm_dates.

 

[ajetrumpet]

Here's a simple explanation of this concept, if I may:

There are always 2 parts to a subform object: the form control and the source object.

That button that's in the design tool box, that has the shape of a subform? That's the form control. The object you see in the database window after you save it is the source object. They are two different things, not to be confused.

In short, a subform is made up of two things: Form Control, and Form object. The control is the same as any others that belong on a form (text box, combo, whatever...) You can see it too: go to the form's module, and type in "me." and look for the name of the container. It's there...

Hey Bob, I am taking a count of how many times you spell the word "container" in CAPS. As of now, I have 50 hands.

 

[ST4RCUTTER]

Thanks ajetrumpet! That being the case, the source is frm_sub_milestones2. How does one "go to the form's module"? Is this done by entering code builder?

 

[ajetrumpet]

Thanks ajetrumpet! That being the case, the source is frm_sub_milestones2. How does one "go to the form's module"? Is this done by entering code builder?

How do you go to the form's module? A subform is a form too. It's in the project explorer window of the VB editor. Take a look...

If you want to edit it, open the form from the DB window just like you do the main form. But remember, if the main form is open, the subform is also considered to be open.

 

[boblarson]

Thanks for such a fast reply Bob! Let me see if I can clarify. When you say subform CONTAINER are you referring to the "Name" as found on the Other tab in the properties window? If so, then yes, it is named subfrm_dates.

Adam has already explained about this, but if you want a little further clarification, take a look here and view the screenshot especially:

http://www.btabdevelopment.com/main...rhowtoreferencesubforms/tabid/76/Default.aspx

 

[ST4RCUTTER]

Ok, I believed I had the correct VBA code but this is still not working. It must be how I have this tab setup. Here is the database. Can someone look at this and see what it is I am missing? Just the code for the tab called Project Milestones...if I can see how that is done I can do all the other tabs as well.

I am currently using this code:

Forms!frm_Main.frm_sub_milestones2.Form.Requery

Thank you in advance!

 

[boblarson]

In the after update event of the combo and the date text box put this:

If Me.Dirty Then Me.Dirty = False

 

[ST4RCUTTER]

Bob, thanks for the near-immediate response...you are prolific! And this works great...with one exception. I have code set for the ondoubleclick event that inserts the current date. That seems to prevent the subform from requerying. I tried adding the dirty = clean code to that subroutine but that didn't work...so my question is can I have the best of both worlds?

 

[ajetrumpet]

Bob, thanks for the near-immediate response...you are prolific! And this works great...with one exception.

Don't question the Boss.

 

[boblarson]

This may be late as I have been really busy today and haven't had a chance to get on the board until just a little while ago.

But, have you tried calling the after update event just after the double click event runs:

Private Sub MyTextBox_DoubleClick()
   Me.MyTextBox = Date
   MyTextBox_AfterUpdate
End Sub

 

[USnick]

A little confused (or maybe a lot)

I am looking at a similar issue. I have a form set up with a sub-form showing dates (columns) and rooms (rows). The range of dates is entered on the main form. I can't get the subform to requery (except by clicking on a subform field and pressing Shift-F9).

I tried to understand and apply the answers to similary questions I found here, but to no avail. The only thing I did not try was to create a VB module as I am not comfortable with that. Is there a way to do this from a macro, which is called from the After Update parameter for the date on the main form?

Thanks in advance.

 

[boblarson]

When do you want to requery? Are you updating something somewhere which then would logically need a requery? I'm assuming you put in your dates but then are you using a button to requery, or are you wanting to do it on the after update of one of the controls?

To put in the VBA code here's something that might help to see how to get to the code window:
http://www.btabdevelopment.com/main/QuickTutorials/Wheretoputcodeforevents/tabid/56/Default.aspx

And to do the requery from the main form you use

Me.YourSubformCONTAINERName.Form.Requery

where YourSubformCONTAINERName is the name of the control on the main form which houses the subform, not the subform itself. The subform container control can be named the same as the subform, but it isn't always, so you need to use the container name and not the subform name.

 

[USnick]

I am trying to do the requery after update of the controlling date field on the main form. If I understand your message correctly I need to put the suggested code in under VB. But I would rather avoid that as I am new to VB and not comfortable with it.

Is there a way to call the same function from a macro?

Thanks for the quick reply and for any additional help you can offer.

Nick

 

[boblarson]

I am trying to do the requery after update of the controlling date field on the main form. If I understand your message correctly I need to put the suggested code in under VB. But I would rather avoid that as I am new to VB and not comfortable with it.

Is there a way to call the same function from a macro?

Thanks for the quick reply and for any additional help you can offer.

Nick

I understand your intrepidation as I went for 4 years before taking the plunge, but it really isn't as bad as it seems. We'll help you with the transition (macros are very limited in what they can do) and there is this great site which will help as well:
http://www.functionx.com/vbaccess/index.htm

And, what I wrote earlier about the syntax for your requery is as simple as replacing the YourSubformCONTAINERName with the actual name of the control on your main form that houses the subform. The ME keyword is a programming shortcut that refers implicitly to the form that the code is written in. So, if the code is on the main form, ME refers to the main form, if the code was on the subform ME refers to the subform and Me.Parent refers to the main form.