[tt]
Database background:
====================
The database is currently split into the data as one database and the forms, queries and reports in another
database. Both databases sit on a file server and are used by multiple users.
Problem:
========
I want to create a form that allows users to select multiple items a list the results of which can then be
used in a standard access query (rather than a recordset). I have seen many examples of this on the
internet all of which are great if the database is only single user. These wouldn't work when applying them
to a multi user environment.
My current/proposed solution:
=============================
I have created a form that lists all sites in a listbox and allows them to add their selected values into a
second listbox (see my horrible ascii design below). The usual add, add all, remove, remove all buttons
allow them to add and removed from the selected list.
ALL SELECTED
_______ _______
|Site1 | ADD |Site3 |
|Site2 | ADD ALL | |
|Site4 | REMOVE | |
|_______| REMOVE ALL |_______|
Once the user has selected the sites they want they hit a button that will create run a standard access
query using the selected values in its where clause (hopefully via an IN command).
My first though was to loop through the selection and add them into a textbox and then use that (adding the
OR's in between). That idea works but your limited on the number of characters so its only good if using
ID's not descriptions. I couldn't get an IN to work in the WHERE clause so have pretty much abandoned this
as an idea (I have posted a help for this in the same forum).
My second though was based upon the original ideas that I have seen for this on the internet. I create a
third database which will be created on the users own PC. This will contain tables that are populated with
the users report selections. When the user creates their selection a three stage process will occur:
1) The local database table that applies to the query has all the data deleted
2) The local database is then updated using recordset inserts
3) The data in the local table can then be used through a link
My Question
===========
This all seems like a lot of extra work to do something that is very simple in most other database languages
and front end tools. For example, this would be very easy to do in visual foxpro. Does anyone know of an
easy way to achieve this in Access that I have missed?
Many thanks,
[/tt]
Mark Davies
Warwickshire County Council
Database background:
====================
The database is currently split into the data as one database and the forms, queries and reports in another
database. Both databases sit on a file server and are used by multiple users.
Problem:
========
I want to create a form that allows users to select multiple items a list the results of which can then be
used in a standard access query (rather than a recordset). I have seen many examples of this on the
internet all of which are great if the database is only single user. These wouldn't work when applying them
to a multi user environment.
My current/proposed solution:
=============================
I have created a form that lists all sites in a listbox and allows them to add their selected values into a
second listbox (see my horrible ascii design below). The usual add, add all, remove, remove all buttons
allow them to add and removed from the selected list.
ALL SELECTED
_______ _______
|Site1 | ADD |Site3 |
|Site2 | ADD ALL | |
|Site4 | REMOVE | |
|_______| REMOVE ALL |_______|
Once the user has selected the sites they want they hit a button that will create run a standard access
query using the selected values in its where clause (hopefully via an IN command).
My first though was to loop through the selection and add them into a textbox and then use that (adding the
OR's in between). That idea works but your limited on the number of characters so its only good if using
ID's not descriptions. I couldn't get an IN to work in the WHERE clause so have pretty much abandoned this
as an idea (I have posted a help for this in the same forum).
My second though was based upon the original ideas that I have seen for this on the internet. I create a
third database which will be created on the users own PC. This will contain tables that are populated with
the users report selections. When the user creates their selection a three stage process will occur:
1) The local database table that applies to the query has all the data deleted
2) The local database is then updated using recordset inserts
3) The data in the local table can then be used through a link
My Question
===========
This all seems like a lot of extra work to do something that is very simple in most other database languages
and front end tools. For example, this would be very easy to do in visual foxpro. Does anyone know of an
easy way to achieve this in Access that I have missed?
Many thanks,
[/tt]
Mark Davies
Warwickshire County Council
