Listbox Record Selection and Open New Form

[number2]

I am trying to get a Listbox to open a diferent Form to the record selected by the listbox.

Here is the code I have to select the record on the Afterupdate:

Private Sub List6_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[number] = " & Str(Nz(Me![List6], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

How can I get the listbox to open a different Form to the record selected by the Listbox?

Thanks!

 

[afryer]

Hi,

I am probably reading this completely incorrectly, but as I understand it you want to open a new form, based on the record selected in the list box?

If this is the case then you simply need to capture the _Click() event with the following code:

Sub List6_Click ()
    dim l_value as string

    l_value = me.List6
    docmd.openform "otherForm", , , , , l_value

end Sub

In the form you have opened you can extract the value with the following

l_value = me.openargs

If I have misunderstood the question then could you please explain in more detail what it is you require.

Cheers

Andrew

 

[number2]

Thanks for the reply. Half way there! I want to open the form to the record selected in the Listbox.

An additional problem is that I have an additional record selector Listbox on the form I am launching. The second Form AND its Listbox must also update to the record selected by the first Form Listbox. (hard to explain, easy to visualize!)

 

[afryer]

Hi,

I think I understand where you are coming from:

- You open the second form using the value selected in list box
- The second form must have it's record source based on the first.
- The list box on the second form must show the value selected in the first form.

To do this you can set the record source of the second form to be the following:

Select * from [MY_TABLE] where [MY_FIELD] = forms ("frm_first").Column (0)

Similarly the list box can be set by setting it's default to be forms("frm_first").Column(0)

I am assuming column 0 is the key field that links the two forms, but if it is different then obviously change it as required.

Andrew