• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Help with displaying proper row in combo box

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
    End If
    Me.Refresh
   
   
End Sub
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

End Sub

In the "Row Source" on the form for the combo box I have the following SQL:

SELECT *
FROM tblBuildings
WHERE (((tblBuildings.Title) Like '*')) OR (((tblBuildings.History) Like '*')) OR (((tblBuildings.Archaeological_Potential) 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.

Ethan
0
FocusedEnergy
Asked:
FocusedEnergy
  • 10
  • 7
1 Solution
 
Rey Obrero (Capricorn1)Commented:
Ethan
What are the columns you want to display in the combo box? Location?

with your present rowsource, to display location set the following properties for the combo box

Column Count  4

Column Widths  0;0;0;1

Bound Column  1

0
 
Rey Obrero (Capricorn1)Commented:
can you also post the fields for tblBuildings.
0
 
FocusedEnergyAuthor Commented:
Capricorn1, that perfectly.  Can you briefly explain to me what each of those fields mean?  I am thinking...Column Count = display forth column, column width = 0 for those one does not want to display and 1 for the one that one does.  Bound column = ?

My only problem now is that I can save the form and then exit but it seems that when I reopen to test, these properties do not necessary lock, but can default back to something else.  Is there a way to lock all properties?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Rey Obrero (Capricorn1)Commented:
from the property sheet of the combo, place the cursor in each of the properties and look at the
Status bar (left lower portion of the screen), you will see the explanation.
if that is not enough hit F1 and you will see the full explanation
0
 
Rey Obrero (Capricorn1)Commented:
you have to set the properties in the DESIGN view of the form, then save.
0
 
FocusedEnergyAuthor Commented:
Thanks capricorn1, it is staying put now and thank you for the type about the F1 command, I tried doing a search but ironically in the access search it would not list the specific field.

Here is my structure of the tblBuildings:

idXindex (autonumber)
Location (text)
Picture (memo)
Title (memo)
History (memo)
Images (memo)
Documents (memo)
Published and Unpublished Resources (memo)
Archaeological Potential (memo)
Zone (text)
Imexist (yes/no)

Though I am now displaying the proper column (Title) it seems to order by idXindex vs. Location:

SELECT *
FROM tblBuildings
WHERE (((tblBuildings.Title) Like '*')) OR (((tblBuildings.History) Like '*')) OR (((tblBuildings.Archaeological_Potential) Like '*'))
ORDER BY [Location];

Is the Order by in the wrong place?

I can make this a separate question if you would like since you did answer my first one.
0
 
Rey Obrero (Capricorn1)Commented:
the ORDER By is correct, check carefully
0
 
Rey Obrero (Capricorn1)Commented:
your select statement can be rewritten like this

SELECT *
FROM tblBuildings
Order By Location

do you want to sort it by Location or Title ?

SELECT *
FROM tblBuildings
Order By Title
0
 
FocusedEnergyAuthor Commented:
I want it to display Title but sort by Location.  The reason is that street numbers will screw up the order when we hit double or triple digits (2 Main St. will be ordered after 10 Main St.) so what I did was in the "Location" field, I ordered them correctly with say: 002 Main St. so that by the time it gets to 100 Main St. I am still in business, yet, I never wanted to dispaly 002 to the user, just 2, hence the order by Location but display in the combo box the results of Title.  

So my Row Source in the Combo box is:

SELECT *
FROM tblBuildings
WHERE (((tblBuildings.Title) Like '*')) OR (((tblBuildings.History) Like '*')) OR (((tblBuildings.Archaeological_Potential) Like '*'))
ORDER BY Location;

and then the combo box items:

Column Count  4 (title)  because Location = column 2.

Column Widths  0;0;0;1

Bound Column  1
0
 
FocusedEnergyAuthor Commented:
It seems that using:

SELECT *
FROM tblBuildings
Order By Location

Still does not have the combo box sorting properly.
0
 
Rey Obrero (Capricorn1)Commented:
can you upload a zipped copy of your db here http://www.ee-stuff.com
0
 
FocusedEnergyAuthor Commented:
Hmm, I kept getting an error stating it was too small of a file, yet it is 3 MB zipped?

Anyhow, I'll upload it to my friends directory on his webserver:

http://www.xyu.ca/~zanth/ArchaeologicalDB.zip

Thanks a bunch for looking at this for me.

0
 
FocusedEnergyAuthor Commented:
I suppose it would be good if I let you know where things are :)

The Switchboard shows you four buttons, hit "Get Started"

This will bring you to the Form frmLaunchpad and both the combo boxes on this page can't seem to sort correctly.

Thanks again really for helping me out.

0
 
Rey Obrero (Capricorn1)Commented:


        SQL = "SELECT * FROM tblBuildings " & _
              "WHERE Title like '*" & Me.txtSearch & "*' " & _
              "OR History like '*" & Me.txtSearch & "*' " & _
              "OR Archaeological_Potential like '*" & Me.txtSearch & "*' " & _
              "ORDER BY tblBuildings.Location;"              '<<<<<<<  Add this line
        Me.cboResults.RowSource = SQL

for combo cboResults    set the column widths  0";0";3"

0
 
Rey Obrero (Capricorn1)Commented:
for combo cboResults    set the column widths  0";0";0";3"
0
 
FocusedEnergyAuthor Commented:
Thank you so much!  So I no longer need the Row Source as the query?

Cheers,

Ethan
0
 
Rey Obrero (Capricorn1)Commented:
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now