Chaz-
Ah, I see what it's doing - the List Box picks up the city code values (for
example) rather than the lookup names from the Cities table. Any search you
do needs to be on the numeric values, so what it's doing is correct, but to
display the names, you'll have to grab the values, look them up, and put
them in the display text box. To do that, you'll have to leave the text
boxes unbound (no Control Source) and write some code in the AfterUpdate
event of the List Box.
Here's some code to get you started:
Private Sub SearchResults_AfterUpdate()
Dim db As DAO.Database, rst As DAO.Recordset
' Point to this database
Set db = CurrentDb
' Clear previous cities
Me.txtCity = ""
' Get cities
Set rst = db.OpenRecordset("SELECT City FROM Cities " & _
"WHERE ID IN (" & Me.SearchResults.Column(1) & ")")
' Loop thru them all
Do Until rst.EOF
Me.txtCity = Me.txtCity & rst!City & ", "
rst.MoveNext
Loop
' Lop off the ending comma, if any
If Len(Me.txtCity) > 0 Then
Me.txtCity = Left(Me.txtCity, Len(Me.txtCity) - 2)
End If
' Close the recordset
rst.Close
' Do county here
' Close out
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
Note that I changed the name of the unbound text box from Text17 to txtCity.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Charles
Dollins
Sent: Wednesday, July 17, 2013 8:50 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] making a list box based on a multi-value field
display user friendly values
John,
You are right, but when you specify city in the Field row, Access displays
all of the values in the multivalued field in just one row as an array or
comma delimited form when you run the query. However, when you use the
Valueproperty, as in City.Value, Access displays the multivalued field in
expanded form so that each value appears in a separate row. The user
specifically did not want it to be that way because it was confusing to have
so many separate instances of each agency. Sorry to be such a pain but any
other ideas?.
Chaz
On Wed, Jul 17, 2013 at 1:33 PM, John Viescas <JohnV@msn.com> wrote:
> **
>
>
> Chaz-
>
> Use the .Value property for your fields in the Row Source that uses
> QRY_SearchAll - i.e. QRY_SearchAll.City.Value instead of
> QRY_SearchAll.City.
> That should display the actual city name instead of a code.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications SQL Queries for Mere Mortals
> http://www.viescas.com/ (Paris, France)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Charles
> Dollins
> Sent: Wednesday, July 17, 2013 7:57 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] making a list box based on a multi-value
> field display user friendly values
>
> Hi John,
> I was searching this forum for a solution and came across the thread,
> "Populating an unbound textbox with multi item field ", which you
> contibuted on. It seemed like a similar problem but I can't figure out
> how to adapt it to my need. I know multi-value fields are bad, but the
> original project is totally dependent on them. I have looked online
> for an explanation of a substitute that creates the same functionality
> in access without using them but I haven't found it. I am posting the
> file Referral Database.zip Thank for your interest.
> Chaz
>
> On Wed, Jul 17, 2013 at 12:08 PM, John Viescas <JohnV@msn.com> wrote:
>
> > **
> >
> >
> > Chaz-
> >
> > Are you saying you're doing a lookup on a multi-value field? <sigh>
> > Multi-value fields are the devil's invention. You can display the
> > individual items by dropping the .Value property listed under the
> > multi-value field in query design. If there is a lookup table for
> > the multi-value field, you can adjust the Row Source, Bound Column,
> > Column Count, and Column Widths to display related text instead of
> > just the code number. I would need to know more about the actual
> > design of the underlying table to help you further.
> >
> > John Viescas, Author
> > Microsoft Access 2010 Inside Out
> > Microsoft Access 2007 Inside Out
> > Microsoft Access 2003 Inside Out
> > Building Microsoft Access Applications SQL Queries for Mere Mortals
> > http://www.viescas.com/ (Paris, France)
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of chaz
> > Sent: Wednesday, July 17, 2013 5:00 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] making a list box based on a multi-value
> > field display user friendly values
> >
> > I am trying to fix a project started by another person which has a
> > form which uses multi-value combo boxes to store key values. I have
> > to create a dynamic multi-field search form to look up community
> > resources for mental health. I have created a multi-field search
> > form based on a query which displays the lookups in a list box but
> > the results are numbers (bound
> > field)
> > and can only be queried by putting in a number which doesn't work
> > well (put in the number 2 and everything containing 2 returns), not
> > to speak of being non-user friendly. It is my understanding that the
> > values I want to display (they show up in the query) are hidden in
> > Access' multi-value field mechanism. Does anyone know how I can
> > access them to be displayed in the list box and searched by typing
> > them into a text box? This project is a good cause and will be used
> > to help many people in my community and made available to other
> > agencies to use as well. Thanks in advance.
> >
> > Chaz
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar