Passing args: One form to subform of opened form 1

[bgv]

I want to pass a date from a calendar-based form to a subform of a master form:

e.g.
calendar form --> activity form --> activity subform

I am already passing the OpenArgs variable from the calendar form in the OpenForm command to the activity form. How do I get the activity form to pass that variable to the activity subform? I want that form to only display records pertaining to the date selected in the calendar form.

 

[KenReay]

HI

To do that you do not need to pass teh date value to the subform, just have the date in a (hidden) control on the main form, and include that control as (one of) the link fields for the subform control

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[hwkranger]

KenReay's right, (of course) but to specifically answer your question, you can pass arguments onto the next form that were previously passed by passing me.openargs in the docmd.openform statement that opens your next form.

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[bgv]

After several attempts, I do not seem to be able to obtain a value from a hidden field in its parent form. I have tried various formats for getting the value from the field by different calls:

Forms!frmDailyActivities!txtStartDate
Forms("frmDailyActivities&quot!txtStartDate
[Forms]![frmDailyActivities]![txtStartDate]

What I am trying to do is only display activity records from the activity table that occurred on the date selected by the initial calendar form.

I have made the txtStartDate field visible and the field displays the date passed from the ActiveXCalendar object, but I cannot seem to even get a MsgBox to display the value in the OnLoad event of the subform. At first the subform was unlinked, so I linked it to the parent form and that made no difference.

I have tried setting it as a filter in the OnLoad event of the subform with no results. Since I am getting a null from the field, the filter passes no records at all; got an empty sack!!

What gives? What is the difference between the three calls to the field as shown above? Maybe I do not understand that very well.....

 

[KenReay]

Hi

You should not need to reference the field from the sub form to achieve your result, as I said in my first post, use the master/child link fields property of the SubForm CONTROL, to achieve this 'automatically', so say you combo box to select teh date is caleld cboDate, and the date column in the query which is the source of teh sub form is datDate (imaginitive aren't I?), then master link field is cboDate and child link field is datDate, Access will do the rest

I you did want to refer to teh combo box cboDate from the subform, you just say parent.cbodate

Hope this helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

I have an example of this if you want it - EMail me kenneth.reayREMOVETHIS@talk21.com

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bgv]

Now that this is solved, let me add another nuance that will clean up the page and make it not so confusing...

What if I wanted to use a date picker in the master form to represent a filter for the begin_date and end_date fields in the subform where the date picker selected records BETWEEN the begin_date and end_date? How do you set up the master and child query relationships via a database query in a VBA script rather than in the Properties window?

 

[KenReay]

Hi

Not sure I understand what you are asking

"SELECT ..etc .. WHERE datDateColumn BETWEEN #" & Format(txtFromDate,"mm/dd/yy&quot & " # AND #" & Format(txtToDate,"mm/dd/yy&quot & "#;"

is that what you need to know ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bgv]

Well, almost. How do you you write the code block to set up the LinkMaster and LinkChild relationships using a SELECT statement from a table? Do you use a Filter call on the subform within a Subform_Enter block?

 

[KenReay]

HI

Sorry, do not understand the question

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bgv]

Lets restate it another way. How do you set up a relationship between a date field in a Master form and a start_date and end_date in a Subform? I want to input a date into the Master date field and have the Subform respond with records that are between the start_date and end_date. For example, if I have records in the activity table of:

activity_id start_date end_date
1 10/8/2003 10/20/2003
2 10/15/2003 10/18/2003
3 10/17/2003 10/17/2003

If I just perform a direct link between the Master form and the Subform using the Subform Control properties where masterform.theDate = subform.start_date, and I select a date of 10/17/2003, it will ONLY display activity_id = 3.

I want to set up the link between the Master form date field and the Subform date fields to display all records where the date chosen is BETWEEN the start_date and end_date. That way activity_id records 1, 2, and 3 would be displayed in the subform if the date chosen was 10/17/2003. I am not sure how to set up the linking in this manner.

Does that make my request seem less confusing?

 

[KenReay]

Hi

WE seem to be going in circles here

Assuming you have two controls on main form (frmDAilyActivies) with name txtStartDate and txtEndDate

Make the subform record source a query with criteria of

Between Forms!frmDailyActivities!txtStartDate AND Forms!frmDailyActivities!txtEndDate

I cannot specify which column in the query would have this criteria, because I do not think you have mentioned this

In the after Update event of the controls txtStartDate and txtEndDate, put MySubFormControl.Requery, where MySubFormControl is the name of the subform CONTROL (not the the sub form!)

If you are using a pop up calendar type form to populate the date controls, this may not fie the after update eevnt (I cannot be sure there are numerous different ways of doing popup calendar), in which case you may also need to include a requery in the code sequence which update the date controls

OK?



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK