By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Requery Subform

P: 12
How to requery the subform based on date range after update in Main form. Example: The forecast quantity in main form was revised based on the new startdate and enddate. How do I requery the subform in order to revise the forecast quantity?

Thanks for any help.
Sep 23 '12 #1
Share this Question
Share on Google+
16 Replies

Seth Schrock
Expert 2.5K+
P: 2,930
There are a couple of ways to initiate the requery code. You could setup an After_Update event on your enddate textbox or you could create a button that has the On_Click event set. Either way, you would use the following code (replace frmSubForm with the name of your subform):

Expand|Select|Wrap|Line Numbers
  1. Me.frmSubForm.Requery
Sep 23 '12 #2

P: 12
Sorry for my late response, I was trying to work it out. I think I am not clear with my question.

The main form and subform are linked by a CodeName. The main form has a forecast quantity (Fcast) and startdate & enddate)for each CodeName. The quantity for each CodeName in the main form keeps changing at certain date range.
The subform has a quantity field and running dates
Using a command how to auto refresh the quantity field by date range in subform based on quantity changed in the main form. Example: during Jan 15 to 31, quantity in main form changed to 100
how to auto refresh the quantity in subform between Jan 15 to 31? Thank you in advance for your help. Regards EOL
Sep 26 '12 #3

Expert Mod 100+
P: 2,321
I have read it a couple of times, but I simply cannot make head or tails of it.

If the calculation is automatic(Calculated in query, or in the controlsource of the controls), you can simply do a requery as Seth has suggested.

If it is not automatic, then you need to define and explain the logic for us to have any chance of helping you.
Sep 26 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,930
That is what I understood from the first post. The only reason that I can think of that my solution wouldn't work would be that the subform is not a form embedded in the main form, but a separate form that you are calling a subform because of it dependence on the main form. If I am correct that the subform really is a subform control on the main form, what have you tried and what were the results?
Sep 26 '12 #5

P: 12
Thanks for your immediate response. The subform is embedded in the main form. Main form fields: CodeID (unique for each product), Product Name, Production Daily Quantity, StartDate and EndDate.
The embedded subform is linked to the main form by CodeID, I have a command in the main form that initially populate the subform with production daily quantity and running date (from startdate to enddate). During production the Daily Quantity and Enddate on the main form changes. Example: On the main form Item1 original Production Daily Quantity is 100, startdate is June 1 and enddate is June 30. After auto populating the subform, the subform has following records:

Code ID RunningDate Production Daily Quantity
Item1 June 1 100
Item1 June 2 100
Item1 June 3 100
Item1 June 15 100
Item1 June 16 100
Item1 June 17 100 on till June 30

If the quantity on the main form is revised for Item1 to 200 from June 15 to June 30, the subform to auto refresh like below:

Item1 June 15 200
Item1 June 16 200
Item1 June 17 200
.....etc till June 30

How to code it that subform will auto refresh quantity based on date range.

Thanks & regards, EOL
Sep 26 '12 #6

Seth Schrock
Expert 2.5K+
P: 2,930
The actual data doesn't matter for this particular question as we are looking at the subform as a whole and not just trying to update certain fields.

Back to my previous question, what have you tried and what were the results/error messages?
Sep 26 '12 #7

P: 75
can you show us your code?
Sep 26 '12 #8

P: 12
My code can only filter within the date range I need, which is perfect but I don't know how to continue to write the code that will auto replace the quantity on subform based on the revised quantity entered on the main form. Thank you for your help. Regards EOL

Expand|Select|Wrap|Line Numbers
  1. Private Sub Image87_Click()
  2. With Me.subformname.Form
  3.     .Filter = "[DDate] BETWEEN " & Format(Me.LastOfRevSDate, "\#mm\/dd\/yyyy\#") & " AND " & Format(Me.LastOfRevEDate, "\#mm\/dd\/yyyy\#")
  4.     .FilterOn = True
  5. End With
  6. End Sub
Sep 30 '12 #9

