I have a search as outlined in this thread: http://www.experts-exchange.com/Databases/MS_Access/Q_22041863.html
text field: txtSearch
search button: pbSearch
combo box to display results: cboResults
Here is the code associated with the search button and combo box:
Private Sub pbSearch_Click()
Dim SQL As String
If Nz(Me.txtSearch, "0") <> "0" Then 'there is a value in the field
SQL = "SELECT * FROM tblBuildings " & _
"WHERE Title like '*" & Me.txtSearch & "*' " & _
"OR History like '*" & Me.txtSearch & "*' " & _
"OR Archaeological_Potential like '*" & Me.txtSearch & "*' "
Me.cboResults.RowSource = SQL
Private Sub cboResults_Click()
DoCmd.OpenForm "frmBuildings", acNormal, , "idXindex = " & Me.cboResults.Column(0) 'this refers to the first column in the table, which is the autogenerated id of the record
In the "Row Source" on the form for the combo box I have the following SQL:
WHERE (((tblBuildings.Title) Like '*')) OR (((tblBuildings.History) Like '*')) OR (((tblBuildings.Archaeolog
tial) Like '*'))
ORDER BY [Location];
This all works fine; however, to the user, how the combo displays now, is pretty usesless. Instead of displaying the results from Location, it instead displays the results from idXindex. That is, instead of the name of the location it lists the autogenerated number which the user may choose and then launch the buildings form. My question then, is, what would I need to do to have the combo box display the record results as Location results vs. idXindex results. Location is column 4 in my table. I thought it might be as easy as changing the idXindex and the reference to Column (0) but that didn't seem to work.
Any help would be much appreciated. I'm very new to SQL and ACCESS.