selecting one item for each hire

[compwizard]

Hello All,

I have a quick query regarding limiting only one of each item to be selected in every single order.
For example from the drop down list of item, only one of each items could be selected so
if item1 is selected this would drop into a listbox, when the next item is to be selected, item 1 should not be allowed to be selected.
For the next order this item should reappear.

I am really stuck on this, any help would be appreciated or an example

Thanks

[ADezii]

Hello All,

I have a quick query regarding limiting only one of each item to be selected in every single order.
For example from the drop down list of item, only one of each items could be selected so
if item1 is selected this would drop into a listbox, when the next item is to be selected, item 1 should not be allowed to be selected.
For the next order this item should reappear.

I am really stuck on this, any help would be appreciated or an example

Thanks

If your goal is to absolutely make sure that an Item in a Drop Down Combo Box con never be selected more than once, then it should never appear as an Option once it has been selected, unless of course, it has been Reset. First a few assumptions:

1. Combo Box Name is cboItems.
2. Row Source for cboItems = tblItems.
3. tblItems consists of the following Fields.
   1. [Item_ID] - {AutoNumber} - [Primary Key]
   2. [Item_Name] - {TEXT}
   3. [Selected] - {Yes/No}
4. Bound Column = 1 ==> [Item_ID], and it is Hidden.
5. Once an Item is selected from the Combo Box, its [Selected] Field is set to True, the Row Source of the Combo Box is modified to [Selected] = False, and the Combo Box is Re-queried.
6. All Items marked as Selected, must be Reset at some point in order to display 'All' Items in the Combo Box.
7. Any questions, feel free to ask.

Dim MySQL As String
 
DoCmd.SetWarnings False
  MySQL = "Update tblItems Set [Selected] = True Where [Item_ID] = " & Me![cboItems]
  DoCmd.RunSQL MySQL
DoCmd.SetWarnings True
 
Me![cboItems].RowSource = "SELECT [Item_ID], [Item_Name] FROM [tblItems] Where [Selected] = False;"
 
Me![cboItems].Requery

'RESET Code (display 'All' Items)
Me![cboItems].RowSource = "SELECT [Item_ID], [Item_Name] FROM [tblItems];"
 
Me![cboItems].Requery

NOTE: Don't rely too heavily on this solution, although it works quite well. It's just that I threw this together when I was going out the door, and someone else will probably have a better response.

[compwizard]

I was hoping for the all the items to remain, but if the same item is selected again a message will appear advising ' that this item has already been selected, something along those lines'. any ideas?

Secondly, just reading through the code you provided, how would you hide a bound column.?

thanks

[ADezii]

I was hoping for the all the items to remain, but if the same item is selected again a message will appear advising ' that this item has already been selected, something along those lines'. any ideas?

Secondly, just reading through the code you provided, how would you hide a bound column.?

thanks

I was hoping for the all the items to remain, but if the same item is selected again a message will appear advising ' that this item has already been selected, something along those lines'. any ideas?

Dim MySQL As String
 
'See if the Item has already been selected
If DLookup("[Selected]", "tblItems", "[Item_ID] = " & Me![cboItems]) = True Then
  MsgBox Me![cboItems].Column(1) & " has already been selected, please make another selection", vbExclamation, "Item Already Selected"
Else
  'Not selected until now, so mark as Selected from this point on
  DoCmd.SetWarnings False
    MySQL = "Update tblItems Set [Selected] = True Where [Item_ID] = " & Me![cboItems]
    DoCmd.RunSQL MySQL
  DoCmd.SetWarnings True
End If
End Sub

Secondly, just reading through the code you provided, how would you hide a bound column.?

By setting its Column Width to 0