ComboBox Data in a Text Box

Question

I have a table named Personnel that consists of the following columns:

• PersonnelId (AutoNumber primary key)
• LastName (Short Text)
• FirstName (Short Text)
• MI (Short Text)

I also have a table named Ticket with the following columns:

• ID (AutoNumber primary key)
• Employee (Number)

The Employee column is populated with a lookup on the Personnel table like so:

I've created a form (TicketForm) with only two fields: A combo box (cmbEmployee) and a text box (txtEmployee).

The form's record source is:

The combo box (cmbEmployee) properties are:

The text box (txtEmployee) properties are:

When I open the form I get the employee name value in the combo box and when a value is selected, the field PersonnelId is displayed in the text box:

What I want to do is have the text box populated from the displayed value, not the bound value.

I have found where this question was asked on a different site and it seemed like this was the answer I was looking for, but I put in the text box =AssignedTo.Column(1) all I get is #Name? in the text box:

What am I doing wrong? Is it even possible to accomplish this?

Thanks in advance and my apologies for the detail of this question making it so verbose!

---

MCSE + I

Answer 1

Hi. Make sure the Column Count property of your Combo is set to 2.

Answer 2

Although I'm not sure why you want to display the value twice, try setting the text box's controlsource to =cmbEmployee.column(1) rather than AssignedTo.column(1).  

-Bruce

Answer 3

Thanks Bruce! I think I made my example a bit to simplistic. You are correct that I would not be doing that on the same form. Let me modify my example slightly.

Instead of one form, lets say I have two forms; one to enter the employee names, and the other will display the names. Here is what the second form (TicketForm2) has in it's Data properties:

When on TicketForm, I select Brown, John, B as the employee on record 2.

When I switch to TicketForm2 with the textbox, I get the bound value, not the displayed value:

All I am trying to do is get the displayed value in the textbox on TicketForm2.

If that is not something that can be done with the textbox control, how can I do it (besides making the textbox a combo box or a list box)? 

---

MCSE + I

Answer 4

Hi Daniel,

If truly all you need is to display the value shown in your combobox on TicketForm, then you simply need to extend the textbox's controlsource to include the form that the combobox lives on.  So instead of setting the textbox controlsource to simply

=cmbEmployee.column(1)

you would want to also specify the form that it's coming from, i.e.

=Forms!TicketForm!cmbEmployee.column(1)

-Bruce

Answer 5

Hi Daniel. If the second form is bound to the same table as the first form, then the safest approach is to also use a Combobox on the second form. Otherwise, you can bind the second form to the Employees table. However, I suspect the Textbox may be bound to the EmployeeID, which means it will be a number. If so, you're back to using a Combobox. You can also display the employee's name if you use an unbound Textbox. Hope it helps...

Answer 6

All I am trying to do is get the displayed value in the textbox on TicketForm2.

For the RecordSource property of TicketForm2  use a query which joins the Ticket and Personnel tables on Employee = PersonelID.  In the query return the concatenated string expression to show the full name in a computed column, named FullName say.  Set the ControlSource property of the text box to FullName.

---

Ken Sheridan, Stafford, England

Answer 7

Hi Daniel,

First, my english in very poor ;-), sorry.

Another way to do...
Are you tried with a Public Function, for example...

Put this function in a independent module and after on the controlSource of TextBox put :

=SearchString(cmbEmployee.value)

[CODE]

Public Function SearchString(idd As long) As String
   Dim cadena As String, RecReg As DAO.Recordset
   cadena = "SELECT Personnel.MI FROM Personnel"
   cadena = cadena & " WHERE (((Personnel.PersonnelId) =" & idd & "));"

   Set RecReg = CurrentDb.OpenRecordset(cadena)
   If RecReg.RecordCount = 0 Then
      SearchString = ""
   Else
      SearchString = RecReg!MI
   End If

End Function

[/CODE]

Hope it helps...