Check for used ID's

[bodylojohn]

Hello,

I have a continious form containing data from the table "tblToernament".
On that form there is a combobox containing teams that are stores in the table "tblTeams".

The table looks like this:

ID | TeamName

1 | Team01
2 | Team02
3 | Team03
4 | Team04

When I select, for example, Team02 in the combobox I want that value on, after I selected it from the combo NOT to be in the combobox.

Can anybody please help me on this?

 

[ajetrumpet]

concatenate the value out of the current rowsource by referencing the combo's value on the change or update event.

I did the same thing for someone else with a listbox here:
http://www.access-programmers.co.uk/forums/showthread.php?t=146821

 

[bodylojohn]

concatenate the value out of the current rowsource by referencing the combo's value on the change or update event.

I did the same thing for someone else with a listbox here:
http://www.access-programmers.co.uk/forums/showthread.php?t=146821

Thanks for the reply but that is not what I need.

Basically it's this:

I have a query and a table. 1 query (query1) where I hold all available teams and the table (table1) is where I see the teams I added to a tournament so far.
Now.... query1 is the rowsource for a combobox that I use on a form based on table1. Every team that is stored in table1 I do NOT want to see in the combobox.

I hope this clarifies things.

 

[ajetrumpet]

Now.... query1 is the rowsource for a combobox that I use on a form based on table1. Every team that is stored in table1 I do NOT want to see in the combobox.

Not to be mean here John, but what I've quoted from you makes absolutely no sense. You'll need to explain another way...

 

[bodylojohn]

Not to be mean here John, but what I've quoted from you makes absolutely no sense. You'll need to explain another way...

You are not mean. I just didn't explain myself good enough.

I will try again ;-)

So I have a form that contains tournament data (Name of the tournament, date, etc... that is stored in the table tblTournament).
form1 is based on tblTournament.

On form1 resides a subform called frmParticipatingTeams wich is based in the table tblTournamentRule. frmParticipatingTeams is a continious form wich hold the ID's for the participating teams (selected via combobox).
Lets say I have 4 teams:

ID | Name
1 | Team01
2 | Team02
3 | Team03
4 | Team04

When I select team01 from the combobox, the ID of that team is stored in the table tblTournamentRule.
NOW..... This is what I hope to achieve:

on the next record, when i want to select a team from the combobox, Team01 is not suppost to be in the combobox.

I hope this makes it more clear?

 

[ajetrumpet]

This is the last thing I said, John. Do you not want to reduce the list in the combo box after every selection? That is, take the current value out of the list? That's exactly what it sounds like.

on the next record, when i want to select a team from the combobox, Team01 is not suppost to be in the combobox.

This is the quote that is confusing me John. Have I not already hit the nail on the head here?

If you're wanting a different combo box list for each record of the form, you can't do that. When you change the CB list, you will see the change no matter what record you are on, because it's only one form object, and thus only one CB object...

 

[bodylojohn]

This is the last thing I said, John. Do you not want to reduce the list in the combo box after every selection? That is, take the current value out of the list? That's exactly what it sounds like.This is the quote that is confusing me John. Have I not already hit the nail on the head here?

If you're wanting a different combo box list for each record of the form, you can't do that. When you change the CB list, you will see the change no matter what record you are on, because it's only one form object, and thus only one CB object...

This is the problem I think.

I just don't want to see the value in the combo if it has already been used.

 

[gemma-the-husky]

i would put a "selectedflag" yesno type in the table to use as a temporary marker

now base the combobox on a query thats finds teams with the flag set to "false". Now if you pick a team, set the flag to true and requery the combo box

job done

have another combo box showing teams picked (flag = true), and you can make teams "jump" from box to box.

 

[bodylojohn]

OK...

Right now I have a table (tblTeams) that contains all the teams participating in a tournament.
Then I have a query that contains the ID's of the teams that allready have played in the tournament.

Now this may be a very stupid question of mine, but how can I get the teams that do not have played in the tournament in a new query.

I tried several things but never with success.

Perhaps this could be the solution for my problem.

 

[bodylojohn]

OK...

Right now I have a table (tblTeams) that contains all the teams participating in a tournament.
Then I have a query that contains the ID's of the teams that allready have played in the tournament.

Now this may be a very stupid question of mine, but how can I get the teams that do not have played in the tournament in a new query.

I tried several things but never with success.

Perhaps this could be the solution for my problem.

OK.... outer join did the trick.

Thanks guys..