combo box, search by second column but select value of first

Question

Hi,

I have a combo box that offers about 100 rows from which a user must choose a value.  Two columns are displayed - ID, Friendly_name.  The combo box is bound to the ID (1st) column.  To make it easier for users I wanted them to be able to type the friendly name and have the combo box scroll to the nearest match, like an autocomplete field.  However, when a row is selected from the combo box results the ID value is captured.

Is this possible?

Thanks,

Charles

Answer 1

Set the Column Widths property of the combo box to 0";1"

This will hide the first column. The user can type some letters from the friendly_name and the combo box will auto-complete it. The value of the combo box will be the ID value from the first column.

---

Regards, Hans Vogelaar

Answer 2

You can also try setting the column width to just 0

You then have the 2nd (1st visible) column auto fitted rather than force a specific width. There are times when this 'looks' better...

Cheers,

Brian.

Answer 3

Hi Hans, sorry for the late reply.  That almost works.  The value that is committed to the underlying table is indeed the ID, but the value displayed in the combo box is the friendly_name.  Is there any way to have it display the ID, but have the friendly_name field searchable?

Thanks

Answer 4

That is not possible, as far as I know, but you could add a (locked) text box bound to the ID field. The combo box will display the friendly_name and the text box the ID.

---

Regards, Hans Vogelaar

Answer 5

Dear Hans,

I know this is pretty much a done thread, but as you will from helping me with other threads I have changed this subform to a continuous form.  This is working really well actually.  However, this particular solution of setting the width of the first column to 0 doesn't work now that I have changed it to a continuous form.  If I do that, e.g. 0cm;6.5cm, I cannot see any value displayed.

Is there a way round this as it was working quite well.

Thanks,

Charles

Answer 6

Are you requerying the row source of the combo box depending on another value?

---

Regards, Hans Vogelaar

Answer 7

Are the field names in the RowSource in the original order?

The Field set to 0" needs to be first and the field you want to see should be second.

Or in the Query design view if you turned off the check mark...

---

Chris Ward

Answer 8

Hi Hans and KCDW,

Sorry for the late response, I broke early for the weekend and then the sun shone for probably the last time till next April... so I played a lot of golf.

Back to access.  Right, I have looked at this again and have noticed a pattern.  This is definitely not a problem with a datasheet form.  The behaviour is quite bizarre and I would be amazed and in awe if anyone could work it out, but here goes.  What happens is the cboProduct value is blank only when:
A) change to a specific cboProject (Header control) value ("0" | "None"), from another
B) and you then hit the Refresh button, navigate away from and back to the record, or enter a row on the continuous subform.

Design elements
Header field: cboProject
Subform field: cboProduct
cboProduct rowsource: "SELECT DISTINCT lupAll_and_Projects_Products.Product, tblProducts.Description
FROM lupAll_and_Projects_Products INNER JOIN tblProducts ON lupAll_and_Projects_Products.Product = tblProducts.ID WHERE (((lupAll_and_Projects_Products.Proj_ID)=[Forms]![nfrmMaster]![NavigationSubform].[Form].[Project]));"
lupAll_and_Projects_Products: "SELECT Product, Proj_ID FROM lupAll_Products UNION ALL SELECT Product, Proj_ID FROM lupProjects_Products;"
lupProjects_Products: "SELECT tblProjects_Header.ID AS Proj_ID, tblProjects_Products.Product FROM tblProjects_Header INNER JOIN tblProjects_Products ON tblProjects_Header.ID = tblProjects_Products.Project ORDER BY tblProjects_Header.ID, tblProjects_Products.Product;"
lupAll_Products: SELECT "0" AS Proj_ID, tblProducts.ID AS Product FROM tblProducts;

What I am trying to do is have a list returned based on the cboProject value, but the tricky thing is that there is not always a Project and in which case all Products need to be offered, and the mandatory cboProject field is set to "0" | "None".  So from the queries above you can see that a list of all product IDs and a project_id of "0" are generated by query lupAll_Products.  Another list or all products in the tblProjects_Products table by query lupProjects_Products.  These are linked together by lupAll_and_Projects_Products and filtered byt Header Form's cboProject value.

Behaviour
I am not sure this is 100%, but it gives a good idea of what I am seeing without documenting every single observation.
> Create new Sales Order record and set cboProject to "0" | "None", and select a product from the filtered list offered by cboProduct on the subform.  Navigate away from and then back the record, hit Refresh and the Product description is displayed as desired in cboProduct. All good so far.
> Change cboProject to another value, e.g. "2" | "Audi A5 Coupe"
> Hit Refresh, navigate away from and back to the record, or enter a row on the continuous subform.  The cboProduct control goes blank.
>Change it back to "0" | "None", and the cboProduct displays column 2 from its rowsource (although it is bound to row 1 using row width 0cm;5cm).
> Navigate away from the record and then back to it, cboProduct still displaying, hit Refresh, cboProduct is blank.  However, I have repeated this particular step and the behaviour is inconsistent.

Please help, this is such a silly little problem but understandably users will hate it.

Thanks,

Charles

Answer 9

Hi KCDW, the field set to "0" is turned off in the query.

Thanks,

Charles

Answer 10

Dear Hans,

I am not sure I understand.  This is what I am doing in the Code Behind:
--------------------------------------------------------------
Private Sub cboProject_AfterUpdate()

If cboProject <> 0 Then
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim filt_ID As Long
   
    filt_ID = cboProject.Value
   
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select Customer From tblProjects_Header Where ID = " & filt_ID & "")
    rs.MoveLast
    cboCustomer = rs!Customer
    rs.Close
    sfrmSales_Orders_Detail!cboProduct.RowSourceType = "Table/Query"
    sfrmSales_Orders_Detail!cboProduct.RowSource = "lupSales_Orders_Products"
    'sfrmSales_Orders_Detail!cboProduct.Requery
    txtCustomer_PO.SetFocus

Else

    'cboCustomer.Value = Empty
    Me!cboProject.Requery
    Me!cboCustomer.Requery
    sfrmSales_Orders_Detail!cboProduct.RowSourceType = "Table/Query"
    sfrmSales_Orders_Detail!cboProduct.RowSource = "lupSales_Orders_Products"
    'sfrmSales_Orders_Detail!cboProduct.Requery
    cboCustomer.SetFocus

End If

End Sub
--------------------------------------------------------------

Whether cboProduct is requeried doesn't seem to make a difference.

Thanks,

Charles

Answer 11

Oh what an idiot!!!  If the cboProject value is changed to "2" | "Audi A5 Coupe" and the Product IDs that exist on the current related records of the subform are not in the rowsource... the values are blank!!  Obviously, can't believe I spent so long working that out.

I will create a vbYesNo when changing this value, and deleting all related rows in the subform if cboProject is changed to a value other than "0".

Regards,

Charles

Answer 12

It would be nice to know though why Datasheet view can handle the requery automatically whereas a continuous form doesn't?