How to go to a specific record in listbox from another form

[bigalpha]

I have three forms: Form1 that contains subform1 and Form2 which is totally separate. Form1 contains a listbox that I use as a search form. When a record in the listbox is clicked, it populates Subform1 with more details.

If I am in Form2, I would like to be able to click a record which opens the listbox in Form1 to the same record (this will automatically update subform1 with the details).

 

[namliam]

research the "Openargs" part of the docmd.openforms method, see if that can help you

 

[Never Hide]

In Form2 on the click event of a Button whatever you use to "click a record" in that form

If CurrentProject.AllForms("Form1").IsLoaded Then
 DoCmd.Close acForm, "Form1"
 DoCmd.OpenForm "Form1", , , , , , "FilterForm1"
Else
 DoCmd.OpenForm "Form1", , , , , , "FilterForm1"
End If

"FilterForm1" will be passed in the "OpenArgs" of Form1 when the forms Loads. You can use whatever you want there, it's a String.

In Form1 on the Form_Load event

If OpenArgs="FilterForm1" Then
 Me.lstListbox=Forms!Form2!FilterFieldID
 Call lstListbox_Click
End If

"lstListbox" is the libox you use in Form1
"FilterFieldID" is the bound coulmn of that Listbox

You said "When a record in the listbox is clicked, it populates Subform1 with more details" so I guess you use the listbox's click event, that's why I call that event.

 

[bigalpha]

In Form2 on the click event of a Button whatever you use to "click a record" in that form

If CurrentProject.AllForms("Form1").IsLoaded Then
 DoCmd.Close acForm, "Form1"
 DoCmd.OpenForm "Form1", , , , , , "FilterForm1"
Else
 DoCmd.OpenForm "Form1", , , , , , "FilterForm1"
End If

"FilterForm1" will be passed in the "OpenArgs" of Form1 when the forms Loads. You can use whatever you want there, it's a String.

In Form1 on the Form_Load event

If OpenArgs="FilterForm1" Then
 Me.lstListbox=Forms!Form2!FilterFieldID
 Call lstListbox_Click
End If

"lstListbox" is the libox you use in Form1
"FilterFieldID" is the bound coulmn of that Listbox

You said "When a record in the listbox is clicked, it populates Subform1 with more details" so I guess you use the listbox's click event, that's why I call that event.

Thank you for the reply!

After fiddling with it and not getting it to work, I realized that "filterform1" is basically being used as a unique identifier for my Form2 and I'm not supposed to actually try to pass my record ID through it. Is there a reason why I don't pass my record ID through openargs and then reference it to my listbox?

On a slightly related note: can I have my listbox jump to the appropriate record as well? I know that there is an index property that allows me to jump to the first or last record but I can't figure out how to jump to a selected record programmatically.

Thanks so much for your help!

 

[Never Hide]

Hey bigalpha,
glad to hear you got it shorted out.
About the Listbox, when I tested

If OpenArgs="FilterForm1" Then
 Me.lstListbox=Forms!Form2!FilterFieldID
 Call lstListbox_Click
End If

On my Access it would select the appropriate record. What is your Access version?

 

[bigalpha]

Hey bigalpha,
glad to hear you got it shorted out.
About the Listbox, when I tested

If OpenArgs="FilterForm1" Then
 Me.lstListbox=Forms!Form2!FilterFieldID
 Call lstListbox_Click
End If

On my Access it would select the appropriate record. What is your Access version?

Well, it filters my subform appropriately, but I don't get the line that highlights the row in the listbox indicating which record it's on. that's what I was wondering about.

e: I'm on 2007 right now.

 

[Never Hide]

Try this

If OpenArgs="FilterForm1" Then
 Me.lstListbox=Forms!Form2!FilterFieldID
 Call lstListbox_Click
 Me.lstListbox=Forms!Form2!FilterFieldID
End If

I guess you have a Me.refresh or something similar in the lstListBox_Click event

 

[bigalpha]

Try this

If OpenArgs="FilterForm1" Then
 Me.lstListbox=Forms!Form2!FilterFieldID
 Call lstListbox_Click
 Me.lstListbox=Forms!Form2!FilterFieldID
End If

I guess you have a Me.refresh or something similar in the lstListBox_Click event

I tried putting a me.refresh in the code manually and nothing. Tried setting focus to the listbox then refresh, nothing.

This is what I have in my listbox_click event:

Me.frmInputWaste.Form.Filter = "[CurrentCYIDPK]= " & Forms!frmWasteSearch.SearchResults
Forms!frmWasteSearch.frmInputWaste.Form.FilterOn = True

Also showed up to work with a big surprise: we've been migrated to Office 2013.