Seth Schrock
Expert 2.5K+
P: 2,930
Please use the <CODE/> button when posting code.

Ah, so what you want isn't a requery, but a replacing of a value (very different things). Now the question is, do you want to replace the quantity on the subform for all the records in the subform, or just the record that is selected?
Sep 30 '12 #10

P: 12
Want to replace just the records selected in the subformm based on the revised quantity in the main form. Tank you and regards EOL
Oct 1 '12 #11

Seth Schrock
Expert 2.5K+
P: 2,930
When you say Records, I'm assuming multiple records. How are you selecting the multiple records? Do you have checkboxes or are you just using the Ctrl or Shift buttons to select the ones that you want?
Oct 1 '12 #12

P: 12
I filter the records based on date range using the above code.
Oct 2 '12 #13

Seth Schrock
Expert 2.5K+
P: 2,930
You said, "Want to replace just the records selected in the subformm based on the revised quantity in the main form."

What method are you using to select the records in the subform? At this point, I don't need to know how the subform information is generated, but how are you selecting the individual records? A checkbox?
Oct 2 '12 #14

Expert Mod 5K+
P: 5,285
Seth, ElizabethLOng;
I've been following this for a little while now and now I'm wonder if we have a mis-communication.

When you say "replace" most people, in my experience, will tend to understand you to mean, that the item (record) is discarded and a new item is put in it's place... think changing the tires. If I replace the front driver-side tire, the old tire is sent to the rubish/recycle bin and a new tire from inventory is put into service.

If what you want is a new selection of the records... then the analogy might be along the lines of tire rotation in that one or two of the tires you see in the driver's side might trade sides with the passenger. You haven't replaced the tires, you have a different view.

We do need to understand the real structure underneath the database and the forms.

- What version of MS Access are you using?

- From what I understand, there are two related tables that are being used for the forms; is this correct?

- From what I understand, there are two forms, one form is based on one of the afore mentioned tables and the the second is bassed on the the other table; is this correct?
Oct 2 '12 #15

P: 12
Sorry it took me sometime to reply, still trying to work out my code. I am using MS Access 2007. My code can filter records in the subform based on the date range entered in the main form. The problem with my code after filtering the subform it only refreshed the first record, is there a way to refresh all the filtered records? I need to constantly change the quantity at a certain date range in the subform based on the revised quantity in the mainform. Thanks for your help.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Image87_Click()
  2.     Dim strFilter As Variant
  3.     Dim strSDate As String
  4.     strSDate = Me.SDate
  5.     Dim strEDate As String
  6.     strEDate = Me.EDate
  8.     'check sdate
  9.     If Not IsDate(Me.[SDate]) Or Not IsDate(Me.[EDate]) Then
  10.         MsgBox "Valid dates must be entered"
  11.         Exit Sub
  12.     End If
  13.     If Me.[EDate] < Me.[SDate] Then
  14.         MsgBox "End date must be later than start date"
  15.         Exit Sub
  16.     End If
  18.     'if both sdate and edate are valid, run filter
  19.     strFilter = "[sfmDDate] BETWEEN " & Format(Me.SDate, "\#mm\/dd\/yyyy\#") & _
  20.                 " AND " & Format(Me.EDate, "\#mm\/dd\/yyyy\#")
  21.     Forms!FormName!SubformName.Form.Filter = strFilter
  22.     Forms!FormName!SubformName.Form.FilterOn = True
  23.     Forms!FormName!SubformName.Form!SubformControlName = Me!FormRevisedQTY.Value
  25. ExitSub:
  27. End Sub
Oct 7 '12 #16

Expert Mod 5K+
P: 5,285
try inserting: Forms!FormName!SubformName.Form.Requery after line 23 of the posted code.

You'll also find the following insight article (and the refered to cascade) to be of interest:
and the overall map is at the bottom of this page "Microsoft Access / VBA Insights Sitemap" scroll down there... TONS and TONS of information!
Oct 7 '12 #17

Post your reply

Sign in to post your reply or Sign up for a free account.