VBA behind a Combo Box

[Phil H]

This question involves using VBA behind a Combo Box. The Combo Box is bound to table CI and a field called APlayer. The search is on a field called LastName in another table PL. The objective is to get a second field, PlayerName from PL, stored in APlayer in the table CI. That seems to work OK, but the Combo Box on the form continues to display LastName, not the value in the bound field, PlayerName. I’ve even used code in an AfterUpdate event to search PL and place PlayerName in the form field with the command Me!APlayer = PL(“PlayerName”). That doesn’t work either and there are no error messages. I must be violating some basic rule about Combo boxes. Anyway, any help would be appreciated.
Phil H

[Gicu]

Hi Phil,

If I understand correctly what you want to achieve I don't think you need any VBA. For your bound combo box (to APlayer) set the Row Source property to "Select [Last Name],[PlayerName] FROM PL Order By [Last Name];", RowSourceType to Table/Query, Column Count to 2 and Column Widths to 0;3" and finally List Width to 3".

Cheers,

[Phil H]

Thanks Vlad, but it still didn't quite work. I set up the combo box exactly as you suggested, but when I open the form with focus on the combo box and start typing a name (expecting last name) it starts displaying the full name (playername). i.e. I start typing "b r o" for Brown, but it comes up with Bill.... It looks like that is because of the column counts of 0",1"...I guess? When I changed it back to 1",1" it works the way it did before, stores player name in the table, but leaves lastname in the combo box. When I click on the down arrow in the combo box (with a width of 3") it displays both the lastname and the playername, but I don't really want the user to have to do that. Anyway, I'm using a workaround for the time being so it's not crucial to my work. Again, thanks for your help.
Phil H

[davegri]

This question involves using VBA behind a Combo Box. The Combo Box is bound to table CI and a field called APlayer. The search is on a field called LastName in another table PL. The objective is to get a second field, PlayerName from PL, stored in APlayer in the table CI. That seems to work OK, but the Combo Box on the form continues to display LastName, not the value in the bound field, PlayerName.

See the red. That's the confusing point to me.
Combobox can't work two ways like that. I suggest putting a bound textbox on the form to display the PlayerName after the unbound combobox lookup, like below attachment.

PhilH-Players-davegri-v01.zip

[Gicu]

Can you try to change the bound column from 1 to 2?

[Gicu]

Hi Phil, I think I get what you want to achieve. If Dave's solution doesn't work you can try this approach.: set the combo to store and display player name when you are just looking at the form (save that SQL as the default rowsource). Then in the OnEnter event change the resource to another SQL statement that has Last Name as its first visible row (something like Select PlayerName,[Last Name],playerName from PL; with column widths 0;1;2); that will allow you to search by last name,view the full player name and store it. Finally in the OnExit event of the combo reset its rowsource to the original set to display the bound field.
Cheers.

[Gicu]

Hi Phil and Dave,
Here is what I had in mind, it seems to work for most parts but autoexpand doesn't....
Sorry Dave for using your db!
Cheers,

[davegri]

PhilH-Players-davegri-v02.zip
Autoexpand didn't work in my original V1. Autoexpand should be automatic. When it doesn't work, I recreate the combobox from scratch and then it does. Here's V2.

Vlad- no problem! That's what it's for. Getting doggy around here, huh....

[Phil H]

See the red. That's the confusing point to me.
Combobox can't work two ways like that. I suggest putting a bound textbox on the form to display the PlayerName after the unbound combobox lookup, like below attachment.

PhilH-Players-davegri-v01.zip

I think I'll just go with the bound textbox approach to display PlayerName. Thanks to all who responded.
Phil H