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