Text Box filling in after combo box selected, need to sort on split form

[Renee R]

This is a very simplified version of what I'm trying to do...



I have a table [Employees] that has the field [FirstName] and [LastName]. On my form [Jobs], I have a combobox [ComboWho]. The combo box has all of the possible first names. When a first name is picked, it makes the last name fill in a textbox.

In the real version, it is a [LastName] is a lot of information, so it is kept in a memo field. I tried to use cascading combo boxes, but it cuts off my text.

I keep getting it to almost work... I've tried about 5 different methods... DLookUp, subform, etc.

Right now, I'm using this code:

Private Sub ComboWho_Change()
Dim location As String
Me.ComboWho.SetFocus
location = DLookup("LastName", "Employee", "FirstName = '" & Me.ComboWho.SelText & "'")
Me.MyTextBox.SetFocus
Me.MyTextBox.Text = location
End Sub

It has properly filled in the textbox. I thought it was working perfectly, but ran into one snag... We have been using a split form... and it won't let me filter that column (Column ComboWho). I think because it is saving everything as numbers instead of text.

ComboWhoDatabase.accdbI've attached a copy of the simplified database... any help would be appreciated.

Thanks!
Renee

[June7]

Don't understand what is meant by 'kept in a memo field' - why would LastName be a memo field? It is not in the posted database.

Yes, the DLookup doesn't work because the actual value of combobox is the EmployeeID, not FirstName. Text is the wrong property anyway, should be Value. But since Value is the default property of data controls, can be omitted. Also, SelText does apply here, Value would but can be omitted.
Me.MyTextBox = DLookup("LastName", "Employee", "EmployeeID = " & Me.ComboWho)

Why is selection made by first name? What if you have several Robert or James? Consider this as combobox RowSource:

SELECT [EmployeeID], [LastName & ", " & [FirstName] AS EmpName FROM Employee ORDER BY [LastName], [FirstName];

If you must search by FirstName, then combobox RowSource:

SELECT [EmployeeID], [FirstName] & " " & [LastName] FROM Employee ORDER BY [FirstName];

No code and no textbox needed for either.

However, if you want textbox:

SELECT [EmployeeID], [FirstName], [LastName] FROM Employee ORDER BY [FirstName];

Then expression in textbox:

=[ComboWho].[Column](2)

Column index begins with 0. LastName is column 3 so index 2.

Again, no code.

The Who field in Jobs table should be number type because the EmployeeID primary key is an autonumber. Related PK and FK fields must be same type - in this case, autonumber and number.

[Renee R]

Don't understand what is meant by 'kept in a memo field' - why would LastName be a memo field? It is not in the posted database.

Sorry... I tried to make a simplified version of what I'm trying to do, so that it would be easier to spot the mistakes. I've updated the posted database to reflect what I'm trying to do. In one of my text boxes, it will show LastName. This isn't the problem, I can do that. The problem is that in another text box, I want it to show the EmployeeInfo, which is a lot of information... so it has to be kept in a memo field. I've made these changes.

Why is selection made by first name? What if you have several Robert or James?

In the real database, each item has a unique name, so there is no worry about several Roberts or James.

However, if you want textbox:

SELECT [EmployeeID], [FirstName], [LastName] FROM Employee ORDER BY [FirstName];

Then expression in textbox:

=[ComboWho].[Column](2)

Column index begins with 0. LastName is column 3 so index 2.

Again, no code.

I have tried this... however, because the data I want displayed is in a memo field, it truncates it. I need to find a method that will show more text.

The Who field in Jobs table should be number type because the EmployeeID primary key is an autonumber. Related PK and FK fields must be same type - in this case, autonumber and number.

Is there any way to get it to save from a combo box as the text that was selected, not the primary key? I don't want to have to loop up what each number means when I'm in datasheet view.

Thanks!
Renee

[Renee R]

I've tried again... no luck!

I've erased and remade my comboboxes again. They now sort in the datasheet beautifully.

I have my combobox [ComboWho] that selects who is doing the job. Then, in the text box [MyTextBox], I've put this as the Control Source =DLookUp("EmployeeInfo","Employee","EmployeeID = " & [Me].[ComboWho])

All I get in the TextBox is #Name?

Renee

[June7]

Remove Me. from the textbox expression. The Me qualifier is only valid in VBA.

Domain aggregate expressions can be slow. Alternatively, try this as the form RecordSource:
SELECT Jobs.*, Employee.EmployeeInfo FROM Employee RIGHT JOIN Jobs ON Employee.EmployeeID = Jobs.Who;

Bind textbox to the EmployeeInfo field and set it as Locked Yes, TabStop No.

[Renee R]

It worked! removing the [Me] made it work! Thank you so much.