Combo box lookup and Text box display Q 1

[stillwillyboy]

I have a table Tbl_SalesNameAndNumber with the fields SalesNumber and SalesName. Info is as follows:

SalesNumber SalesName
1 Joe
2 Bob
9 House

On my form Frm_SalesName, I have a Combobox named cbo_SalesNumber. It works by showing me the different #’s to select: 1,2 or 9. It’s RowSourceType is Table/Query and its RowSource is Tbl_SalesNumberAndName. So far, so good.

I have text box (not combo) to display the SalesName, Selecting 1 in cbo_SalesNumber, should give me Joe in the text box, 2 for Bob, 9 for House. Seems like pretty standard stuff, but all I get in my text box is the number I selected in the combobox. I need the name to appear. I have tried SQL in the text box property, but lost what I typed somewhere along the line. I tried the following in the AfterUpdate for the text box.

Private Sub SalesNumber_AfterUpdate()
SalesName = DLookup("SalesName", "Tbl_SalesNumberAndName", _
"SalesNumber" & Me.SalesNumber)
End Sub

If someone could please tell me what I use and where I put it to get the SalesName to appear in the text box, I would be greatly appreciative.

TIA

Bill

 

[TSIsolutions]

Hi StillWillyBoy,

In order to get the name of the SalesName in the text box you need to send the second column information of your ComboBox to your TextBox. In order to do so type the following in the AfterUpdate procedure of your ComboBox:

Private Sub SalesNumber_AfterUpdate()
SalesName = Me.cboSalesNumber.Column(1)
End Sub

The (1) references the secong column since the first column is assigned (0). The reason why you get the number instead is because the column with the Sales Numbers in the ComboBox is the Bound Column so any reference to this ComboBox will default the Bound Column.

Hope this helps.

Lawrence.

 

[stillwillyboy]

Lawrence,

Thanks. It works when I use the mouse to select the SalesNumber, but when I enter the number with the keyboard and press "enter", the Name appears in the text box but I also get an error message telling me that it cannot find the macro named "."

Thoughts?

TIA

Bill

 

[TSIsolutions]

Hi stillwillyboy,

When does the error message occur? Right after you type in the first digit or after you press enter or tab out? Is there additional code in your AfterUpdate event? The code I gave you is pretty straight forward. Is your BoundColumn the SalesNumber field? Make sure also that you set your LimitToList property in your ComboBox is set to YES. Also, make sure that the Combo Box doesn't have any other code running on it (especially in the OnChange event).

Hope this helps. Let me know.

Lawrence

 

[stillwillyboy]

Lawrence,

The error appears after I tab out or press enter. The correct info appears in the text, then the error message appears.

I do not see any code in the AfterUpdate Event.

The BoundColumn is 1, which is the SalesNumber field in the table. The Limit To List is set to Yes.

There is nothing in either OnChange Event.

The error is telling me that it can't find the Macro "." "The Macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved."

I have not created any macros for this.

Thanks, Bill

 

[TSIsolutions]

Hi Bill,

I retested the scenario and couldn't find any problems. However, sometimes Access reacts in ways that doesn't always make sense so what I would do if I were you is to delete the ComboBox you are using right now and create a new one with the same parameters. If it still doesn't work test it on a new form.

The process of elimination should give you an idea of where the problem comes from.

Let me know,
Lawrence

 

[stillwillyboy]

Will do this week.

Thanks