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