We help IT Professionals succeed at work.

MS Access:  Select and Display combo box value based on text box value.

Dale James
Dale James asked
on
Hello Team

Can you  please advise how it is possible to display the value of a text box to displayed in a combo box.

I have a form called frmAuditors which contains amongst others, a combo box called cboCSRLookup and a text box called txtWindowsID.

The combo box has is populated from a table called tblAuditor which contains four fields with the bound field being the first column [ID] field. The combo box actually displays the value in the Auditor Name field.

I have used the following dlookup to obtain the ID number from the tblAuditor table.

CSR = Dlookup("[ID]", "tblAuditor", "[WinID] = '" & me.txtWindowsID& "'")

The above dlookup provides the ID number which can be used to corresponded with the ID field that the combo box is bound to.

Can you please advise how it is possible, to display the in the combo box, the Auditor Name that is based on the ID value obtained from the above aforementioned dlookup? Alternatively, if there are any other or better methods of obtaining the required result, your advice would be much appreciated.

Just as a quick example:  If I input into the text box MXTHOM, this string value has an associated Auditor table ID value of 20.  This ID value will be available to the combo box via the row source. So the objective is to pass the ID value of 20 to the combo box and the combo box will then display the associated string value (item) MXTHOM.

Thanks in advance.

Sinerely

Dale
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:

Does your user "set" the Auditor by selecting a value from the combo? If so, why not just let them do that instead of requiring them to enter something into a textbox.


You can also show the user more than one column in your combo, if they need to select based on Windows ID. To do that set the following properties of your combo:


ColumnCount: Number of columns you want available in your combo - probably 3

ColumnWidths: 0;1;1 - or something like that. This would hide the first column and show the second 2.


Assuming your Combo's Rowsource includes the WindowsID (or whatever column holds the values your user will use to select the Auditor), your user should be able to find and select the right record.



Dale JamesTherapist

Author

Commented:
Hello Scott

Thanks for getting back to me.

The form is used for both input and recall.

The option has been given to allow the user to search via the drop down or by entering a unique ID  string  in the text box.  If the search is done via the drop down then it will populated the text box and if the search is triggered via a text box entry then if the ID exists it will show Auditor's detail in the combo box.  The text box is also coded to alert of possible duplicate inputs, the existence of an ID etc.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:

Still sounds like you're storing the same data in two different places (assuming your Textbox is bound), and that's not a good idea.


If the Textbox is unbound (and it should be), then you could set the value of that combo to the value returned from your DLookup, assuming that is the value your combo would be bound to. That would display whatever value is in the first visible column of your combo.


Me.YourCombo = CSR


If you're trying to show values in columns other than the first visible in your combo, then you can't do that.



Mark EdwardsChief Technology Officer
Commented:
First, we need to understand a few things:

Is your textbox bound to a form recordsource field? (controlsource set to a field)  If you're using the textbox for lookup-hopefully not bound.
If the combobox is unbound, you can simply set the combobox value the new ID and requery the combobox.

Is your combobox bound to a form recordsource field? (controlsource set to a field)  If so, then changing the form's current record will change the ID value if the combobox which will display the auditor name (you said the combobox displays the auditor name for the ID that the combobox value is set to.)  If you try to change the value of the combobox to another ID value while keeping it on the current record, all you will do is change the ID value in the record from the previous ID to the new ID - and I don't think that is what you want to do unless you really want to mess up your data.

It sounds like what you are asking is to move the current record of the form to the record for the new ID so that the combobox will display the auditor name for the new current record determined by the new ID.

I can give further guidance once I know what's going on...
Chief Technology Officer
Commented:
If you want to show text in the textbox section of your combobox that you don't "see" in your dropdown list, there is a little trick you can use.
The combobox testbox will display ONLY the text in the first non-zero width (1st "visible") column.  However, you can set the width of a column to a value that makes it so narrow, that it is invisible to the naked eye.

For example, you want the ID in the first column, which is the bound column, but you don't want it displayed in the textbox or the dropdown, so you set its width to zero.
You have 3 other columns [FirstName], [MiddleName], and [LastName], but you want to display the Full Name in the combo's textbox.  Here's what you do.  Create the Full Name column as column #2 by putting the 3 name pieces together in one column of your rowsource query, and put the 3 name piece columns as columns 3,4, and 5.
Set the width of the Full Name column to .007 (too narrow to see, but "visible" to the computer).
The dropdown will show the 3 separate name piece columns, but display the full name column in the combo's textbox when you select something.
Dale JamesTherapist

Author

Commented:
Hello Scott and Mark

Thank you very much for your responses.

Just to confirm.  The text box and combo box are both unbound. Both retrieve the data via SQL and  any entries that are to be saved  to the table  are also saved via SQL,  I just wanted to keep the form as compact as possible to allow the text box to be used as a search facility which in turn checks to ensure that the ID entered doesn't already exist, if it does it retrieves the record linked to the ID allows the user to proceed to create a new record with the new ID entered.  If the ID already exists, this is where I required the code;  me.cboCSRLookup = CSR. instead of the Requery method when recalling the ID record (so simple in comparison to the code I was writting)

Scott:  The data is only being saved to one table and you are correct, it wouldn't be something I would choose to do.

Mark:  Thank you for your excellent tip regarding column concatenations and how to display it within the combo box drop down display.

I have adjusted according the the advise you have both provided and all is working well.

Once again, thank you for your assistance and additional information.

Sincerely

Dale
Mark EdwardsChief Technology Officer
Glad to help!