Many to one form design

[DrDAQ]

I have an events table with event_id, event_type, date, & narrative.

I have a form with 1 subform in "Continuous Forms" view showing the
event_type and Date and a textbox bound to event_id. There is a 2nd subform
in "Single Form" view showing the narrative (memo) whose Link Master field is
the textbox. This approach works will but the narrative refresh is sluggish
as I move through the events.

Is there a better way to do this?

 

[Allen Browne]

The simplest way to solve this would be to move up to Access 2007. The new
version has a split form view that does exactly what you want.

In A2003, it is possible to program this more efficiently with a form that
has a list box on the left where you select the event you want to see. The
form is in Form view, and its RecordSource is:
SELECT * FROM Events WHERE (False);
so it loads no records (i.e. shows only the new record.) Then in the
AfterUpdate event procedure of the list box, you change the RecordSource of
the form so it loads only the one record in the list box, e.g.:
Me.RecordSource ="SELECT * FROM Events WHERE EventID = " & Me.lst1 & ";"

This works instantaneously, provided you are not trying to load tens of
thousands of events into the list box. If this is likely, you need to
provide further filters so it shows only a particular date period (e.g.
initializing to the last 3 months.)