If user has selected a value from a combo box, eliminate that value as a choice for other users.

[wily_wolf]

I am in need of a way that if a user selects a value from a combo box, other users will not be able to see/select that value. The code or whatever could either make the value disappear, or disable it as a choice (which I'm not sure if the latter is possible). Anyways, I would suspect some If..Then statements, but frankly, I am puzzled as to how this could work.

Furthermore, I don't know if this would conflict, but currently I have it setup where, if the user selects the value, updates the data on the form, and submits that data (using a button on the form), the value WILL disappear from the combo box. However, prior to the submission of the data, the value will still appear if other users are using the database as well. I want to make it so, once that value is selected, no other user can see it and subsequently select it.

Any and all help would be great.

[ranman256]

So, if a user picks NY in the combo on the form, no other user can pick NY.
Is this forever? or just just while the user is on the form?
If he picks NY it is added to a TABLE?
if other people open the form, is it supposed to remove ALL states in the table? Until there are no states left to pick?

[wily_wolf]

Q1: So, if a user picks NY in the combo on the form, no other user can pick NY.
A1: Yes, that is what I'm going for.

Q2: Is this forever? or just just while the user is on the form?
A2: Yes this would be permanent, because once the user has selected it, completed the form, and submitted it, the value would/should disappear so no more editing can be made.

Q3: If he picks NY it is added to a TABLE?
A3: If I'm understanding this questions correctly, I don't think it would be added to the table immediately, but after the "Submit" button is hit, then that value and the rest of the data on the form will be saved to a table (Table2, referenced below).

Q4: if other people open the form, is it supposed to remove ALL states in the table? Until there are no states left to pick?
A4: I think that is right. So, once the user selects and submits the value/data, that value is removed permanently, until there are no other values to pick.

Currently, I have two tables...the first table (Table1) contains data that has been imported from an Excel spreadsheet. Some of this table's data is pulled onto the form (including those values in the combo box). The second table (Table2) is where the edited/updated data will be saved. So, the user selects the value, fills out the required fields on the form, hits the submit button and then the data is saved into Table2.

I mentioned that I currently have it setup where once the user hits the submit button, the selected value in the combo WILL disappear...permanently. This is because Table2 is checking the ID (primary key) from Table1 and if the ID matches what is in Table2, the value is excluded from the combo box.

Sorry, I rambled on, but am just trying to clarify as much as possible.

[Homegrownandy]

without drilling to deep into what you're doing, I assume NY exists somewhere on your tables.

When the combo box is selected then on that form it should have the ID of the associated record. You can do an event on your submit button that will do something to the NY record.

you can use a tickbox, or add a date (so you know when it was selected), or who selected it. Or a combination of whatever you want.

If the NY record is changed you can use that change to eliminate it from the initial search. So in the criteria for the combo box query you would exclude any that have been selected.

Hope this helps.

[ranman256]

You want an outer join. The existing items saved joined to the list to pick from.
choose all items In pick list,but not in data saved.

[vicsaccess]

Please excuse me for jumping into your conversation but i do something similar that might be a simple solution. If I'm understanding your setup, the combo box is a listing of jobs unassigned, once the user picks a value from the combo its their job to complete?

For myself i have a table of jobs with a column for the users ID that has taken on this job. So simply sort the combo to display records with a null ID value. Once the user selects and confirms the job, save their ID in the ID field.

[wily_wolf]

ranman, can you explain your solution maybe with more detail? So, using an query with an outer join will make the value disappear upon selection?

Btw, if there's more information I can provide, just let me know.

Thanks.