Re: [MS_AccessPros] making a list box based on a multi-value field display user friendly values

John,

That works but it looks like when it gets to a row that has more than one
city, it breaks with the message: Syntax error in query expression ID IN ("
& Me.SearchResults.Column(1) & ") (Error 3075)

Chaz

On Wed, Jul 17, 2013 at 2:37 PM, John Viescas <JohnV@msn.com> wrote:

> **
>
>
> 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
>
>
>


[Non-text portions of this message have been removed]



------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/