Need to filter combobox query for already selected values. Need Help

[PLiNk]

Thorough problem description:

I am constructing a database program that is intended to control how a competition unfolds, say with teams, team match with paintgun or football with variable teams, and score processing.

When selecting the teams I absoluteley need to remove say Frank Conners from the list so that it is impossible to select a record twice or thrice or place one player on both teams.

I have tried to solve this with a virtual field, but it should be able to do this with filters and perhaps an afterupdate or Dirty form function.

I wouldn't be surprised if the solution was dead simple.
Can you help?

Regards Stan Dandtjes.

 

[scriverb]

This can be done numerous ways. I need a little more information about the specifics of your process when you are picking the player. Do you post that selection to a Team/Player table or something like that? If so we can create a query for your combobox that performs a Left Join from the Players table to the Team/Players table and only provide players that don't have a record match. A simple requery of the combobox after the selection will refresh the list without the all players already selected.

Waiting for your reply.

Bob Scriver

 

[Trendsetter]

Hi Stan,
if you send me an email at frank@fhsservices.co.uk with subject "dual list boxes" I will send you a sample that works on a very similar principle to what you want to do.

 

[PLiNk]

What happens is this:

I am working on a database for controlling
compos at LAN parties.

I have all the members of the community in onetable. This one is called Players
Some of these players want to be in on this
compo but not all.
I have a table called Compo. This contains information about the compo.
Then I have a table called PlayerinCompo in which I add those who want to play in this compo. Compokey and Playerkey constitues the primary key, so that it impossible to add players twice. But it looks like it is possible now, without filtering out those already selected. I am currently using dual list boxes, which looks nice, but the problem also occured with comboboxes in a subform with continous listing.

I tried to do exactly what you said in the post above, i couldn't get the query to discriminate. My limited mind tried to use
the [playerkey] is null but that didn't work.

[player][playerkey]-[playerincompo][playerkey] = 0
and
[player][playerkey]- null = [playerkey]

select [player][playerkey] if [playerincompo][playerkey]is null
should work.

i cannot write as you might know
criteria = is not 0, only null will work.

Problem is that when there is no record in the playerinCompo table there isn't even a null value, but something less than that, namely the lack of a field that has nothing in it.

Which makes it tiny by any standard.
It's the little things you know, that eats your guts.

Do you know a way to make a select query to select everything by standard, and then use the select sql statement to remove post instead?

regards.

Stan Dandtjes

 

[scriverb]

Let's give this a try. We need to create a Global variable to hold the current CompoKey. Copy and paste the following into a database module:
Global vCompo as String
Function Compo()
Compo = vCompo
End Function

Now on your form when you identify the compo that you are working on you have to execute the following:
vCompo = Me.Compo

Copy and paste the following SQL into a query to be used as the RowSource of your combobox. Name it as you wish.

Select [Players]![Playerkey], [Players]![PlayerName]
FROM Players
WHERE [Players]![Playerkey] NOT IN (Select [PlayerInCompo]![Playerkey] FROM PlayerInCompo WHERE [PlayerInCompo]![Compokey] = Compo());

After each pick of the ComboBox you will have to execute the following:
Me.ComboboxName.Requery

Now I have made a few assumptions thru lack of information of your process. If you don't understand any of this please get back with me and we can work this out.

Bob Scriver

 

[PLiNk]

Thanks both of you for very very valuable help!

But I'm still stuck, although I managed to filter out by using the NOT IN clause which I didn't exist. I gald it did.

And although the Dual list boxes didn't do exactly what i wanted them to do I found excellent use for the procedure. I now have a screen for sorting out the teams, and teams may vary from compo to compo.

The dual list boxes i got from Bob Scriver (cheers) didn't add records into a player/compo table. It changed a field into the value highlighted in a combobox. This limits the number of simultaneous compos to 1, since it doesn't use a middle table.

If you got the time and feel like it,
I really need help on hwo to get a list of players in a listbox into a player/compo table, visualised thorugha listbox, where the compo value is a global constant. (for the moment)

I'm using a primitve way of global settings, by running a form in the background. Downside is that it has to be open for things to work. Good thing is that you can change the active compo with a single click.

didn't get the global VB stuff to work since
I only ordered the visual basic bible today... Any other books I need?

 

[scriverb]

PLink: I am not sure where we are right now. Did you follow the instructions and create the global variable and function code? I know you gleaned off the NOT IN stuff but did you use the exact SQL that I posted as the RowSource of your ComboBox??

You see the idea is that while you are working with an individual Compo( what is this anyway? ) your code would store the Compo value in the Global variable. This value could then be used by the query SQL that I provided to help select only the Compo/player records for this individual compo. The Function is called in a NESTED Select that draws up a list of Players that have already been entered for this Compo. Then the NOT IN will eliminate them from the active list for the combobox.

Let me know what you have done and what you haven't.

Bob Scriver

 

[Trendsetter]

The dual list box sample that I sent you was not intended to do exactly what you wanted , as I pointed out but, it was intended to give you the idea and the method to acheive what you wanted.
To do what you want you need to convert the second listbox to let you select the CompoID from the combo above and transfer the players from the first listbox to the second. This would then need to write the data to the table which is the many to many part of the design, whilst at the same time it is excluding the selected players from the first listbox thereby preventing you selecting the player(s) twice.
Frank Hill

 

[PLiNk]

I tried to use the global variable and function code, but all I got was a compile error so i started working on a new set of listboxes instead.

And yes, I used the exact sql, i just changed the global variable part into my own very special brand of global variable that isn't very good but it works.
So I got it to work, just not with the global variable. But I'm going to try that again now.

I might as well explain what it is that I'm doing.
A compo is short for competition, but it has come to specificially mean competitions at LAN parties. Basically i have all the players, several compos with players attached through a players/compo table, a settings table with information on how to produce the necessary quarterfinals, semifinals and the finale and bronzefinale. Each of these rounds are then split into Battles. The program will contain ways of random teams and of course listing of the winners and looser with points and so on.

I'm planning to sell this stuff to them in the end, but the databases I have made in the past have not required such extensive VB coding so I've got a knowledge hole when it comes to visual b.


PSFrank Hill, Thanks for your help. Last post is exactly what I want to do. It's just above my head at the moment. I'm thinking.
I'll get it right sooner or later.