I'm finally working on this. I'm wondering how I would get the parameters of a search into a record. Would I create a form with unbound boxes and upon saving, have code that would go through each box and enter them into the SearchParams table?
Thanks!
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