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

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@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Tuesday, July 03, 2012 11:45 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

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> , 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@yahoogroups.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>
> 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> , 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@yahoogroups.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>
> > 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]