Use VBA to get a Bound Column from list box

[tl mike]

I am trying to set up a form in which I am able to dbl click on a product in a list box and it will take me to that product in a form associated to its product line.

This is what I have so far:

Dim stFrmName As String
Dim rs As Object

Set rs = stFrmName.Recordset.Clone
rs.FindFirst "PartNumberPK = " & Me.List30
stFrmName.Bookmark = rs.Bookmark


stFrmName = Forms!frmProductIndex!List30
DoCmd.OpenForm stFrmName

I get a compile error for the use of stFrmName for:

Set rs = stFrmName.Recordset.Clone

 

[pbaldy]

I'm not sure about the design, but try

  Dim stFrmName     As String
  'Dim rs As Object

  '    Set rs = stFrmName.Recordset.Clone
  '    rs.FindFirst "PartNumberPK = " & Me.List30
  '    stFrmName.Bookmark = rs.Bookmark


  stFrmName = Forms!frmProductIndex!List30
  DoCmd.OpenForm stFrmName

 

[tl mike]

Thanks for the reply but I am also trying to get it to open to the specific product that was selected which is where I am stuck. Getting it to open the correct form and then go to the the record selected.

 

[SOS]

Change your code to this:

Private Sub List30_DblClick(Cancel As Integer)
On Error GoTo Err_List30_DblClick
Dim stFrmName As String
Dim rs As Object
    stFrmName = Me.List30
    
    
    stFrmName = Forms!frmProductIndex!List30
    DoCmd.OpenForm stFrmName
    Set rs = Forms(stFrmName).Recordset.Clone
    rs.FindFirst "[PartNumberPK] = " & Me.List30.Column(0)
    Forms(stFrmName).Bookmark = rs.Bookmark
    
Exit_List30_DblClick:
    Exit Sub
Err_List30_DblClick:
    MsgBox Err.Description, , " GV Health"
    Resume Exit_List30_DblClick
End Sub

 

[tl mike]

That is it!!

Works perfectly

Thank you

 

[SOS]

That is it!!

Works perfectly

Thank you

No problema