Requery Subform

[ElizabethLOng]

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.
Elizabeth

[Seth Schrock]

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):

Me.frmSubForm.Requery

[ElizabethLOng]

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

[TheSmileyCoder]

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.

[Seth Schrock]

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?

[ElizabethLOng]

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
.....so 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

[Seth Schrock]

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?

[lyodmichael]

can you show us your code?

[ElizabethLOng]

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

Private Sub Image87_Click()
With Me.subformname.Form
    .Filter = "[DDate] BETWEEN " & Format(Me.LastOfRevSDate, "\#mm\/dd\/yyyy\#") & " AND " & Format(Me.LastOfRevEDate, "\#mm\/dd\/yyyy\#")
    .FilterOn = True
End With
End Sub

[Seth Schrock]

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?

[ElizabethLOng]

Want to replace just the records selected in the subformm based on the revised quantity in the main form. Tank you and regards EOL

[Seth Schrock]

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?

[ElizabethLOng]

I filter the records based on date range using the above code.

[Seth Schrock]

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?

[zmbd]

Seth, ElizabethLOng;
I've been following this for a little while now and now I'm wonder if we have a mis-communication.

EL:
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?

[ElizabethLOng]

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.

Private Sub Image87_Click()
    Dim strFilter As Variant
    Dim strSDate As String
    strSDate = Me.SDate
    Dim strEDate As String
    strEDate = Me.EDate
 
    'check sdate
    If Not IsDate(Me.[SDate]) Or Not IsDate(Me.[EDate]) Then
        MsgBox "Valid dates must be entered"
        Exit Sub
    End If
    If Me.[EDate] < Me.[SDate] Then
        MsgBox "End date must be later than start date"
        Exit Sub
    End If
 
    'if both sdate and edate are valid, run filter
    strFilter = "[sfmDDate] BETWEEN " & Format(Me.SDate, "\#mm\/dd\/yyyy\#") & _
                " AND " & Format(Me.EDate, "\#mm\/dd\/yyyy\#")
    Forms!FormName!SubformName.Form.Filter = strFilter
    Forms!FormName!SubformName.Form.FilterOn = True
    Forms!FormName!SubformName.Form!SubformControlName = Me!FormRevisedQTY.Value
 
ExitSub:
 
End Sub
 

[zmbd]

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: http://bytes.com/topic/access/insigh...filtering-form
... http://bytes.com/topic/access/insigh...mbo-list-boxes
and the overall map is at the bottom of this page "Microsoft Access / VBA Insights Sitemap" scroll down there... TONS and TONS of information!