Drop-down list filtering 1

[kastaman]

Hi there,

I'm trying to create a form for the members to select their choices of teams on a weekly basis, but what i need for the drop-down list to do is once a team from the list is selected by the individual, they can't select that team again on the preceeding weeks.

For example:

Choices for Week 1:
Dogs
Cats
Bears
Lions

Dogs chosen for 1st week.

Choices for Week 2:
Cats
Bears
Lions



Thanks in advance,

Kastaman

 

[KenReay]

Hi

You need to have your drop down list based on a query, with a criteria which eliminates selected teams

You do not give details about your application, but let us say you have (at least two tables)

tblAvailableTeams

tblSelectedTeams

and that each team is uniquely identified by an Id Number

the criteria of the query on which the drop down is based would be would be something like:

SELECT Id, TeamName FROM tblAvailable WHERE Id NOT IN (SELECT Id FROM tblSelectedTeams);

In the Got Focus event of the dropdown list you need to refresh the list using a requery, so

CboTeams.requery

hope that helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[willir]

kastaman

You can base your combo box on a SELECT statement as the record source.

Assumptions...
- tblTeam is name of table holding team info.
- TeamName is the field holding the team name info
- TeamID is the primary key for each team in tblTeam
- tblSelectedTeams is name of table that captures teams selected by members for previous weeks. It includes TeamID and MememberID. There is a unique index (to prevent duplicates) TeamID + MemberID
- MemberID is on the form, and is referenced by [forms]![YourForm]![MemberID]

RowSource for the combo box...[tt]
SELECT Team FROM tblTeam Where TeamID NOT IN (SELECT TeamID from tblSelectedTeams Where MemberID = [forms]![YourForm]![MemberID]);[/tt]

Richard

 

[PaulF]

Or you can add a field to the table with the teams to be selected. This can be a boolean (yes/no), numeric (for week #) or text, doesn't matter, then update this field when the record is selected from the ComboBox. Then use a query for the ComborBox Source where this field is Null (for text or numeric) or False (if boolean).

PaulF

 

[kastaman]

Hi guys,

I'm thinking of using Access for this one.

One critical aspect I forgot to mention is that each user controls their own selection so do I need to create a table that contains the user's name and the selection so when a specific user chooses a team, it won't be available for that sepcific team only for the preceeding weeks.
Willir might have covered this already but I haven't delved into Access for some time now.



Thanks in advance,

Kastaman

 

[KenReay]

Hi same principle holds try, you need to base your drop down box on a query which excludes the already selected teams, it just means now the your criteria is slightly more complex in that you need to also test for current user if

More relevant here might be "How are you going to idntify users? ie

Via Windows login id?
or
Via Access Security ?
or
Via a custom login screen?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

Kastaman

Since it is very unlikely that you will not be able to store this information in the computer memory (i.e., not rebooting the workstation for weeks and weeks) is by tracking the user and their selection.

Ken and I posted very similar solutions at the same time because it is the solution that makes the most sense in Access.

Imagine this...
You have a credit card with a maximum limit, say $1000. The magic thing about the credit card is that it does not track usage - transactions are not stored, and the remaining credit limit is not tracked. So unless you over spend the $1000, you can use the credit card for all unlimited transactions -- pretty cool huh -- I want one of these babies. ;-)

...But is is not going to happen. Transactions are tracked, and remaining credit is adjusted.

Likewise, if you are going to impose restrictions, you need to track previous transactions. This is not a hard thing to do, but essential in order to meet your reuirement.

Richard

 

[kastaman]

Users will be given userid's and they will need to create their password to access the database.

So the query where the combo-box will pull the data will need to be specific to the userid and the teams not selected?

I'm thinking of a form that will look like the following:
(not sure if I should look at the queries and tables prior to designing the form to be used to make the selections)

username Week1 Week2 Week3 .........
Bob dropdown dropdown dropdown .........



Thanks in advance,

Kastaman

 

[willir]

Hi Again Kastaman

Not sure if you had a chance to read my last post. The one where the system knocked me off but allowed the post, so I show up as ()

To do this type of thing, you will need a table, call it PriorSelections...

tblPriorSelections
UserID - foriegn key, link to the
TeamID or TeamName - see discussion
SelectionWeek - could be a date field or an interger.

Discussion:
It probably makes sense to have a Team table, tblTeam. If you use a value list, you will be able to update the tblPriorSelections, but it will be very difficult to adjust the value list to exclude the teams from the value list but it would be very easy to use the tblTeam and the aforementioned SELECT clauses using the NOT IN solution.

Richard

 

[kastaman]

Thanks for the input. I'll create my database then apply the suggested solutions. I'll let you guys know if I need further assistance.



Thanks in advance,

Kastaman

 

[kastaman]

Hi there again,

Going through the design of the forms & tables and you mentioned that I should have tblPriorSelections AND tblSelectedTeams.

Firstly, do I need both table and secondly, how will the info populate the tables? Via forms/query?

Thanks in advance,

Kastaman

 

[willir]

Hi again Kastaman

tblPriorSelections should be fine - you want to store the "history". You may also want to have a tblTeam to store the team info.

How to update the tables - forms will do most of the work, but you will need to use a query, and either some macros or code.