Listbox results from selected submenu record

[Ruegen]

Probably a common question



I have a submenu with a list of records in continuous form.

I have a listbox of records with the bookings of each school.

I need to be able to click the submenu record and have it show the bookings for that school in the listbox.

Because I have made a search box I can't have the listbox show all the records when the main form loads - I need it to show nothing until a record from the submenu is clicked.....

[Ruegen]

Note: the main form does not have a record source.

I have exchanged the listbox for another subform.

I'm guessing one needs to edit the record source to be of whatever the first subform (subform 1) is selected and that needs to be typed in the query sql however i am not sure how.

[amrut]

1. Using listbox - Create a textbox on main form say txtLinkBox. Set its controlsource to the schoolname from subform. It should look like "=[YoursubformControl].[Form]![Schoolfieldcontrol]. Modify the SQL of listbox to include the txtLinkbox as criteria.In the form's current event, you have to requery the listbox.
2.Using another sub form- Set the SQL for listbox as source for the 2nd subform. Drag this subform to main form. In the 2nd subforms's Property sheet>Data>Link Master fields & Link Child fields, select the txtLinkbox as Master field and the schoolcontrolfield in 2nd subform as child field. Now whenever a record is selected on 1st subform, the corrosponding records will be shown by 2nd sub form.

After testing, hide this text box.

[Ruegen]

1. Using listbox - Create a textbox on main form say txtLinkBox. Set its controlsource to the schoolname from subform. It should look like "=[YoursubformControl].[Form]![Schoolfieldcontrol]. Modify the SQL of listbox to include the txtLinkbox as criteria.In the form's current event, you have to requery the listbox.
2.Using another sub form- Set the SQL for listbox as source for the 2nd subform. Drag this subform to main form. In the 2nd subforms's Property sheet>Data>Link Master fields & Link Child fields, select the txtLinkbox as Master field and the schoolcontrolfield in 2nd subform as child field. Now whenever a record is selected on 1st subform, the corrosponding records will be shown by 2nd sub form.

After testing, hide this text box.

why would you need two subforms? why not just one subform and one listbox and one invisible textbox?

Also the main form has no record source...

[Ruegen]

Basically you click a school and it opens their booking in the listbox.

[amrut]

Note: the main form does not have a record source.

I have exchanged the listbox for another subform.

I'm guessing one needs to edit the record source to be of whatever the first subform (subform 1) is selected and that needs to be typed in the query sql however i am not sure how.

I have provided two ways to accomplish the task - one using list box, another using sub-form. Choice is yours.

[Ruegen]

I have provided two ways to accomplish the task - one using list box, another using sub-form. Choice is yours.

oh nice.

I think I will try first.

[Ruegen]

The hidden textbox changes to each id which is great, the listbox does not refresh with the criteria after the change has been made...

[amrut]

Try in the subform's current event Me.Parent!Listboxname.requery

[Ruegen]

ah. Works.

many thanks