Solved

Help with displaying proper row in combo box

Posted on 2006-11-09
17
264 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17910683
the ORDER By is correct, check carefully
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17917537
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

821 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