List box display data

[colossalUW]

Hello,

I've created a form to search for a particular grant proposal. The form contains several combo boxes (for grant number, PI ID, and Type) with which the user can narrow the search. A list box (lstSearch) appears at the bottom which displays the search results after "OK" is clicked.

My problem is that data displayed in the list box is not displayed in the same manner as in the combo boxes. For instance, the combo box will show the PI's name (Smith, John), but the list box displays John Smith's ID number, 199456. I realize this is b/c in the PI combo box (cboPIEID), I selected records from tblPIs and chose to only display the last and first name. The list box, however, is populated based on qryGrantsAll, which is in turn based on tblGrants. Also, the field for PI in tblGrants is set as a lookup to tblPIs, but it still display as ID # in lstSearch. How can I have the list box display the name instead of ID number?
Code for running search appears below.
Thanks for any help!

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strGC1 As String
Dim strPIEID As String
Dim strType As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryGrantsAll")
If IsNull(cboGC1) Then
strGC1 = " Like '*' "
Else
strGC1 = "='" & Me.[cboGC1].Value & "'"
End If
If IsNull(cboPIEID) Then
strPIEID = " Like '*' "
Else
strPIEID = "='" & Me.[cboPIEID].Value & "'"
End If
If IsNull(cboType) Then
strType = " Like '*' "
Else
strType = "='" & Me.[cboType].Value & "'"
End If
strSQL = "SELECT [tblGrants].* From [tblGrants] WHERE [tblGrants].[GC1#]" & strGC1 & " AND [tblGrants].[PIEID]" & strPIEID & " AND [tblGrants].[GntTypeID]" & strType & " ORDER BY [tblGrants].[GC1#];"
qdf.SQL = strSQL
[lstSearch].RowSource = ""
[lstSearch].RowSource = strSQL
Set qdf = Nothing
Set db = Nothing
End Sub

 

[jbpez]

Look column widths of the listbox, column count and bound column. Is your left most field the id or the name ?

John Borges

 

[colossalUW]

Hi John,

There are 3 columns in the list box: GC1#, PI, and Type. The bound column is currently the leftmost column, which is GC1#.

I think I see what you're getting at. If I make the PI ID the bound column, though, I still get the ID displayed and not the name -- I assume b/c the name isn't actually stored in tblGrants. tblGrants looks it up in tblPIs. Do I need to change strSQL to include ... AND [tblPIs].[PIEID] ... instead of (or in addition to) ... AND [tblGrants].[PIEID]" ... ?

Thanks for the help.

 

[TheAceMan1]

How are ya colossalUW . . . . .

[blue]. . . the combo box will show the PI's name ([purple]Smith, John[/purple]) . . .[/blue]

I find it hard to believe that [purple]Smith, John[/purple] is the result of data entry! [blue]Most likely its the result of concatenation in a Custom Field.[/blue] [blue]Is this correct![/blue]

In any case, you'll have to [blue]add the lookup table to the query[/blue] (with proper relationship) [blue]so you can get to the names[/blue] (unless there already a part of [blue]qryGrantsAll[/blue]). Add a [purple]Custom Field[/purple] to the query and [purple]concatenate the names there for proper display.[/purple]

See Ya! . . . . . .

 

[colossalUW]

Hey AceMan,

You're right, the name is not the result of data entry. Actually, I didn't concatenate the fields, either - I display them in the combo box in two columns: LastName and FirstName, but thanks for the idea to concatenate in the list box!

And thanks for the solution of adding the field to qryGrantsAll. I'm not in my office now to try it out, but I'm sure that's what I need to do.

Much appreciated!