Viewing chosen record from list box

[dkirk02]

I have a list box which uses a parameter query to bring back all matching results of the search. ie.."Search by last name" would bring back all cases with Smith as last name and display them in the list box. How can I then select the particular case I would like to view and bring it up in edit mode in a form? Any help would be appreciated.

 

[Angello Pimental]

To answer your question :

The following code will open up a form based on the record a user selects in the list box.

Private Sub listbox_Click()
On Error GoTo Err_listbox_Click

Dim stDocName As String
Dim rst As Recordset, strCriteria As String
stDocName = "editform"

strCriteria = "[field]=" & "'" & Me![listbox] & "'"
DoCmd.OpenForm stDocName

Set rst = Forms!editform.RecordsetClone
rst.FindFirst strCriteria
Forms!editform.Bookmark = rst.Bookmark
Me!listbox = ""

Exit_listbox_Click:
Exit Sub

Err_listbox_Click:
MsgBox Err.Description
Resume Exit_listbox_Click

End Sub

**Note**

*Substitute listbox for the name of your listbox
*Substitute editform for the name of the form that will contain the particular case you wish to view and edit
*Substitute field for the name of the field which you have done your search by. i.e. lastname

This code should be inserted into the on_click properties of your list box

Give that a go, if you have any problems just ask

HTH
Angelo

 

[dkirk02]

Thank you so much for your prompt reply!
I put this code in the code builder on the on click for the form. It is not working for me. Is there something I'm missing? Have I put the code in the correct place?

I am brand new to coding. This is my first attempt. Woohoo!

 

[charityg]

Put the code in the onclick or dblclick event of the listbox. That way when the user clicks the desired record from the listbox, the code will be executed.

 

[dkirk02]

I have placed the following code on the dbl click of the list box, and it will not pull up the record double clicked. it continues to simply pull the form up in edit mode starting with the first record. Any ideas?

Private Sub List0_DblClick(Cancel As Integer)
On Error GoTo Err_listbox_Click

Dim stDocName As String
Dim rst As Recordset, strCriteria As String
stDocName = "Edit Case Info"

strCriteria = "[Assigned To]=" & "'" & Me![List0] & "'"
DoCmd.OpenForm stDocName

Set rst = Forms![Edit Case Info].RecordsetClone
rst.FindFirst strCriteria
Forms![Edit Case Info].Bookmark = rst.Bookmark
Me![List0] = ""

Exit_listbox_Click:
Exit Sub

Err_listbox_Click:
MsgBox Err.Description
Resume Exit_listbox_Click

End Sub

 

[charityg]

Normally when I am searching for specific records to open, I use the record's ID. You should include the record ID as a hidden column in the listbox (if you haven't done so already). Then change your code to something like

Private Sub List0_DblClick(Cancel As Integer)
On Error GoTo Err_listbox_Click

Dim stDocName As String
Dim rst As Recordset, strCriteria As String
stDocName = "Edit Case Info"

strCriteria = "[ID]=" & Me![List0].column(0)
'the column number is whatever the ID column position is minus 1
DoCmd.OpenForm stDocName

Set rst = Forms![Edit Case Info].RecordsetClone
rst.FindFirst strCriteria
Forms![Edit Case Info].Bookmark = rst.Bookmark
Me![List0] = ""

Exit_listbox_Click:
Exit Sub

Err_listbox_Click:
MsgBox Err.Description
Resume Exit_listbox_Click

End Sub


After a specific record is selected from the listbox, you don't want to use a generalized criteria. I'm assuming that more than one record can be assigned to the same person.

Let me know how this works

Charity

 

[dkirk02]

Charity,
First of all, thanks so much!

I wish I knew more about code so that I could help you to help me...haha With time, right?
Anyway, is this kind of search limited to only unique values such as a case id would be?
What I am really looking for is a way to conduct a search by any piece of information in a customer's account. ie...phone number, address, user id of person who entered information, etc.
Am I on the right track with this code, or is ther something different I need to be doing?

 

[charityg]

If the list box is set up correctly, your search should be fine!
Is the listbox returning the expected values?

I thought the issue was upon selecting a specific record from the list to view. Am I correct?

If so, you need to identify the specific record you want to view, hence the record ID (primary key)

~Charity

 

[dkirk02]

With some help from a colleague, this has worked. Thanks again! You have always been great about responding quickly and with great information. Rock on!