Capturing the correct displayed combobox data (1 Viewer)

[nector]

I want to capture the correct displayed combo box data see the screen shoot below"



How do I do it , I can tell you I have worked so hard to reach at this point in terms of filtering a combox in Ms Access all I need now is to capture the correct data in the combo box . Example the firstname must be inserted in the combo box not last name

VBA code I'm using:

Option Compare Database

Option Explicit

Private Sub EmployeeID_Change()
Dim rstBig As ADODB.Recordset
Dim rstFilter As ADODB.Recordset
Dim strPerm As String
'With each valid change the combo is requeried against the table source.
'A data hit with each keypress

    Dim intLen As Integer
   
    intLen = Val(Nz(Me.txtReq))
   
    If Len(Me.EmployeeID.Text) >= intLen Then
        Me.EmployeeID.RowSource = "SELECT FirstName,LastName FROM tblEmployees WHERE FirstName Like '" & Me.EmployeeID.Text & "*'"
        If Me.EmployeeID.ListCount > 0 Then
            Me.EmployeeID.Dropdown
        End If
    ElseIf Len(Me.EmployeeID.Text) < intLen Then
        Me.EmployeeID.RowSource = ""
    End If
End Sub

Private Sub Form_Current()
Me.txtReq = 3
End Sub

Sample database is also attached , I will highly appreciated your assistance in resolving this issue , I think I'm almost there.

 

[MajP]

You cannot do that in a continuous form as designed. If you set the rowsource to "" at any time then all previous records will appear to disappear. Is you list really that long that you need to limit the names returned or can you start with all names and filter down? If you want to do this as you are trying you will have to do this with a tabular form. If you make the subform tabular I can demonstrate.

 

[raziel3]

If I'm reading this right you want to cascade the combobox based on a textbox in the form's header (the logical place to put said textbox, in a continuous form design)?

 

[MajP]

If I'm reading this right you want to cascade the combobox based on the a textbox in the form's header (the logical place to put said textbox in a continuous form design)?

It is not a "cascading combobox", but a Find/filter as you type combobox in the continuous form that begins the filter after a set number of characters. This is problematic to start with because as you limit the list all previous records will go blank. Same issue as a continuous cascading. There are work arounds see.

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

However that will not work here. In the above example on the exit event you return all records to the rowsource. The OP wants to set the rowsource to "".
This can be faked if this is a tabular form. You would put a textbox in front of the combobox.
Here is a demo of a FAYT in a continuous form.

 

[nector]

Thank you so much I will follow you solutions

 

[raziel3]

It is not a "cascading combobox", but a Find/filter as you type combobox in the continuous form that begins the filter after a set number of characters. This is problematic to start with because as you limit the list all previous records will go blank. Same issue as a continuous cascading. There are work arounds see.

Yes it can be problematic @MajP and so far @CJ_London method in my opinion, is the best solution. I often use that together with your FAYT class module in my form designs.

the op can also look at this

https://www.access-programmers.co.u...us-record-data-displayed.322776/#post-1821758