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

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