ACCESS FEED: RE: [MS_AccessPros] Save from form to table where each item is 1 record; multiselect unbound boxes

Connie-

Well, you could include a multi-value field (horrors!) in which you store all
the selected valued from the list box. It's a bit tricky in code because you
have to open a Recordset2 object on the .Value property of the multi-value field
to insert values. It's possible, though.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Thursday, July 05, 2012 3:05 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Save from form to table where each item is 1
record; multiselect unbound boxes

John-- Ah! I believe I've got it!

The only thing I anticipate is that we will need to check/change the buyer's
criteria from time to time. So is there a way to populate the multi select list
box from the saved field? This would happen each time the form is opened.

I'm off work yesterday and today, but I feel kind of like a dog with a
bone--don't want to give up! When this part is done I'll feel like I'm "done".
WooHoo! So I'm looking forward to actually working on what we're talking about
on Friday. Once this is done I'll have a LOT of work left to correct errors like
looking for more UNION's vs UNION ALL, ...

Merci mille fois :-)
Connie

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@...>
wrote:
>
> Connie-
>
>
>
> Bill is correct, but in this case, you're creating an internal filter that the
> user will never see. I had forgotten that what the user sees is a multi-select
> listbox. When you go to save the filter, you would save an = test if the user
> selected only one value. If the user selects multiple, then build an IN clause
> with the values listed as you would in SQL. You're merely saving the filter
> values so that when a new property gets listed, you can check them one by one
to
> see if any existing buyers might be looking for that property. Your "filter"
> table might look like:
>
>
>
> tblBuyerSearches
>
> BuyerID
>
> SearchInitiated (date)
>
> City
>
> Bedrooms
>
> Bathrooms
>
> SqFt
>
> HasGarden
>
> LowPrice
>
> HighPrice
>
> etc. for all the criteria you offer.
>
>
>
> After looking at the selected cities, the field might end up looking like:
>
>
>
> ="Hancock"
>
>
>
> or
>
>
>
> IN ("Hancock", "Morris")
>
>
>
> Remember, the user doesn't see this. You should be able to directly plug in
the
> search values you store into a filter or WHERE clause.
>
>
>
> strWhere = "[City] " & rstSearch!City
>
>
>
> Get it?
>
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> <http://www.viescas.com/> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill Mosca
> Sent: Thursday, July 05, 2012 2:13 AM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Save from form to table where each item is 1
> record; multiselect unbound boxes
>
>
>
>
>
> Connie
>
> Even though it's the 4th of July and my wife would kill me if she knew I snuck
> into my office to check email, I'm going to step in a bit here.
>
> You are becoming quite the relational DB developer if you recognize
Multi-value
> fields as not falling into the rules of normalization.
>
> These fields are actually stored in a system table that you cannot get to. MS
> came up with this garbage to please the unwashed masses. No serious developer
> would use them. It would be like saying lookup fields are a good thing. (see
our
> twenty-something rules of development in our files folder.
>
> That said, I'm out of here for the rest of the day. Time to enjoy the family
and
> celebrate our freedom from oppression.
>
> Regards,
> Bill Mosca,
> Founder, MS_Access_Professionals
> That'll do IT <http://thatlldoit.com/> http://thatlldoit.com
> MS Access MVP
> <https://mvp.support.microsoft.com/profile/Bill.Mosca>
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> Sent: Wednesday, July 04, 2012 5:02 PM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] Save from form to table where each item is 1
> record; multiselect unbound boxes
>
> John,
>
> It seems like having several values in a field goes against normalization
rules.
> Is there a benefit to this or reason to go this route?
>
> Also, our goal is to make it obvious to the users what to do so I'd prefer to
> have a way for them to enter the multi data in a way that they've seen on
other
> forms. I'd like to use a multiselect box but that seems inherently problematic
> (especially with repopulating it). So i'm wondering about using the table idea
> that graham suggested for house types and cities. Then use subforms (
continuous
> or data form) for the cities and house type lists. Do you see problems with
this
> approach?
>
> I really appreciate your input--Thanks!
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> wrote:
> >
> > Connie-
> >
> >
> >
> > Tell the users they can enter a list of values:
> >
> >
> >
> > Hancock, Morris
> >
> >
> >
> > You probably don't want to train them to select "IN" as the operator, so
your
> > code will have to accept "=" and convert it to IN when you see a list of
> values.
> > Will probably need some front-end validating to ensure the user is entering
> > something you can later parse into a real predicate.
> >
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > <http://www.viescas.com/> http://www.viescas.com/
> >
> > (Paris, France)
> >
> >
> >
> >
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> > Sent: Tuesday, July 03, 2012 11:45 PM
> > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: Re: [MS_AccessPros] Save from form to table where each item is 1
> > record; multiselect unbound boxes
> >
> >
> >
> >
> >
> > How would I enter into the table with the saved search that they want a
house
> in
> > Hancock or Morris? Don't really want to have a field for each city as the
> cities
> > that we service could change.
> >
> > Thanks John!
> > Connie
> >
> > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> > wrote:
> > >
> > > Connie-
> > >
> > >
> > >
> > > The secret is in the "comparison" operator. When multiple criteria apply
> (per
> > > your example for cities and number of stories), use IN as the operator and
> > > provide a list of acceptable values. If they want exactly 2 bathrooms,
then
> > the
> > > entry is:
> > >
> > >
> > >
> > > NoBathrooms = 2
> > >
> > >
> > >
> > > If they're happy with 2 or more bathrooms, then:
> > >
> > >
> > >
> > > NoBathrooms >= 2
> > >
> > >
> > >
> > > If they want 2 or 3 bathrooms, then:
> > >
> > >
> > >
> > > NoBathrooms IN 2, 3
> > >
> > >
> > >
> > > Capiche?
> > >
> > >
> > >
> > > You would have to write code to assemble the appropriate filter given the
> > field
> > > name, the comparison, and the value.
> > >
> > >
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > <http://www.viescas.com/> http://www.viescas.com/
> > >
> > > (Paris, France)
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> > > Sent: Tuesday, July 03, 2012 11:11 PM
> > > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: Re: [MS_AccessPros] Save from form to table where each item is 1
> > > record; multiselect unbound boxes
> > >
> > >
> > >
> > >
> > >
> > > John--Yes this definitely helps! Only question that pops up is how to work
> > with
> > > multivalued criteria like Cities and Types of houses. For example, lets
say
> > they
> > > would be okay with houses in Morris or Hancock and they are looking for a
> two
> > > story or 1.5 story house.
> > >
> > > I currently have LookingEvent which which connects buyers together and is
> > > created each time they are looking for a property. (A buyer can be looking
> for
> > a
> > > personal home, investment property, and business property at one time so
> > that's
> > > 3 LookingEvents). So I'll put the search criteria in that table. Seems a
lot
> > > easier this way than the other way.
> > >
> > > thanks!
> > > Connie
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> > > wrote:
> > > >
> > > > Connie-
> > > >
> > > >
> > > >
> > > > Each "search" is a complete entity, correct? Then rather than save the
> > search
> > > > criteria as individual items, save it as a single row that has values
> > entered
> > > in
> > > > columns for each criteria. The "search" row should be linked back to the
> > buyer
> > > > who has those criteria. When a new property is entered as a listing, you
> can
> > > > perform a "search" using the saved buyer criteria to come up with buyers
> > that
> > > > might match. Note that the match doesn't need to be 100%. You could mark
> > > > certain criteria as critical. For example, if buyers are searching for a
3
> > > > bedroom house, no sense getting a "match" on 1 bedroom even though all
the
> > > other
> > > > criteria match. But number of bathrooms could be a "maybe". Buyer wants
2
> > > > bathrooms, but a match on 1 or 3 bathrooms would be a "maybe" if all the
> > other
> > > > criteria are good. And price could have some flexibility. For example,
if
> > > > someone says they don't want to spend more than 250K, you should still
> come
> > up
> > > > with a "maybe" on houses newly listed at up to some percentage above
that.
> > > >
> > > >
> > > >
> > > > Does that help?
> > > >
> > > >
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > <http://www.viescas.com/> http://www.viescas.com/
> > > >
> > > > (Paris, France)
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
mrsgoudge
> > > > Sent: Tuesday, July 03, 2012 8:26 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > Subject: [MS_AccessPros] Save from form to table where each item is 1
> > record;
> > > > multiselect unbound boxes
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Hi All!
> > > > I am working on creating a form where the parameters that a buyer has
can
> be
> > > > entered. A couple of criteria can have several items in one category
> > (several
> > > > cities, several types of houses) and the rest have one response (such as
> min
> > > > beds, etc). Those criteria that have several items are selected in a
> > > multiselect
> > > > box. Right now the boxes are unbound and are working to the point that a
> > > button
> > > > can be clicked and it creates a filter which is then applied to another
> form
> > > so
> > > > that all the houses meeting the parameters are displayed.
> > > >
> > > > ***Now I need to change it so that the entries are saved to a table.
> > > >
> > > > The bigger picture is that the next project will be having Access look
up
> > > buyers
> > > > that match a new house when it's entered. i.e. Right now I'm working on
> > > finding
> > > > houses when a buyer is entered, and next I'd like to find buyers when a
> > house
> > > is
> > > > entered.
> > > >
> > > > As it stands now the house features are entered into one line on the
> > Listings
> > > > table. The buyers parameters are supposed to be entered into the
> > SearchParams
> > > > table with one record per parameter.
> > > >
> > > > Graham had given me input back in March but has not responded to current
> > > > questions, so I'm reposting this. I am fine if I have to redo because
you
> > want
> > > > to do it a different way.
> > > >
> > > > Info is below.
> > > >
> > > > What would I do without you!! XOX ;-)
> > > > Connie
> > > >
> > > > Here are the tables I currently have:
> > > >
> > > > SearchCriteria
> > > > ==============
> > > > CriteriaID (autonumber)
> > > > CriteriaName (unique text)(min Bedrooms, max Bedrooms, min bathrooms,
max
> > > > bathrooms, rural, city, housetype, etc.)
> > > > CriteriaField (name of field to filter)(Beds, Baths, Rural, City,
> HouseType,
> > > > etc.)
> > > > CriteriaType (code for text, numeric, etc)
> > > > Operator (comparison operator for the WHERE clause)
> > > > AllowMulti (boolean - more than one allowed per search)
> > > >
> > > > My SearchCriteria records look like this (I followed Grahams advice but
do
> > not
> > > > know how to use the AllowMulti:true portion) and since I had already
> created
> > a
> > > > search form for houses I used that code for creating a filter rather
than
> > his
> > > > operators.
> > > >
> > > > ID:1 Name:MinBedrooms Field:NumBedrooms Type:Numeric Operator:>=
> > > > AllowMulti:false
> > > > ID:2 Name:MaxBedrooms Field:NumBedrooms Type:Numeric Operator:<=
> > > > AllowMulti:false
> > > > ID:3 Name:MinBathrooms Field:NumBathrooms Type:Numeric Operator:>=
> > > > AllowMulti:false
> > > > ID:4 Name:MaxBathrooms Field:NumBathrooms Type:Numeric Operator:<=
> > > > AllowMulti:false
> > > > ID:5 Name:City Field:City Type:Numeric Operator:IN
> > > > AllowMulti:true
> > > > ID:6 Name:HouseType Field:HouseType Type:Numeric Operator:IN
> > > > AllowMulti:true
> > > > (I have tables for Cities and HouseTypes with numeric
> > > > PKs)
> > > >
> > > > LookingEvent
> > > > ==============
> > > > LookingID
> > > > BeginDate
> > > > EndDate
> > > > Agent
> > > > BuyerType
> > > > Notes
> > > > SaleID
> > > > SearchParams (a junction table joining the above tables)
> > > > ============
> > > > LookingID (foreign key to LookingEvent table)
> > > > CriteriaID (Foreign key to SearchCriteria table)
> > > > ParamValue (text)
> > > > Listings (where the info re house parameters resides)
> > > > ============
> > > > ListID
> > > > ..
> > > > Price
> > > > Beds
> > > > Baths
> > > > Rural
> > > > HouseTypeID
> > > >
> > > > The Price will have to be gotten from a query.
> > > >
> > > > Graham had said the following but I don't know how to save from a form
to
> a
> > > > table where each item is one record and where some data is entered into
a
> > > Multi
> > > > select box. I'm leaving my questions regarding the last paragraph until
> I've
> > > > been able to save the parameters.
> > > > When you save a search, create a Searches record and then create as many
> > > > related SearchParams records as required. For example, if the search
> > > > specifies at least 2 bedrooms, housetypes 1, 3 or 4 and cities 48 or 56,
> > > > then (assuming SearchID is 1) your SearchParams records would look like
> > > > this:
> > > >
> > > > 1 1 2
> > > > 1 5 48
> > > > 1 5 56
> > > > 1 6 1
> > > > 1 6 3
> > > > 1 6 4
> > > >
> > > > Note that the values in the last five records correspond to the selected
> > > > items in the Cities and HouseTypes listboxes.
> > > >
> > > > Similarly, when you load a saved search, you can read the SearchParams
> > > > records and set/select values in textboxes/listboxes. You could
facilitate
> > > > this by setting the Tag property of the textbox/listbox to the
> corresponding
> > > > SearchID value.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

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