Open Form based on Listbox

[alex]

Hello,

I have a listbox (bound column is ID field based on table) on a popup
form. The listbox has about 10 selections.

What's the best way to open a particular form (different for each
selection) based on a selection off the list? Once the selection is
made, I want to store the value of the selection (ID field) within the
newly opened form.

thanks for any help,
alex

 

[Arvin Meyer [MVP]]

It seems to me that if you have 10 different forms, your best solution would
be to build a table with the forms, and use the SelectionID as the Key. If
the form's name is not what you want the users to see, just add a zero (0")
width column to the listbox.

So now you can open the form and pass the SelectionID to the form opened,
although you shouldn't need to, unless all 10 forms are based on the same
table. The table will look like:

SelectionID - Long Integer - PK
Selection - Text
FormName - Text and the open code would look like:

Sub lstWhatever_Click()
DoCmd.OpenForm lstWhatever.Column(2), , , , , , lstWhatever
End Sub

the lstWhatever is the OpenArgs argument which you will use in the GotFocus
event of the SelectionID textbox in your new form. txtSelectionID is the
name of that textbox, and it is the first control in your tab order:

Private Sub txtSelectionID_GotFocus()
On Error Resume Next
If Me.NewRecord = True Then
Me.txtSelectionID = Me.OpenArgs
End If
End Sub

 

[alex]

It seems to me that if you have 10 different forms, your best solution would
be to build a table with the forms, and use the SelectionID as the Key. If
the form's name is not what you want the users to see, just add a zero (0")
width column to the listbox.

So now you can open the form and pass the SelectionID to the form opened,
although you shouldn't need to, unless all 10 forms are based on the same
table. The table will look like:

SelectionID - Long Integer - PK
Selection - Text
FormName - Text and the open code would look like:

Sub lstWhatever_Click()
    DoCmd.OpenForm lstWhatever.Column(2), , , , , , lstWhatever
End Sub

the lstWhatever is the OpenArgs argument which you will use in the GotFocus
event of the SelectionID textbox in your new form. txtSelectionID is the
name of that textbox, and it is the first control in your tab order:

Private Sub txtSelectionID_GotFocus()
On Error Resume Next
    If Me.NewRecord = True Then
        Me.txtSelectionID = Me.OpenArgs
    End If
End Sub
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com









- Show quoted text -

Thanks Arvin. I'll take your advice and apply it to my db.