Main Form & Subform Between Two Dates - Problem

[aamer]

Hi,

Am trying to make a form with a subform in data view.
on the Main Form I have placed 2 combo boxes for dates i.e START DATE & END DATE
I am unable to connect these two combo boxes with the subform.
A help / guidence is needed to fix this problem.

A Sample DB is attached to better understanding of the problem.

Your help is appreciated.
thx
aamer

Sample DB.zip

[June7]

I can't download db now. What have you tried? These are unbound comboboxes for selection of filter criteria?

Must understand that subform loads before main form - I know that sounds weird but it is the way it is. This means the controls on main form are not immediately available to the subform when it is loaded. Is the main form bound to data? If not, perhaps consider the subform as standalone in Continuous view with controls arranged to look like datasheet. This will allow the comboboxes to be on the same form as the data.

[cbende2]

Ok, So what I did was in the subform query, I added:

Between [cboDateFrom] And [cboDateTo]

And then I simply added a refresh button to the main form. After selecting a new date range, if you click the refresh button, it refilters your subform.

Sample DB.zip

Or....

Instead of using a command button to refresh the form, in the [cboDateTo].AfterUpdate event, you can add a refresh command. This will get rid of the button.

[aamer]

Thank you cbende2
This is exactly what I was looking for, I could not have better understood without your help.
Now I wanted to fix this on the main project but I encountered another error.

The Form "BillsTransForm" With a Subform "BillsTransSubform" is actually will be placed in "BillsTranDlog" as a Subform along with another form and subform.
Now when I do that I am asked to enter the parameter value. But If I open "BillsTransForm" I get no errors.
I Think I have to change the values in the Querry "ComTransSubformQuerry"
Currently the criteria under "TransactionDate" is as under:

Between [Forms]![ComTransForm]![cboDtFrm] And [Forms]![ComTransForm]![cboDtToFrm]

What Changes should I do to make it work.
I have again attached a sample, So If I am unable to explain properly you can check problem from the New Sample DB.
The Problem is In "BillsTranDlog" Form.

New Sample DB.zip

[aamer]

I Tried the following but even this is not working

Between [Forms]![BillsTranDlog]![Forms]![ComTransForm]![cboDtFrm] And [Forms]![BillsTranDlog]![Forms]![ComTransForm]![cboDtToFrm]

[cbende2]

Aamer, I won't be able to help you with this until tomorrow. I don't have my pc on me and I won't be able to take a look at it until tomorrow.

[ItsMe]

Maybe it is a simple syntax issue.
[Forms]![BillsTranDlog]![Forms]![ComTransForm]![cboDtFrm]

If the main form name is BillsTranDlog and the container for the Subform is named ComTransForm and the combo control is named cboDtFrm

Then this should work ...
[Forms]![BillsTranDlog]![ComTransForm].[Form].[cboDtFrm]

[June7]

Note that Query has only one 'r' but you use two in your query names.

Why is the Sales table included in the RecordSource for both subforms?

Why use the BillsTranDlog form? It is not bound and has no other controls on it. It just seems to complicate things without providing any real benefit.

If have never used dynamic parameterized queries. I prefer VBA to set filter criteria. Review http://www.allenbrowne.com/ser-62.html

Suggest BillsTransSubform could be a standalone form in Continuous view with controls arranged like datasheet and the search boxes and buttons in its header section. However, I have used a form/subform arrangement with controls in UNBOUND main form to input filter criteria for subform in Datasheet view. But I still use VBA to set Filter and FilterOn properties.

[aamer]

its not working. I tried the following also

Between [Forms]![BillsTranDlog]![ComTransForm].[Forms].[cboDateFrom] And [Forms]![BillsTranDlog]![ComTransForm].[Forms].[cboDateTo]

[ItsMe]

I did not look at your Database. I only looked at the syntax in post #5. June has mentioned some things in post #8 you should take into consideration. As for the syntax in post #9, it is not correct.
[Forms]![BillsTranDlog]![ComTransForm].[Forms].[cboDateFrom]

There is no "s" in "Form".


The first "Forms" gets an "s". You begin your statement with "Forms" because you are referring to the "Forms Collection". The second time you are referring to an object type Form [ComTransForm].[Form].

[aamer]

Thank you cbende2 for your help and guidance.