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

That works great John. I am so rusty at all this haven't done any in
several years and was just learning even then, but this is causing me to
remember. I will be a faithful group member after this. I will work on
implementing the rest of the fields, then turn back to the search query
itself. Have a good weekend.
Chaz

On Fri, Jul 19, 2013 at 8:49 AM, John Viescas <JohnV@msn.com> wrote:

> **
>
>
> Chaz-
>
> Here's my code again with a fix added to check for empty city.
>
> 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 - if there is something to in SearchResults
> If Len(Me.SearchResults.Column(1) & "") > 0 Then
> 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
> End If
>
> ' Do county here
>
> ' Close out
> rst.Close
> Set rst = Nothing
> Set db = Nothing
>
> End Sub
>
> What does your code look like, and where is it halting?
>
> 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: Friday, July 19, 2013 2:54 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,
> 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
>
>
>


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