Unable to Display Results of Combobox in Subform Based on Results of Combobox in Main Form 1

[JOEYB99]

I am using Access 2010.

I have a combobox in a continuous form view of a subform that is based on the results of a combobox in the main form.

Everything works fine input-wise! I am able to populate all the controls/fields as intended. However, when I view the records the subform combobox is blank some of the time and yet the proper data is all there, as per the underlying table.

Apparently this is a known problem when 3 conditions exist, as per Microsoft.

1. The form is a continuous form, or it is open in Datasheet view so that you can view multiple records.
2. The combo box's RowSource property is set to a parameterized query to limit the number of rows in the combo box based on criteria in another field in the form.
3. The combo box criteria for the current record eliminate the rows that were selected in the other records.

Microsoft Access maintains only one query recordset for a combo box rather than one recordset for each combo box in each record.

When the criteria for the query that the combo box is based on change, rows that were selected in the other records may be eliminated from the recordset. As a result, the non-BoundColumn values are no longer available to be displayed.
RESOLUTION
Do not hide the BoundColumn field. If you want to see a different column, use the DLookup() function or the AutoLookup technique to display the information you want in another control on the form.

I am having trouble understanding this workaround, and was wondering if someone could please help me out. I did not provide any details and was hoping someone could explain the technique here.

One suggestion I read about said "You have to have the bound columns showing, you can't set the first column, assuming that's the bound to 0, you have to paste a label over it to try and hide it, then use another combo which takes the second column of the second combo as it's record source, it's fiddly and not perfect, but close"

 

[MajP]

This is how I do it. This assumes that you want a combo that is limited to the list.

1) add a textbox
2) add a corresponding combobox
3) set up your combobox.

In this example you are displaying the employee last name in the combobox but the bound field is the employee ID which is hidden. This is easier if the thing that is visible is the bound item.

4) Make a function . You could do this in a calculated control but that gets very very difficult to debug.

In my example I want to display the employee last name. This is the same thing I see in the combobox

Public Function GetDisplay(EmpID As Variant) As Variant
  Const Expr = "[LastName]"
  Const domain = "Employees"
  Dim Criteria As String
  If Not IsNull(EmpID) Then
    Criteria = "[EmployeeID] = " & EmpID
    GetDisplay = DLookup(Expr, domain, Criteria)
  End If
End Function

5) So in my combo I am selecting an employee id (bound and hidden) and storing in the table. Now I want my textbox to show the employee last name as well. Make the control source of the textbox
=getdisplay([employeeID_FK])

6) need to refresh the form after selecting from the combo

Private Sub CmboEmployee_AfterUpdate()
 Me.Recalc
End Sub

7) You can play with the formatting to get the effect you want. I just shrink the combo down until only the arrow is showing. I place the textbox up against the arrow so that it now looks like a combo.

The only issue is that the drop down appear to the right. You can probably play with the formatting and adjust the size of the combo and list width.

 

[MajP]

Here is how to get the format correct. Make the textbox the exact size of the text portion of the combobox (no down arrow). Then lay the textbox over it aligned to the right overlapping the down arrow. Not sure why but when this renders with the arrow is visible.