requery combo box after selection

[kobiashi]

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

 

[isladogs]

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

 

[CJ_London]

the issue you have is similar to cascading combos see this link about cascading combos in a continuous form

https://www.access-programmers.co.uk/forums/showthread.php?t=291269

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?

 

[arnelgp]

you need VBA on your Form to do that.
on this example the textbox is FRUIT:

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

 

[CJ_London]

@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.

 

[arnelgp]

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.

 

[kobiashi]

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.

Sent from my ONEPLUS A6003 using Tapatalk

 

[arnelgp]

to be safe, use the forms beforeupdate event i stead of the control's.

 

[arnelgp]

here is a much better approach, put in your form's VBA:

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:

=UpdateFruitCombo(True)

on LostFocus() Event:

=UpdateFruitCombo(False)

this guarantee that user can change previous selection of Fruit.