Select Record from List Box

[kit_sune]

Hello, I have a form that allows me to change the information for a list of personnel. On this form I have a list box that displays all the personnel in the list. There should never be more than 12-15 people in this list so it's an effective way of selecting records, however I can't figure out how to make the form select the record that is tied to that person from the list.

In VBA I've tried creating an "On Click" command for the list that, when a name is selected, is brings up the corresponding record. I thought I could use a DoCmd.FindRecord but I guess I don't quite know what I'm doing. I didn't want to use SQL to limit the records to just the one that matched I wanted to keep it simple if possible. Is this possible, and what am I doing wrong?

Private Sub Personnel_List_Click()
DoCmd.FindRecord Personnel_List, , True, , True
End Sub

The Personnel_List box is tied to the Personnel_Table, and the table only displays the names available.

Thanks,
~Kit

 

[pr2-eugin]

Clarify this again, the ListBox is on the same form bound form that holds the details? Have you considered having a combo box instead? The solution is readily available @ http://support.microsoft.com/kb/287658

You can use it with ListBox, I am not saying you cannot. Having a combo box is a lot more neater.

 

[kit_sune]

Sorry, I didn't mention it. The list box is an unbound object that uses the same table that the form is using. I want to use a list rather than a combo box for aesthetic reasons, while also reducing the number of needed clicks by one.

Once the name is clicked, the rest of the form should fill out with the data that relates to that individual, showing their name, email address, the section they belong to, etc… all this data is in the Personnel_Table. Each name will always be unique.


I attempted to recreate the first example in the link you provided and I think I’m close, but I get a “Type Mismatch” error when I run it. I don’t know what it thinks is wrong since all the values are strings…

[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Private Sub Personnel_List_AfterUpdate()[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]    ' Find the record that matches the control.[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Dim rs As Object[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]    Set rs = Me.Recordset.Clone[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    rs.FindFirst "[Name_MMA] = " & Str(Nz(Me![Personnel_List], 0))[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    If Not rs.EOF Then Me.Bookmark = rs.Bookmark[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]

Thank you for all your help,
~Kit

 

[missinglinq]

...I get a “Type Mismatch” error when I run it. I don’t know what it thinks is wrong since all the values are strings…

What is wrong is that the syntax you're using

rs.FindFirst "[Name_MMA] = " & Str(Nz(Me![Personnel_List], 0))

is only correct for a Number, not for Text!

Numeric Syntax

rs.FindFirst "[FieldName] = " & Me.ControlName

Text/String Syntax

rs.FindFirst "[FieldName] = '" & Me.ControlName & "'"

Linq ;0)>