textbox on form populated from other table (1 Viewer)

[cochese]

Hopefully that subject is accurate.

I have a Form that is populated off Table A. Table A has employee information (name, department, store, date of hire, etc). The store field holds the store's code number, which is related to Table B which has the store code number and the store's name and address.

The form has three optional search criteria boxes (which I picked up from http://allenbrowne.com/ser-62.html). Depending on what search criteria is selected by the user, the form filters the records. One of these search boxes is used to locate a store (this is a combo box).

When the store search box is selected it displays the store's name and not its code number. However, in the result boxes it displays the store's code number (this is because the results are from Table A, and the store field in Table A only stores the code number).

I need the result box to show the store name not it's number. The only method I could get working was using a DLookup function, but this is slow (all the other fields appear while the store field populates a split second later), and it displays #Error when no selection is made in the Store search box.

I also tried making a query that returns the store's name based off the selection in the store search box. When I set the store's result textbox to this query it just gives a #Name error.

Any ideas how to do this?

 

[PNGBill]

Combo Boxes / List Boxes can use a Table or a SQL/Query for the list.
SQL/Query allows you to pull in data that is related but in another table or even Derived Fields eg table has FirstName and LastName, your query/SQL can have FullName and that can be selected or used as the control record.

When you setup the ListBox/Combo Box you can set what will be displayed it the Selection and what will populate the control.
Check the Properties. Format of the control.

 

[vbaInet]

Copy your Store combo box and replace it with your result textbox. Rename this copy to the name you gave your result textbox. Make sure to set the Control Source too.

 

[cochese]

Let me try to clarify. The form has a combo box search field, which allows the user to select a list of stores to search. The same form also has a text box that displays the store the employee works in.

The combo box displays the name of the store (not it's number) but when results are shown the text box displays the store number (because the results are being pulled from Table A which lists just the store number...but is linked to Table B which has the store number as the primary key and the store name as an additional field).

Everything works fine, except for the fact that the text box that displays the results needs to show the store name and not the number.

Any better?

 

[vbaInet]

Like I said, copy the combo box and replace it with your textbox if you want to display the corresponding name. If you still want to display the name in a textbox, you can hide the combo box and set the Control Source of the textbox to the column where the name resides - which I presume will be the second column, i.e. Column(1).

=ComboBoxName.Column(1)

 

[cochese]

I'm sorry to be daft. This line "copy the combo box and replace it with your textbox if you want to display the corresponding name" makes no sense to me. I don't want to reflect what is in the combobox...the combobox is there to select a search criteria. The textbox displays the store field of the record(s) returned. The problem is while the combo box shows the store name (the first, and hidden column of the combobox is the store number, while the second column is the store name), the textbox shows the store number. I want it to display the store's name.

Is there anyway to hide the drop down button of a combo box? That would work.