Solved

Help with displaying proper row in combo box

Posted on 2006-11-09
17
235 Views
Last Modified: 2008-03-03
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
Comment
Question by:FocusedEnergy
  • 10
  • 7
17 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 17910534
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17910538
can you also post the fields for tblBuildings.
0
 

Author Comment

by:FocusedEnergy
ID: 17910555
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17910581
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17910585
you have to set the properties in the DESIGN view of the form, then save.
0
 

Author Comment

by:FocusedEnergy
ID: 17910649
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17910683
the ORDER By is correct, check carefully
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17910771
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:FocusedEnergy
ID: 17910847
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
 

Author Comment

by:FocusedEnergy
ID: 17910964
It seems that using:

SELECT *
FROM tblBuildings
Order By Location

Still does not have the combo box sorting properly.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17910969
can you upload a zipped copy of your db here http://www.ee-stuff.com
0
 

Author Comment

by:FocusedEnergy
ID: 17911183
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
 

Author Comment

by:FocusedEnergy
ID: 17911188
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17911445


        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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17911475
for combo cboResults    set the column widths  0";0";0";3"
0
 

Author Comment

by:FocusedEnergy
ID: 17911582
Thank you so much!  So I no longer need the Row Source as the query?

Cheers,

Ethan
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17917537
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now