Auto Fill data in textbox from Combobox selection With Number Value Text description

[smd747]

Auto Fill data in textbox from Combobox selection
Where the combobox is:
ManagerID stored as a number but displayed as text when selected
Location ID stored as a number but displayed as text when selected
Table Field s for Managers
ID AutoNumber
Manager Text
LocNo Number
Location Text
E-mail Address Text
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Table to store data
RecordID AutoNumber
Date Date/Time
ManagerID Number
(ComboBox Drawing Data from Managers Table displays Text but store Number, ID & Manger)
LocationNo
(ComboBox Drawing Data from Managers Table displays Text but store Number, LocNo & Location)
LocationNo Number
Request Text
FeeID Number
Cost Currency
TicketNo Number
<o></o>
On my from I had used a Combo box For Request that when I select item the Cost auto populates from the combo box. I used the following after update code.

[SIZE=3][FONT=Calibri]Private Sub FeeIDCombo_AfterUpdate()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri][FONT=Calibri][SIZE=3]ReAllocatedCost = DLookup("Cost", "Re-Allocated_Cost_Fee_T", "FeeID=" & FeeIDCombo)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Me.Refresh[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT][/FONT][/SIZE]

<o></o>


Which works great.
I tied the same logic with the manger and Location but could not get it to work.
Can this be done? Is it because ComboBox is Drawing Data from Managers Table displays Text but store Number, ID & Manger)?
<o></o>
I also tried making an unbound combo to store the value and tried the following code:

[FONT=Calibri][SIZE=3]Private Sub FeeIDCombo_AfterUpdate()[/SIZE][/FONT]
[SIZE=3][FONT=Calibri][FONT=Calibri][SIZE=3]Me.ManagerIDTextBox = Me![ManagerIDComboBox].Column(0) [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Me.BranchTextBox = Me![ManagerIDComboBox].Column(1)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Me.Refresh[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT][/FONT][/SIZE]

<o></o>
<o></o>
I have been at this for days. Is this possible with values that are Number but display as text?
What am I doing wrong, any incite or direction would be greatly appreciated.

 

[SydneyGeek]

You're on the right track, and it shouldn't matter in this case if the combo is bound or unbound,
I never use Me!... syntax. Try

Me.ManagerIDTextBox = Me.ManagerIDComboBox.Column(0)

...etc.
I'd argue, though, that you don't need the ManagerID text box as well as the combo... it's the same value being stored. If the combo is unbound, set its Control Source to the ManagerID field.

Denis

 

[smd747]

Denis

Thanks for the direction. Here what I have:

The ManagerIDComboBox has the managers name in column(1) and the BranchName in Column(2) the second coluum in the managersIDCombo box.
The ManagerID is in column (0) which is stored a number. I tried you suggestion. I have the combo bound to the ManagerID field

What I am attempting for the fourth day is to so simple but so complicated at the same time for me. I want to click the ManagerIDCombo box and have the BranchIDTextbox populate with the Branch name which is the 2nd column in the ManagersIDCombo. Both are stored as ID Autonumbers but displayed as textname.

Here what I did based on your suggestion.

Private Sub ManagerIDComboBox_AfterUpdate()
Me.BranchIDTextBox = Me.ManagerIDComboBox.Column(0)
Me.Refresh
End Sub

I tied Me.ManagerIDComboBox.Column(0)
,Column(1) and also Column(3)

When I run the after update code I get aCompile Error: Method or data menber not found With the Mw.BranchIDTextBox highlighted yellow.

and in the BranchIDTextBox "#Name?" This is a bound text box to BranchID stored as a number, but displayed as Text from the ManagersIDComboBox, column 2 from the dropdown.

The good news its a new error, hopefully ,maybe I am heading blindly in the right direction.

What do you think? I need someone to shine a light to take me in the right direction. Thanks again for you help

 

[SydneyGeek]

You can use the Me notation to select the right control; at the moment it seems that Access can't find it.

When you type the period after Me you will get a pick list of all the objects, properties and methods for the form. Select the control name from that list.

EDIT: Some other tips.
When you refer to columns in a listbox or combo box the first column is 0, then 1,2,3...
It looks like you have tried to push manager ID into location ID. Use Column(1) instead.
Then, to display the location name, turn the Location text box into a combo. All it needs is LocationID and Location text fields; hide the first column and your location text should display.
If not, add Me.LocationIDCombo.Requery (adjust name to suit)

Denis

 

[smd747]

Wow we are almost there. I did what you said and now when I click on the MnagerIDCombo box The BranchIDTextBox give me the BranchID NUmber that is stored in that control. Not the Text name of the BranchID I was hoping for. What I change it to Column(1) which is the Text Name first Column(0) the BrandID number which the control hold. I get "Run-Time error -2147352567 (80020009}': The value you entered isn't valid for this field"

 

[SydneyGeek]

I edited my earlier response.
Check what I said about making the Location text box a combo. That way you can store the number and display the text.

Denis

 

[smd747]

Denis

I did the final step and turned the txt box back into a combo box and it worked wonderfully. Thank you, Thank you, this was driving me nuts.

Can not stop playing with it, with a big smile on my face after 4-days.

Thanks for the education. Life is great now Thank you

 

[SydneyGeek]

You're welcome

Denis