How to select a record from a list box

[Ryno Bower]

Hi,

I hope someone can help me. I have a form where I add data. I also have a search button on there that opens another form (listbox) showing all the records. If I want to edit a record, I want to be able to double click on a specific record in the listbox, and then bring me back to the main form and go to the record i just chose in the list box.

Please help!!!!!

Thanking you in advance,

Ryno

[Steven Kogan]

There is a peculiarity with the double-click event where the focus returns to the original form when you open another form. To solve that, set cancel= true.

This sort of code would work:

Private Sub List0_DblClick(Cancel As Integer)
    Cancel = True
    DoCmd.Close acForm, "Form1"
    DoCmd.OpenForm "Form1", acNormal, , "[ID]=" & Me.List0
End Sub
 

[Ryno Bower]

Hi Steven,

I tried your code, but it brings me back to the form to add a new record, it does not choose the record I chose from the listbox. Any other suggestions?

[colintis]

Ryno,

Please take notice the example code, it closes Form1 and then open Form1 again. This is actually an example for your reference, not an exact answer to your question. Try work around with and see if it works, you might also need to find a way to accept the parameter in the second form.

[Steven Kogan]

The last parameter, "[ID]=" & Me.List0, is the where clause. For this to work ID would be your key field, and the first column in your list box would be the key field. It should filter the records matching the criteria, which may not be what you want to do. If you close the form first it should clear any pre-existing filters.

More generally you would need a way to identify the record, preferably uniquely but not necessarily.

Can you provide specifics such as the form name, field name, and how you would identify the record to be selected?

DoCmd.OpenForm "Form1", acNormal, , "[ID]=" & Me.List0