Referring to a Listbox Record

[RKAccess]

All,

I am using MS Access 2000.

I have a Main Form with a Listbox and a Subform. The Subform has various fields that correspond to a value selected from the Listbox.

To make this more understandable, lets assume that I have a Table with Client names and the State where they reside.

The Listbox is based on a Query that list only the Clients residing in a particular State.

On_Open of the Main Form the Listbox is automatically populated with the filtered clients from the Query. When I Click on one of the Clients listed in the Listbox, the information displayed on the Subform is automatically populated with additional data associated with that particular Client.

O.K., now here's the question.

When I open the Main Form, the Listbox is automatically populated with the filtered data from the Query. However, the Subform indicates data based on the first record in the associated Table.

What I would like is for the Subform to automatically read the first record listed in the Listbox not the first record in the Table when the Main Form is opened. By doing so, I can be assured that the data listed in the Subform when the Main Form is opened is relevant to what I set the Query for.

Any help would be appreciated...

 

[pgk]

Hi
Try setting the Sub Form to open with the records relevant to the first item in the list box. I believe you will have some in the AfterUpdate or Click event of the list box to change the Sub Form contents to something relevant to the list box selection.

Try the following in the Form_Load of the main form:

Me.List1.Selected(0)= -1
Call List1_Click

I am assuming that you use the Click event of the list box to update your sub form. Put the code wherever it is appropriate. Hope it helps. Let me know what happens.
With regards,
PGK

 

[ShawnR]

I have this problem also and I tried the above suggestion but it didn't do anything. I put it in the load event on the main form.

 

[RKAccess]

PGK,

I tried your suggestion in both the On_Load and On_Open events of the Main Form. Nothing happened except for locking my database up. Thanks anyway. I really appreciate any help I can get.

I will provide some additional information in hopes that my programming problem can be resolved.

The Main Form has a Listbox that is automatically populated when the Main Form is opened. It is populated with the names of Clients who live in a particular State.

There is also a SubForm on the Main Form that has a Bound Textbox which is linked to the "Clients" Column of the Main Table.

Therefore, both the Listbox and a Textbox on the Subform are linked by the "Clients" name.

When I Click on one of the names in the Listbox the Textbox on the SubForm is updated with a particular Clients name. Other Bound Textboxes on the SubForm are also updated with information corresponding to the selected Client's Name.

This is all done on the After_Update event of the Listbox.

Everything works fine with the exception of the fact that the SubForm list the data for the Client listed on the first row of the Main Table when the Main Form is opened not for the first row of the Listbox. I have to click on a name in the Listbox before the SubForm indicates one of the names that I Queryed for in the Listbox.

I'm just trying to get the Client Name listed in the Subform to match the Client Name listed on the first row of the Listbox when the Main Form is opened.

Any other suggestions would be greatly appreciated...