none
Combobox isn't showing the values similar to the other column. RRS feed

  • Question

  • Hi,

    I have a ExempName column in the OrdersDetail subform. It is FK in the OrdersDetail table reference to the PK in the ExempName table. I am selecting ExempName for each PartDescription using combobox.

    Earlier, I was using following code in the On Got Focus event of the combobox; however, the selected values weren't displaying in the ExempName column.

    Me.ExempNameID.RowSource = "SELECT ExempNameID, ExempName, ExempQty FROM ExempName WHERE ExempName Like '*" & Me.PartDescription.Value & "*' ORDER BY ExempNameID"

    As per expert advice, I moved the code from On Got Focus event to On Load event of the subform. Now the values are displaying in the ExempName column when the form loads.

    However, the combobox list doesn't show the ExempNames matching to the PartDescription. For example, when I type the PartDescription "Bearing" so the combobox should display the ExempNames in the list matching to the PartDescription "Bearing".

    I also tried below code in order to refresh the combobox list but it doesn't work.

    Private Sub ExempNameID_GotFocus()

    Me.ExempNameID.Requery

    End Sub

    I need experts advise to resolve this issue.

    Thanks in advance.

    Monday, June 26, 2017 3:47 PM

Answers

  • Create a query based on tblOrdersDetail and tblExempName.

    Join them on ExempNameID.

    Double-click the join line and select the option to return ALL records from tblOrdersDetail. Then click OK.

    Add all fields from tblOrdersDetail to the query grid, plus the ExempName field from tblExempName.

    Save this query, and set the Record Source of the OrdersDetail (sub)form to this query.

    You can now create a text box bound to ExempName, and place it on top of the combo box.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 28, 2017 7:10 PM

All replies

  • Use the code

    Me.ExempNameID.RowSource = "SELECT ExempNameID, ExempName, ExempQty FROM ExempName WHERE ExempName Like '*" & Me.PartDescription.Value & "*' ORDER BY ExempNameID"

    in the On Current event of the form and in the After Update event of the PartDescription control. Requery won't work here because you insert the literal value of PartDescription in the row source.

    Private Sub Form_Current()
    Me.ExempNameID.RowSource = "SELECT ExempNameID, ExempName, ExempQty FROM ExempName WHERE ExempName Like '*" & Me.PartDescription.Value & "*' ORDER BY ExempNameID"
    End Sub

    Private Sub PartDescription_AfterUpdate()
    Me.ExempNameID.RowSource = "SELECT ExempNameID, ExempName, ExempQty FROM ExempName WHERE ExempName Like '*" & Me.PartDescription.Value & "*' ORDER BY ExempNameID"
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, June 26, 2017 5:36 PM
  • Thanks for responding, I tried your suggested code in the On Current event of the form and in the After Update event of the PartDescription.

    The code is working and the combobox is showing similar values to the PartDescription. Also, the values are displaying in the ExempName column.

    However, when I move the cursor to the previous rows to change the values, the values in the ExempName column become hide.

    Wednesday, June 28, 2017 12:11 PM
  • Do you have a continuous (or datasheet) form?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 28, 2017 2:09 PM
  • I have a OrdersDetail datasheet subform. These controls are in the OrdersDetail subform.
    Wednesday, June 28, 2017 2:20 PM
  • This won't work in a datasheet subform. You'll have to convert it to a continuous form. With a bit of work you can make it look like a continuous form.

    Place a text box bound to the ExempName field on top op the combo box, so that it covers the entire combo box except for the dropdown arrow.

    If ExempName is currently not included in the Record Source of OrdersDetail, you'll have to add it.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 28, 2017 2:35 PM
  • I have converted OrdersDetail subfrom from datasheet to continuous form. However, the values in the ExempName column still aren't displaying.

    When I move the cursor in the ExempName colum then it shows the selected values from combobox.

    Wednesday, June 28, 2017 5:28 PM
  • Have you placed a text box bound to ExempName on top of the combo box?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 28, 2017 5:35 PM
  • There is no ExempName control in the OrdersDetail table. Actually, ExempName is a table. The OrdersDetail have ExempNameID it is FK in the OrdersDetail table reference to the PK in the ExempName table.

    I have a combobox in the OrdersDetail subform which is bound to this ExempNameID. See below both tables structure.

    tblOrdersDetail

    OrdersDetailID (PK)

    OrderID (FK)

    PartNumber

    PartDescription

    OrderQty

    UnitPrice

    ExempNameID

    tblExempName

    ExempNameID (PK)

    ExempName

    ExempQty

    Unit

    Wednesday, June 28, 2017 5:44 PM
  • Create a query based on tblOrdersDetail and tblExempName.

    Join them on ExempNameID.

    Double-click the join line and select the option to return ALL records from tblOrdersDetail. Then click OK.

    Add all fields from tblOrdersDetail to the query grid, plus the ExempName field from tblExempName.

    Save this query, and set the Record Source of the OrdersDetail (sub)form to this query.

    You can now create a text box bound to ExempName, and place it on top of the combo box.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 28, 2017 7:10 PM
  • Thanks, it works.
    Thursday, June 29, 2017 11:34 AM