Referencing a listbox on another form question

[magster06]

Hello all,

Yet another question of many to come, lol.

I have a listboxA on formA and a listboxB on formB. When the user clicks an item in listboxA and then presses the view record I want formB to open and the item selected in listboxA should select the same record in listboxB.

This is the code I have:

Private Sub cmdSearchSelect_Click()
'opens form to record selected in listbox
    On Error GoTo Err_cmdSearchSelect_Click
    Dim strDocName As String
    Dim strLinkCriteria As String
 
    strDocName = "CWRDefinitions"
    
    strLinkCriteria = "lstCWR.Column(0) ='" & Me![SearchResults].Column(0) & "'"
    DoCmd.OpenForm strDocName, , , strLinkCriteria
    
Exit_cmdSearchSelect_Click:
    Exit Sub
Err_cmdSearchSelect_Click:
    MsgBox Err.Description
    Resume Exit_cmdSearchSelect_Click
End Sub

This code works great from listboxA to formB for textboxes, but I cant get it to select any item in lstboxB. I have tried various combinations, but to no avail.

 

[Solo712]

Hello all,

Yet another question of many to come, lol.

I have a listboxA on formA and a listboxB on formB. When the user clicks an item in listboxA and then presses the view record I want formB to open and the item selected in listboxA should select the same record in listboxB.

This is the code I have:

Private Sub cmdSearchSelect_Click()
'opens form to record selected in listbox
    On Error GoTo Err_cmdSearchSelect_Click
    Dim strDocName As String
    Dim strLinkCriteria As String
 
    strDocName = "CWRDefinitions"
 
    strLinkCriteria = "lstCWR.Column(0) ='" & Me![SearchResults].Column(0) & "'"
    DoCmd.OpenForm strDocName, , , strLinkCriteria
 
Exit_cmdSearchSelect_Click:
    Exit Sub
Err_cmdSearchSelect_Click:
    MsgBox Err.Description
    Resume Exit_cmdSearchSelect_Click
End Sub

This code works great from listboxA to formB for textboxes, but I cant get it to select any item in lstboxB. I have tried various combinations, but to no avail.

You can pass the result to form B as an OpenArgs argument.

DoCmd.OpenForm strDocName, , , strLinkCriteria, , , Me![SearchResults].Column(0)

Look at the link's example how to retrieve the OpenArgs value in the Load event for the form B.

Best,
Jiri

 

[magster06]

Jiri,

I have the code working part way thanks to you. I appreciate the help.

This is what I have now. When I click the "View Record" Button on formA, it opens formB and the record selected in formA listboxA is now selelcted in formB listboxB.

Private Sub cmdSearchSelect_Click()
'opens form to record selected in listbox
    On Error GoTo Err_cmdSearchSelect_Click
    Dim strDocName As String
    Dim strLinkCriteria As String
    strDocName = "CWRDefinitions"
    
    DoCmd.OpenForm strDocName, , , , , , "lstCWR|" & Me.SearchResults.Column(0)
    
Exit_cmdSearchSelect_Click:
    Exit Sub
Err_cmdSearchSelect_Click:
    MsgBox Err.Description
    Resume Exit_cmdSearchSelect_Click
End Sub

This is the code that went into the form load event of formB:

Private Sub Form_Load()
    'Use this version if the ID is text
  Dim x As Variant
  Dim strControl As String
  Dim strID As String
  'If parameters exist, use them
  If Len(Me.OpenArgs) > 0 Then
    'Split creates a zero-based array from the input string
    x = Split(Me.OpenArgs, "|")
    strControl = x(0)
    strID = x(1)
    Me(strControl) = strID
  End If
   
End Sub

My question now is this: how do I get the subform to reflect the selection on form load?

I currently have the code of the lstboxB to populate subformB in the afterupdate event. I tried requery in different locations, but nothing seems to work.

 

[Solo712]

Jiri,

I have the code working part way thanks to you. I appreciate the help.

This is what I have now. When I click the "View Record" Button on formA, it opens formB and the record selected in formA listboxA is now selelcted in formB listboxB.

Private Sub cmdSearchSelect_Click()
'opens form to record selected in listbox
    On Error GoTo Err_cmdSearchSelect_Click
    Dim strDocName As String
    Dim strLinkCriteria As String
    strDocName = "CWRDefinitions"
 
    DoCmd.OpenForm strDocName, , , , , , "lstCWR|" & Me.SearchResults.Column(0)
 
Exit_cmdSearchSelect_Click:
    Exit Sub
Err_cmdSearchSelect_Click:
    MsgBox Err.Description
    Resume Exit_cmdSearchSelect_Click
End Sub

This is the code that went into the form load event of formB:

Private Sub Form_Load()
    'Use this version if the ID is text
  Dim x As Variant
  Dim strControl As String
  Dim strID As String
  'If parameters exist, use them
  If Len(Me.OpenArgs) > 0 Then
    'Split creates a zero-based array from the input string
    x = Split(Me.OpenArgs, "|")
    strControl = x(0)
    strID = x(1)
    Me(strControl) = strID
  End If
 
End Sub

My question now is this: how do I get the subform to reflect the selection on form load?

I currently have the code of the lstboxB to populate subformB in the afterupdate event. I tried requery in different locations, but nothing seems to work.

Ok, this is fast becoming muddy. In the original post you you had form B open with the record selected on form A, via listbox A. Additionally, you wanted a listbox B on form B display the same selection as the item on listbox A. There was nothing in your request about handling subforms.
I have no idea what you are talking about.

Best,
Jiri

 

[magster06]

Jiri,

Sorry for the confusion. I am not the best at trying to write what I am trying to accomplish.

When the user clicks an item in listboxA and then presses the view record I want formB to open and the item selected in listboxA should select the same record in listboxB.

This is what I originally wanted and you have help me accomplish this, thank you!

Now on the second form (formB) with the second listbox (listboxB), I have a subform that was populated by listboxB.

What I had hoped that would happen was that when formB opened and a value was selected in listboxB, then this would popuate the subform.

 

[magster06]

Maybe I should just use a simple form with a textbox to populate the selection from formA to make things easy.

I think I make things a little too complicated at times or better yet, I could just place the textbox below the search box

 

[Solo712]

Maybe I should just use a simple form with a textbox to populate the selection from formA to make things easy.

I think I make things a little too complicated at times or better yet, I could just place the textbox below the search box

Yeah, I think it would be better just to have the verbiage window (as a form) pop up in over form A (without the list box), using the WHERE argument of the Open Form as you did before.

Best,
Jiri

 

[magster06]

Thanks again for the quick help Jiri!

 

[Solo712]

Thanks again for the quick help Jiri!

You are welcome, magster !