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

John,
The error I am getting is 'Syntax Error (missing operator) in query
expression' and I have not been able to find a fix specific to this code.
Can you send the correction? Thanks in advance.
Chaz

On Thu, Jul 18, 2013 at 10:35 AM, John Viescas <JohnV@msn.com> wrote:

> **
>
>
> No, my bad. I should have coded for that!
>
> 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: Thursday, July 18, 2013 2:51 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,
>
> My bad. On closer inspection, it's breaking when it comes to a row that is
> empty with no city.
>
> 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
>
>
>


[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/