Guess I'm just looking at the next step and not the total picture. How would one then populate the form to show what the buyers are looking for?
Thanks again!
Connie
--- In MS_Access_Professionals@yahoogroups.com, "Graham Mandeno" <graham@...> wrote:
>
> Hi Connie
>
> You cannot have a bound multiselect listbox, because the Value of the
> listbox is not unambiguously defined. You can use a multiselect listbox to
> represent the many side of a one-to-many relationship, but you need to write
> code to:
> - load the listbox with the "selected" values from a recordset
> - respond to changes in the selection by adding/deleting many-side
> records
>
> This code is reasonably straightforward but post back if you need help with
> it.
>
> The other question is table design. I think I would recommend three tables:
>
> Searches
> ========
> SearchID (autonumber)
> CustomerFK (foreign key to Customers table)
> SearchDate (date/time)
> SearchNote (text)
>
> SearchCriteria
> ==============
> CriteriaID (autonumber)
> CriteriaName (unique text)
> CriteriaField (name of field to filter)
> CriteriaType (code for text, numeric, etc)
> Operator (comparison operator for the WHERE clause)
> AllowMulti (boolean - more than one allowed per search)
>
> SearchParams (a junction table joining the above tables)
> ============
> SearchFK (foreign key to Searches table)
> CriteriaFK (Foreign key to SearchCriteria table)
> ParamValue (text)
>
> Your SearchCriteria records would look like this:
>
> 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
> ... etc
>
> (note I am assuming you have tables for Cities and HouseTypes with numeric
> PKs)
>
> 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.
>
> Finally, you can write a function to translate a SearchID into a WHERE
> clause. This function would need to read a query based on Searchparams
> joined to SearchCriteria and build a string:
>
> CreateSearchFilter( 1 )
> would return:
> (NumBedrooms>=2) AND (City IN (48,56)) AND HouseType IN (1,3,4))
>
> I hope this gives you enough to go on. If not, please post back!
>
> Best wishes,
> Graham Mandeno
> Microsoft Access MVP 1996 - 2012
>
>
> > From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > Sent: Wednesday, 29 February 2012 11:51
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Transferring data from one multilist to another
> and pro/cons of bound multilist
> >
> >
> > Hi all!
> >
> > I have a form that has a header which creates a filter to search our
> listings based upon cities selected,
> > # bedrooms, # baths etc. cboCity and cboHouseType are both unbound
> multiselect list boxes in this header.
> > Therefore we can search for houses in 3 cities and 4 types of houses. This
> form is working well.
> >
> > I have another form (LookingEvent)where Lookers are entered. Similar info
> is entered here. Only problem
> > is that I'd like to store the info that is entered here and click a button
> to enter that info into the
> > Search_AllListings form (mentioned above) to bring up all the houses that
> fit this person. Also, later
> > I'd like to enter the info for a new house into a form (yet to be created)
> and pull up potential buyers.
> >
> > I vaguely recall that multiselect boxes are not recommended.
> > -Would you tell me why?
> > -In this situation should I be creating a one to many relationship with a
> table for cities and types of houses?
> > -If so, is it better to have one table for both cities and type of houses
> or separate them?
> >
> > Thanks!
>
Tidak ada komentar:
Posting Komentar