i have a combo box on a continuous sub form, what i want to do is, when i make a selection from the combo lets say for example, apples, on the next line, i dont want to be able to select apples again, so the option is removed from the selection
This could get messy.
You could save the combo output as a variable then add code to the on enter event of the combo to update the row source to exclude that value.
However, what do you want to happen the third time?
Exclude just the last selection ... or both the previous selections?
And what if you need to reselect a previous selection?
I would think about this carefully before you go down this route
in your case, rather than referencing another combo in your rowsource, you need to left join it to a table which lists the items to be excluded. This will need to be populated each time a record is updated and the rowsource refreshed when the combo is entered. Alternatively you might have code to maintain a string of selected items. See this link about a multi select form. https://www.access-programmers.co.uk/forums/showthread.php?t=289116. You may be able to adapt the code to build the string
you do not have an easy task in a continuous form - what if a user selects Apples, then next record Oranges, then goes back to the apples record and selects onions?
you need VBA on your Form to do that.
on this example the textbox is FRUIT:
Code:
Option Compare Database
Option Explicit
Dim strCollection As String
Private Sub FRUIT_AfterUpdate()
If InStr(strCollection, Me.FRUIT) = 0 Then _
strCollection = strCollection & Chr(34) & Me.FRUIT & Chr(34) & ","
End Sub
Private Sub FRUIT_GotFocus()
Dim thisFilter As String
If Len(strCollection) > 0 Then
thisFilter = Left(strCollection, Len(strCollection) - 1)
With Me.FRUIT
.RowSource = "SELECT Table2.FRUIT FROM Table2 Where Fruit Not In (" & strCollection & ")"
.Requery
End With
End If
End Sub
Private Sub FRUIT_LostFocus()
Me.FRUIT.RowSource = "SELECT Table2.FRUIT FROM Table2"
End Sub
@Arnel - code similar to what I was thinking, but what if the user deselects an item?
And I would use the form_beforeupdate event rather than the fruit_afterupate event - user selects the wrong item, goes back to change to the correct item. Wrong item no longer in list.
you are right.
we will use the BeforeUpdate event of the combo and not the AfterUpdate to get the OldValue of the combo.
replace: Chr(34) & OldValue & Chr(34) & "," With ""
and add the Value, same on the code.
or we can use the Form's AfterUpdate to add the item, since this will be the final event.
or maybe use Collection object to easily remove the item.
Wow, thanks for the replies, I'm gonna have a look at the code now, just for your info, I'm not giving the user the option to deselect and of the records, as there will only be a maximum of 12 needed, so I'll remove that variable from the equation.
here is a much better approach, put in your form's VBA:
Code:
Option Compare Database
Option Explicit
Dim strCurrentFruit As String
Public Function UpdateFruitCombo(ByVal bolFilter As Boolean)
Dim rs As DAO.Recordset
Dim strFruitCollection As String
strFruitCollection = ""
If bolFilter Then
' tblNameFruits on this example is the RecordSource of the Bound Form
With CurrentDb.OpenRecordset("tblNameFruits", dbOpenSnapshot)
If Not (.BOF And .EOF) Then .MoveFirst
While Not .EOF
strFruitCollection = strFruitCollection & Chr(34) & !FruitName & Chr(34) & ","
.MoveNext
Wend
End With
If strCurrentFruit <> "" Then
strFruitCollection = Replace(strFruitCollection, Chr(34) & strCurrentFruit & Chr(34) & ",", "")
End If
If Len(strFruitCollection) <> 0 Then
Me.FruitName.RowSource = "select FruitName from tblFruits Where FruitName Not In (" & strFruitCollection & ")"
Else
Me.FruitName.RowSource = "select FruitName from tblFruits"
End If
Else
Me.FruitName.RowSource = "select FruitName from tblFruits"
End If
Me.FruitName.Requery
End Function
Private Sub Form_Current()
strCurrentFruit = Me.FruitName & ""
End Sub
on the "FruitName" combo's OnGotFocus Event:
Code:
=UpdateFruitCombo(True)
on LostFocus() Event:
Code:
=UpdateFruitCombo(False)
this guarantee that user can change previous selection of Fruit.