General discussion


Querying Subforms in a form/Access 97

By SixFourtyKilo ·
BRIEF: I would like to view reservations made daily, weekly or monthly in a calendar format.
QUICK INFO: I am having trouble creating a query that will auto-update subforms on a form without opening additional forms or dialog boxes to gain the query information. I would like to be able to enter a date, press a button and then have the table in the subform auto query or filter according to the date.
MORE INFO: I am modifying the auto-wizard, "Resource Scheduling" I would like to use the "Reservations" form with an Active-X calendar and/or an input mask that accepts a date and/or date range that will auto-update and query the adjoining subforms (Reservations Subform and Reservations Time Subform).

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Querying Subforms in a form/Access 97

by Bob Sellman In reply to Querying Subforms in a fo ...

I assume you have a form/subforms format where the date of interest is selected on the main form and the subforms display the associated information.

On the control where the user selects the date, set the property "After update" to requery each of the subform controls. The code would read something like this, where sfrm1 is the name of the subform control on the main form:

You may need to use recalc instead of requery.

The subforms should be based on queries where the date field in the record source has a criteria something like
>= forms!frmMain.cboUserDate AND <= (forms!frmMain.cboUserDate + 6)

This format assumes the query driving the subform shows records for a week. Change the number 6 to whatever is appropriate for the time period needed.

Open the main form and select a date, then run each query to be sure the desired records are selected. Once they are working correctly the requery code should work fine.

You may consider making the user selected date control a combo box that is based on a grouping query that shows only dates for which reservations were actually made. Sort it in reverse order so the most recent is listed first. Build the query from your reservations table, listing only the reservation date (and perhaps also making sure a cancelled column is false, if you have something like that). Group by the date and set sorting to descending. You might also limit the date to be greater than or equal to the current date [Date()], or perhaps the current date less a week or month (to show recent activity).

If you want the user to select one date or a date range (say controls named cboDate1 and cboDate2), then the on update for cboDate1 would check cboDate2 and if it is blank, place the value of cboDate1 in it and run the queries. Do similar checks on cboDate2.

Hope this helps.

Collapse -

Querying Subforms in a form/Access 97

by SixFourtyKilo In reply to Querying Subforms in a fo ...

Thank you for your answer, but it seemed to have gone in the wrong direction. I ended up having a caption that would grab its info from an ActiveX control (the date) and then the query would use the caption as a variable for defining a new query every time the ActiveX control is updated. Thank you anyway!

Collapse -

Querying Subforms in a form/Access 97

by SixFourtyKilo In reply to Querying Subforms in a fo ...

This question was closed by the author

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums